sql - tipos - Función valorada de tabla de declaraciones múltiples frente a función valorada de tabla en línea

funciones sql ejemplos

Question

Algunos ejemplos para mostrar, solo en caso de:

Tabla en línea valorada

CREATE FUNCTION MyNS.GetUnshippedOrders()
RETURNS TABLE
AS 
RETURN SELECT a.SaleId, a.CustomerID, b.Qty
    FROM Sales.Sales a INNER JOIN Sales.SaleDetail b
        ON a.SaleId = b.SaleId
        INNER JOIN Production.Product c ON b.ProductID = c.ProductID
    WHERE a.ShipDate IS NULL
GO

Tabla de declaración múltiple valorada

CREATE FUNCTION MyNS.GetLastShipped(@CustomerID INT)
RETURNS @CustomerOrder TABLE
(SaleOrderID    INT         NOT NULL,
CustomerID      INT         NOT NULL,
OrderDate       DATETIME    NOT NULL,
OrderQty        INT         NOT NULL)
AS
BEGIN
    DECLARE @MaxDate DATETIME

    SELECT @MaxDate = MAX(OrderDate)
    FROM Sales.SalesOrderHeader
    WHERE CustomerID = @CustomerID

    INSERT @CustomerOrder
    SELECT a.SalesOrderID, a.CustomerID, a.OrderDate, b.OrderQty
    FROM Sales.SalesOrderHeader a INNER JOIN Sales.SalesOrderHeader b
        ON a.SalesOrderID = b.SalesOrderID
        INNER JOIN Production.Product c ON b.ProductID = c.ProductID
    WHERE a.OrderDate = @MaxDate
        AND a.CustomerID = @CustomerID
    RETURN
END
GO

¿Hay alguna ventaja al usar un tipo (enunciado en línea o multi) sobre el otro? ¿Hay ciertos escenarios cuando uno es mejor que el otro o las diferencias son puramente sintácticas? Me doy cuenta de que las dos consultas de ejemplo están haciendo cosas diferentes, pero ¿hay alguna razón por la que las escriba de esa manera?

Leer sobre ellos y las ventajas / diferencias realmente no se han explicado.

Answer 1

Al investigar el comentario de Matt, he revisado mi declaración original. Él está en lo cierto, habrá una diferencia en el rendimiento entre una función con valores de tabla en línea (ITVF) y una función con valores de tabla multi declaraciones (MSTVF) incluso si ambos simplemente ejecutan una instrucción SELECT. SQL Server tratará un ITVF como una VIEW en el sentido de que calculará un plan de ejecución utilizando las últimas estadísticas en las tablas en cuestión. Un MSTVF es equivalente a rellenar todo el contenido de su instrucción SELECT en una variable de tabla y luego unirse a eso. Por lo tanto, el compilador no puede usar ninguna estadística de tabla en las tablas en MSTVF. Por lo tanto, si todo sigue igual (lo que rara vez sucede), la ITVF tendrá un mejor rendimiento que la MSTVF. En mis pruebas, la diferencia de rendimiento en el tiempo de finalización fue insignificante, sin embargo desde un punto de vista estadístico, fue notable.

En tu caso, las dos funciones no son funcionalmente equivalentes. La función MSTV realiza una consulta adicional cada vez que se llama y, lo más importante, filtra en la identificación del cliente. En una consulta grande, el optimizador no podría aprovechar otros tipos de uniones ya que necesitaría llamar a la función para cada ID de cliente pasado. Sin embargo, si reescribió su función MSTV de la siguiente manera:

CREATE FUNCTION MyNS.GetLastShipped()
RETURNS @CustomerOrder TABLE
    (
    SaleOrderID    INT         NOT NULL,
    CustomerID      INT         NOT NULL,
    OrderDate       DATETIME    NOT NULL,
    OrderQty        INT         NOT NULL
    )
AS
BEGIN
    INSERT @CustomerOrder
    SELECT a.SalesOrderID, a.CustomerID, a.OrderDate, b.OrderQty
    FROM Sales.SalesOrderHeader a 
        INNER JOIN Sales.SalesOrderHeader b
            ON a.SalesOrderID = b.SalesOrderID
        INNER JOIN Production.Product c 
            ON b.ProductID = c.ProductID
    WHERE a.OrderDate = (
                        Select Max(SH1.OrderDate)
                        FROM Sales.SalesOrderHeader As SH1
                        WHERE SH1.CustomerID = A.CustomerId
                        )
    RETURN
