Подтвердить что ты не робот

Почему в плане выполнения включен пользовательский вызов функции для вычисленного столбца, который сохраняется?

У меня есть таблица с двумя вычисленными столбцами, каждая из которых имеет "Is Persisted", которая установлена ​​в true. Однако при использовании их в запросе в плане выполнения отображается UDF, используемый для вычисления столбцов как части плана. Поскольку данные столбца вычисляются UDF, когда строка добавляется/обновляется, почему план должен включать ее?

Запрос невероятно медленный ( > 30 с), когда эти столбцы включены в запрос и молниеносно (< 1s), когда они исключены. Это приводит меня к выводу, что запрос фактически вычисляет значения столбцов во время выполнения, что не должно быть так, поскольку они настроены на сохранение.

Я что-то пропустил?

UPDATE: Здесь немного больше информации о наших рассуждениях для использования вычисленного столбца.

Мы являемся спортивной компанией и имеем клиента, который хранит имена игроков в одном столбце. Они требуют от нас, чтобы они могли искать данные игрока по имени и/или фамилии отдельно. К счастью, они используют согласованный формат для имен игроков - LastName, FirstName (NickName), поэтому их анализ довольно прост. Я создал UDF, который вызывает функцию CLR для анализа частей имени с использованием регулярного выражения. Таким образом, очевидно, что вызов UDF, который, в свою очередь, вызывает функцию CLR, очень дорог. Но поскольку он используется только в постоянном столбце, я решил, что он будет использоваться только несколько раз в день, чтобы мы импортировали данные в базу данных.

4b9b3361

ОТВЕТЫ

Ответ 1

Причина в том, что оптимизатор запросов не очень хорошо справляется с калькуляцией пользовательских функций. В некоторых случаях он решает, что было бы дешевле полностью переоценить функцию для каждой строки, а не подвергать считывание диска, которое может потребоваться в противном случае.

Модель калькуляции SQL Server не проверяет структуру функции, чтобы увидеть, насколько она дорогой, поэтому оптимизатор не имеет точной информации в этом отношении. Ваша функция может быть произвольно сложной, поэтому, возможно, понятно, что калькуляция ограничена таким образом. Эффект хуже для скалярных и многозадачных табличных функций, так как они чрезвычайно дороги для вызова строки.

Вы можете определить, решил ли оптимизатор переоценить функцию (а не использовать постоянное значение), проверив план запроса. Если есть итератор Compute Scalar с явной ссылкой на имя функции в списке Defined Values, функция будет вызываться один раз для каждой строки. Если в списке "Определенные значения" ссылается имя столбца, функция не будет вызываться.

Мой совет, как правило, вообще не использовать функции в вычисленных определениях столбцов.

Воспроизведение script ниже демонстрирует проблему. Обратите внимание, что PRIMARY KEY, определенный для таблицы, некластерирован, поэтому для получения сохраненного значения потребуется поиск по закладкам из индекса или сканирование таблицы. Оптимизатор решает, что дешевле читать исходный столбец для функции из индекса и повторно вычислять функцию за строку, вместо того, чтобы нести расходы на поиск по закладке или сканирование таблицы.

Индексирование сохраненного столбца ускоряет запрос в этом случае. В общем, оптимизатор стремится к пути доступа, который позволяет избежать повторного вычисления функции, но решение основано на издержках, поэтому все еще можно увидеть, что функция пересчитывается для каждой строки даже при индексировании. Тем не менее, обеспечение "очевидного" и эффективного доступа к оптимизатору помогает избежать этого.

Обратите внимание, что столбец не должен быть сохранен для индексации. Это очень распространенное заблуждение; сохранение столбца требуется только там, где оно неточно (оно использует арифметику с плавающей запятой или значения). Сохранение столбца в данном случае не добавляет значения и расширяет требования к хранилищу базовой таблицы.

Пол Уайт

-- 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