performance - tipos - ¿Por qué el plan de ejecución incluye una llamada de función definida por el usuario para una columna calculada que se mantiene?

funciones sql server pdf

Question

Tengo una tabla con 2 columnas computadas, ambas de las cuales tienen "Se persiste" establecido en verdadero . Sin embargo, cuando se usan en una consulta, el plan de ejecución muestra la UDF utilizada para calcular las columnas como parte del plan. Dado que los datos de la columna son calculados por el UDF cuando se agrega / actualiza la fila, ¿por qué lo incluiría el plan?

La consulta es increíblemente lenta (> 30 s) cuando estas columnas se incluyen en la consulta, y la velocidad del rayo (<1s) cuando se excluyen. Esto me lleva a la conclusión de que la consulta en realidad está calculando los valores de columna en el tiempo de ejecución, lo que no debería ser el caso, ya que están configurados para persistir.

¿Me estoy perdiendo de algo?

ACTUALIZACIÓN: Aquí hay un poco más de información sobre nuestro razonamiento para utilizar la columna calculada.

Somos una empresa de deportes y tenemos un cliente que almacena los nombres completos de los jugadores en una sola columna. Nos exigen que les permitamos buscar datos de jugadores por nombre y / o apellido por separado. Afortunadamente, utilizan un formato coherente para los nombres de los jugadores: Apellido, Nombre (NickName), por lo que su análisis es relativamente fácil. Creé una UDF que llama a una función CLR para analizar las partes del nombre usando una expresión regular. Así que obviamente llamar a la UDF, que a su vez llama a una función CLR, es muy costoso. Pero como solo se usa en una columna persistente , pensé que solo se usaría las pocas veces al día en que importamos datos a la base de datos.

Answer 1

La razón es que el optimizador de consultas no hace un buen trabajo al calcular el costo de las funciones definidas por el usuario. Decide, en algunos casos, que sería más barato volver a evaluar completamente la función para cada fila, en lugar de incurrir en las lecturas de disco que de otro modo podrían ser necesarias.

El modelo de cálculo de costos de SQL Server no inspecciona la estructura de la función para ver qué tan costosa es en realidad, por lo que el optimizador no tiene información precisa al respecto. Su función podría ser arbitrariamente compleja, por lo que quizás sea comprensible que los costos se limiten de esta manera. El efecto es peor para las funciones de valores de tabla escalares y de múltiples instrucciones, ya que son extremadamente costosas de llamar por fila.

Puede saber si el optimizador ha decidido volver a evaluar la función (en lugar de utilizar el valor persistente) inspeccionando el plan de consulta. Si hay un iterador escalar de cálculo con una referencia explícita al nombre de la función en su lista de valores definidos, la función se llamará una vez por fila. Si la lista de Valores definidos hace referencia al nombre de la columna, no se llamará a la función.

Mi consejo generalmente es no usar funciones en las definiciones de columnas computadas en absoluto.

El siguiente script de reproducción muestra el problema. Observe que la CLAVE PRINCIPAL definida para la tabla no está agrupada, por lo que la obtención del valor persistente requeriría una búsqueda de marcadores desde el índice o una exploración de la tabla. El optimizador decide que es más barato leer la columna de origen para la función del índice y volver a calcular la función por fila, en lugar de incurrir en el costo de una búsqueda de marcadores o un escaneo de tablas.

La indexación de la columna persistente acelera la consulta en este caso. En general, el optimizador tiende a favorecer una ruta de acceso que evita volver a calcular la función, pero la decisión se basa en el costo, por lo que todavía es posible ver una función que se vuelve a calcular para cada fila, incluso cuando se indexa. Sin embargo, proporcionar una ruta de acceso ''obvia'' y eficiente al optimizador ayuda a evitar esto.

Observe que la columna no tiene que ser persistida para poder ser indexada. Esto es un malentendido muy común; la persistencia de la columna solo se requiere cuando es imprecisa (utiliza valores o valores aritméticos de punto flotante). La persistencia de la columna en el presente caso no agrega ningún valor y expande el requisito de almacenamiento de la tabla base.

Paul blanco

-- An expensive scalar function
CREATE FUNCTION dbo.fn_Expensive(@n INTEGER)
RETURNS BIGINT 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @sum_n BIGINT;
    SET @sum_n = 0;

    WHILE @n > 0
    BEGIN
        SET @sum_n = @sum_n + @n;
        SET @n = @n - 1
    END;

    RETURN @sum_n;
END;
GO
-- A table that references the expensive
-- function in a PERSISTED computed column
CREATE TABLE dbo.Demo
(
    n       INTEGER PRIMARY KEY NONCLUSTERED,
    sum_n   AS dbo.fn_Expensive(n) PERSISTED
);
GO
-- Add 8000 rows to the table
-- with n from 1 to 8000 inclusive
WITH Numbers AS
(
    SELECT TOP (8000)
        n = ROW_NUMBER() OVER (ORDER BY (SELECT 0))
    FROM master.sys.columns AS C1
    CROSS JOIN master.sys.columns AS C2
    CROSS JOIN master.sys.columns AS C3
)
INSERT dbo.Demo (N.n)
SELECT
    N.n
FROM Numbers AS N
WHERE
    N.n >= 1
    AND N.n <= 5000
GO
-- This is slow
-- Plan includes a Compute Scalar with:
-- [dbo].[Demo].sum_n = Scalar Operator([[dbo].[fn_Expensive]([dbo].[Demo].[n]))
-- QO estimates calling the function is cheaper than the bookmark lookup
SELECT
    MAX(sum_n)
FROM dbo.Demo;
GO
-- Index the computed column
-- Notice the actual plan also calls the function for every row, and includes:
-- [dbo].[Demo].sum_n = Scalar Operator([[dbo].[fn_Expensive]([dbo].[Demo].[n]))
CREATE UNIQUE INDEX uq1 ON dbo.Demo (sum_n);
GO
-- Query now uses the index, and is fast
SELECT
    MAX(sum_n)
FROM dbo.Demo;
GO
-- Drop the index
DROP INDEX uq1 ON dbo.Demo;
GO
-- Don''t persist the column
ALTER TABLE dbo.Demo
ALTER COLUMN sum_n DROP PERSISTED;
GO
-- Show again, as you would expect
-- QO has no option but to call the function for each row
SELECT
    MAX(sum_n)
FROM dbo.Demo;
GO
-- Index the non-persisted column
CREATE UNIQUE INDEX uq1 ON dbo.Demo (sum_n);
GO
-- Fast again
-- Persisting the column bought us nothing
-- and used extra space in the table
SELECT
    MAX(sum_n)
FROM dbo.Demo;
GO
-- Clean up
DROP TABLE dbo.Demo;
DROP FUNCTION dbo.fn_Expensive;
GO