Función valorada de tabla de declaraciones múltiples frente a función valorada de tabla en línea
Algunos ejemplos para mostrar, por si acaso:
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 extractos múltiples 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
¿Existe alguna ventaja en utilizar un tipo (declaración en línea o múltiple) sobre el otro? ¿Existen ciertos escenarios en los que uno es mejor que el otro o las diferencias son puramente sintácticas? Me doy cuenta de que las dos consultas de ejemplo hacen cosas diferentes, pero ¿hay alguna razón por la que las escribiría de esa manera?
Al leer sobre ellos y realmente no se han explicado las ventajas/diferencias.
Al investigar el comentario de Matt, revisé mi declaración original. Tiene razón, 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 de instrucciones múltiples (MSTVF), incluso si ambas simplemente ejecutan una instrucción SELECT. SQL Server tratará un ITVF como VIEWsi calculara un plan de ejecución utilizando las últimas estadísticas de las tablas en cuestión. Un MSTVF equivale a incluir todo el contenido de su instrucción SELECT en una variable de tabla y luego unirse a ella. Por lo tanto, el compilador no puede utilizar ninguna estadística de tabla en las tablas del MSTVF. Entonces, en igualdad de condiciones (lo cual rara vez ocurre), el ITVF funcionará mejor que el 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 su 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 la identificación del cliente. En una consulta grande, el optimizador no podría aprovechar otros tipos de combinaciones, ya que necesitaría llamar a la función para cada ID de cliente pasado. Sin embargo, si reescribiste tu función MSTV así:
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 crear un mejor plan de ejecución, pero aun así no sería mejor que un ITVS equivalente y no parametrizado o un archivo VIEW.
Se deben preferir los ITVF a los MSTVF cuando sea posible debido a los tipos de datos, la capacidad de nulos y la intercalación de las columnas de la tabla, mientras que usted declara esas propiedades en una función con valores de tabla de declaraciones múltiples y, lo que es más importante, obtendrá mejores planes de ejecución de ITVF. En mi experiencia, no he encontrado muchas circunstancias en las que una ITVF fuera una mejor opción que una VIEW, pero el kilometraje puede variar.
Gracias a Matt.
Suma
Como vi esto recientemente, aquí hay un excelente análisis realizado por Wayne Sheffield que compara la diferencia de rendimiento entre las funciones con valores de tabla en línea y las funciones de declaraciones múltiples.
Su publicación de blog original.
Copiar en SQL Server Central
Internamente, SQL Server trata una función con valores de tabla en línea de manera muy similar a como lo haría con una vista y trata una función con valores de tabla de múltiples declaraciones de manera similar a cómo lo haría con un procedimiento almacenado.
Cuando se utiliza una función con valores de tabla en línea como parte de una consulta externa, el procesador de consultas expande la definición de UDF y genera un plan de ejecución que accede a los objetos subyacentes, utilizando los índices de estos objetos.
Para una función con valores de tabla de declaraciones múltiples, se crea un plan de ejecución para la función en sí y se almacena en la memoria caché del plan de ejecución (una vez que la función se ha ejecutado por primera vez). Si se utilizan funciones con valores de tabla de declaraciones múltiples 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, supone que la función devolverá una sola fila y que los retornos de Se accederá a la función mediante el escaneo de una tabla con una sola fila.
Donde las funciones con valores de tabla de declaraciones múltiples pueden funcionar mal es cuando devuelven una gran cantidad de filas y se unen 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, lo que no será necesariamente el plan más apropiado.
Como regla general, hemos descubierto que, cuando sea posible, las funciones con valores de tabla en línea deben usarse con preferencia a las de múltiples declaraciones (cuando la UDF se usará como parte de una consulta externa) debido a estos posibles problemas de rendimiento.
