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

Question

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

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

Я что-то здесь упускаю?

ОБНОВЛЕНИЕ: Вот немного больше информации относительно наших рассуждений об использовании вычисляемого столбца.

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

Answer 1

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

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

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

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

Сценарий воспроизведения ниже демонстрирует проблему. Обратите внимание, что 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