END
GO

En una consulta, el optimizador podría llamar a esa función una vez y construir un mejor plan de ejecución, pero aún así no sería mejor que una ITVS equivalente o no parametrizable o una VIEW .

Las ITVF deben preferirse a las MSTVF cuando sea posible debido a los tipos de datos, la nulabilidad y la intercalación de las columnas en la tabla, mientras que usted declara esas propiedades en una función de tabla de múltiples declaraciones y, lo que es más importante, obtendrá mejores planes de ejecución de la ITVF. En mi experiencia, no he encontrado muchas circunstancias en las que una ITVF sea una mejor opción que una VISTA, pero el kilometraje puede variar.

Gracias a Matt.

Adición

Desde que vi esto aparecer recientemente, aquí hay un excelente análisis hecho por Wayne Sheffield comparando la diferencia de rendimiento entre las funciones de Inline Table Valued y las funciones de Multi-Statement.

Su publicación de blog original.

Copiar en SQL Server Central

Answer 2

Hay otra diferencia. Una función en línea con valores de tabla puede insertarse, actualizarse y eliminarse, como una vista. Se aplican restricciones similares: no se pueden actualizar las funciones mediante el uso de agregados, no se pueden actualizar las columnas calculadas, y así sucesivamente.

Answer 3

Internamente, SQL Server trata una función con valores de tabla en línea de forma muy similar a como lo haría con una vista y trata una función con valores de tabla multi-extracto similar a cómo sería un procedimiento almacenado.

Cuando se utiliza una función en línea con valores de tabla como parte de una consulta externa, el procesador de consultas expande la definición UDF y genera un plan de ejecución que accede a los objetos subyacentes, utilizando los índices en estos objetos.

Para una función con valores de tabla multi-extracto, se crea un plan de ejecución para la función en sí y se almacena en el caché del plan de ejecución (una vez que la función se ha ejecutado por primera vez). Si las funciones con valores de tabla de declaraciones múltiples se utilizan como parte de consultas más grandes, entonces el optimizador no sabe qué devuelve la función, y por lo tanto hace algunas suposiciones estándar; de hecho, asume que la función devolverá una sola fila, y que los retornos de se accederá a la función usando un escaneo de tabla contra una tabla con una sola fila.

Donde las funciones con valores de tabla multi-declaración pueden tener un rendimiento bajo es cuando devuelven una gran cantidad de filas y se unen en contra en consultas externas. Los problemas de rendimiento se deben principalmente al hecho de que el optimizador producirá un plan suponiendo que se devuelva una sola fila, que no será necesariamente el plan más apropiado.

Como regla general, hemos encontrado que, cuando sea posible, las funciones con valores de tabla en línea deberían utilizarse con preferencia a las de declaración múltiple (cuando la UDF se utilizará como parte de una consulta externa) debido a estos posibles problemas de rendimiento.

Answer 4

Si va a hacer una consulta, puede unirse a su función de Inline Table Valued como:

SELECT
    a.*,b.*
    FROM AAAA a
        INNER JOIN MyNS.GetUnshippedOrders() b ON a.z=b.z

incurrirá en gastos generales y correrá bien.

Si intenta usar su Table Multi Statement Table Valued en una consulta similar, tendrá problemas de rendimiento:

SELECT
    x.a,x.b,x.c,(SELECT OrderQty FROM MyNS.GetLastShipped(x.CustomerID)) AS Qty
    FROM xxxx   x

porque ejecutará la función 1 vez por cada fila devuelta, a medida que el conjunto de resultados sea grande, se irá haciendo cada vez más lento.

Answer 5

Sus ejemplos, creo, responden muy bien a la pregunta. La primera función se puede hacer como una única selección, y es una buena razón para usar el estilo en línea. El segundo probablemente se podría hacer como una sola declaración (usando una subconsulta para obtener la fecha máxima), pero algunos codificadores pueden encontrar más fácil de leer o más natural para hacerlo en múltiples declaraciones como lo has hecho. Algunas funciones simplemente no se pueden hacer en una declaración, y por lo tanto requieren la versión multi-declaración.

Sugiero usar el más simple (en línea) siempre que sea posible, y usar declaraciones múltiples cuando sea necesario (obviamente) o cuando la preferencia / legibilidad personal lo haga anotar el tipeo extra.

Answer 6

consulte la comparación de funciones con valores de tabla en línea y en varias instrucciones , puede encontrar buenas descripciones y puntos de referencia de rendimiento