performance - 为什么执行计划包括对持久化的计算列的用户定义函数调用？

Question

我有一个包含2个计算列的表，两个列的“ Is Persisted”都设置为true。但是，在查询中使用它们时，执行计划会显示用于计算列的UDF作为计划的一部分。由于在添加/更新行时，列数据是由UDF计算的，为什么计划要包含它？

当这些列包含在查询中时，查询速度非常慢（> 30s），如果排除了这些列，则闪电般快（<1s）。这使我得出一个结论，即查询实际上是在运行时计算列值，但情况并非如此，因为它们被设置为持久化。

我在这里想念什么吗？

更新：这是有关我们使用计算列的推理的更多信息。

我们是一家体育用品公司，并且有一个客户将完整的运动员姓名存储在一个栏中。他们要求我们允许他们分别按名字和/或姓氏搜索球员数据。幸运的是，它们使用一致的格式来命名玩家的名字-LastName，FirstName（NickName）-因此解析它们相对容易。我创建了一个UDF，该UDF调用CLR函数以使用正则表达式解析名称部分。因此，显然，调用UDF（又调用CLR函数）非常昂贵。但是由于它只在一个持久化的列上使用，我认为它只会在每天将数据导入数据库的几次中使用。

Answer 1

原因是查询优化器在花费用户定义的函数方面做得不好。在某些情况下，它决定完全重新评估每一行的功能要便宜，而不是招致否则可能需要的磁盘读取。

SQL Server的成本核算模型不会检查函数的结构以查看其实际成本，因此优化器在这方面没有准确的信息。您的功能可能非常复杂，因此以这种方式限制成本是可以理解的。对于标量和多语句表值函数，效果最差，因为按行调用它们非常昂贵。

您可以通过检查查询计划来判断优化器是否已决定重新评估功能（而不是使用持久值）。如果有一个Compute Scalar迭代器在其Defined Values列表中明确引用了该函数名称，则该函数将每行调用一次。如果“定义的值”列表引用了列名，则不会调用该函数。

我的建议通常是根本不在计算列定义中使用函数。

下面的复制脚本演示了此问题。请注意，为表定义的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