Ответ 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