Función Valorada de Tabla de Sentencias Múltiples vs Función Valorada de Tabla en Línea
Algunos ejemplos para mostrar, solo en caso:
Tabla Inline 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
Multi Statement Table Valued
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 una ventaja al usar un tipo (en línea o sentencia 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 una razón por la que las escribiría de esa manera?
Leyendo sobre ellos y las ventajas/diferencias realmente no se han explicado.
6 answers
Al investigar el comentario de Matt, he revisado mi declaración original. Él es correcto, habrá una diferencia en el rendimiento entre una función de valor de tabla inline (ITVF) y una función de valor de tabla multi-sentencia (MSTVF) incluso si ambos simplemente ejecutan una sentencia SELECT. SQL Server tratará un ITVF como un VIEW en el sentido de que calculará un plan de ejecución utilizando las últimas estadísticas de las tablas en cuestión. Un MSTVF es equivalente a rellenar todo el contenido de su SELECCIONE la instrucción en una variable de tabla y luego únase a ella. Por lo tanto, el compilador no puede usar ninguna estadística de tablas en las tablas del MSTVF. Por lo tanto, siendo todas las cosas iguales, (que rara vez son), la ITVF funcionará mejor 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 su caso, las dos funciones no son funcionalmente equivalentes. La función MSTV realiza una consulta adicional cada vez se llama y, lo más importante, se filtra en el id del cliente. En una consulta grande, el optimizador no sería capaz de tomar ventaja de otros tipos de uniones, ya que tendría que llamar a la función para cada CustomerID pasado. Sin embargo, si reescribe su función MSTV de esta 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 sería capaz de llamar a esa función una vez y construir un mejor plan de ejecución, pero todavía no sería mejor que un ITV equivalente, no parametrizado o un VIEW.
ITVFs se debe preferir sobre un MSTVFs cuando sea posible porque los tipos de datos, la nullabilidad y la intercalación de las columnas de la tabla, mientras que declara esas propiedades en una función de valor de tabla de sentencias múltiples 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 fuera una mejor opción que una VISTA, pero el kilometraje puede variar.
Gracias a Matt.
Adición
Desde que vi esto venir recientemente, aquí hay un excelente análisis realizado por Wayne Sheffield comparando la diferencia de rendimiento entre las funciones de Valor de Tabla en línea y las funciones de Múltiples sentencias.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-05-15 14:42:27
Internamente, SQL Server trata una función con valor de tabla en línea como lo haría con una vista y trata una función con valor de tabla de instrucciones múltiples de manera similar a como lo haría con un procedimiento almacenado.
Cuando se utiliza una función con valor de tabla en línea 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 de estos objetos.
Para una función con valor de tabla de sentencias múltiples, una ejecución plan se crea para la propia función y se almacena en la caché del plan de ejecución (una vez que la función se ha ejecutado la primera vez). Si las funciones con valor de tabla de sentencias 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 : en efecto, asume que la función devolverá una sola fila, y que se accederá a los retornos de la función utilizando un análisis de tabla fila.
Donde las funciones con valores de tabla de sentencias múltiples pueden funcionar mal es cuando devuelven un gran número de filas y se unen contra en consultas externas. Los problemas de rendimiento se deben principalmente al hecho de que el optimizador producirá un plan asumiendo que se devuelve una sola fila, que no necesariamente será el plan más apropiado.
Como regla general, hemos encontrado que, cuando sea posible, las funciones con valores de tabla en línea deben usarse con preferencia a multi-sentencia (cuando el UDF se utilizará como parte de una consulta externa) debido a estos posibles problemas de rendimiento.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-04-16 19:20:28
Hay otra diferencia. Una función con valor de tabla en línea se puede insertar, actualizar y eliminar, al igual que una vista. Se aplican restricciones similares: no se pueden actualizar funciones usando agregados,no se pueden actualizar columnas calculadas, etc.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-07-25 09:23:51
Sus ejemplos, creo, responden muy bien a la pregunta. La primera función se puede hacer como una sola selección, y es una buena razón para usar el estilo en línea. La segunda probablemente podría hacerse como una sola instrucción (usando una sub-consulta para obtener la fecha máxima), pero algunos codificadores pueden encontrar más fácil de leer o más natural hacerlo en varias instrucciones como lo ha hecho. Algunas funciones simplemente no se pueden hacer en una instrucción, por lo que requieren la versión de varias instrucciones.
Sugiero usar el más simple (en línea) siempre que sea posible, y el uso de declaraciones múltiples cuando sea necesario (obviamente) o cuando la preferencia personal/legibilidad hace que wirth la escritura adicional.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-03-31 15:47:57
Mira Comparando Funciones con Valores de Tabla en Línea y Multi-Sentencia puedes encontrar buenas descripciones y puntos de referencia de rendimiento
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-02-17 09:45:01
Si va a hacer una consulta, puede unirse a su función de Valores de tabla en línea como:
SELECT
a.*,b.*
FROM AAAA a
INNER JOIN MyNS.GetUnshippedOrders() b ON a.z=b.z
Incurrirá en pocos gastos generales y funcionará bien.
Si intenta usar su Tabla de Sentencias Multi Valorada 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
Debido a que ejecutará la función 1 vez por cada fila devuelta, a medida que el conjunto de resultados se haga grande, se ejecutará cada vez más lento.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-03-31 17:38:23