sql - udf 与直接 sql 性能

Question

使用 MSSQL 2005

我今天在 where 语句中使用标量 UDF，以查看与进行调用和 io 差异等相关的一些成本。

我从 2 个基本表开始。具有 100 万行的客户。和购买有 100,000。两者都有一个自动标识列作为主键。没有定义其他索引。

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SET STATISTICS IO ON
    SELECT * FROM Customer C 
    INNER JOIN Purchases P on C.[IDENTITY] = P.CustomerID
    WHERE P.Amount > 1000
SET STATISTICS IO OFF

这将返回 IO 统计信息

Table 'Customer'. Scan count 0, logical reads 3295, physical reads 1, read-ahead reads 32, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Purchases'. Scan count 1, logical reads 373, physical reads 1, read-ahead reads 370, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

所以只是为了看看标量 UDF 的影响，然后我将 P.Amount > 1000 移到了 UDF。功能如下:

CREATE FUNCTION [dbo].[HighValuePurchase]
(
    @value int
)
RETURNS bit
AS
BEGIN
    DECLARE @highValue bit
    SET @highValue = '0'

    IF @value > 1000
    BEGIN
        SET @highValue = '1'
    END
    RETURN @highValue
END

所以我然后运行了以下查询:

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SET STATISTICS IO ON      
    SELECT * FROM Customer C 
    INNER JOIN Purchases P on C.[IDENTITY] = P.CustomerID
    WHERE dbo.HighValuePurchase(P.Amount) = '1'
SET STATISTICS IO OFF

我原以为这会更糟。此查询返回以下 IO 统计信息:

Table 'Purchases'. Scan count 1, logical reads 373, physical reads 1, read-ahead reads 370, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customer'. Scan count 1, logical reads 35, physical reads 3, read-ahead reads 472, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

这也比 > 1000 查询返回得更快。当返回相同的行时，调用 UDF 的行的顺序自动按 C.[IDENTITY] 排序，而另一个查询则显示为未排序。这可能是由于执行计划中组合的方式造成的。计划概要如下。

非 UDF 的执行计划显示了在嵌套连接中组合的购买的聚集索引扫描和客户的聚集索引搜索。

UDF 版本的执行计划显示购买的聚集索引扫描，然后是过滤器，然后是排序。 Customer 上有一个聚集索引扫描。然后将结果合并到 Merge Join 中。

我确定这与缺乏索引等有关，但我不确定为什么这些结果是这样的。我经历过 UDF 运行缓慢，每个人都说使用它们通常是一个坏主意，这就是我将这个测试放在一起的原因。我目前无法解释为什么 UDF 版本似乎要好得多。

Answer 1

如果您想加入 Purchases.CustomerID你应该在上面放一个索引。

如果您经常查询值范围，您也应该在其上放置索引。

实际上，您要求 SQL Server 在两个糟糕的计划之间进行选择。

SQL Server 可以大致猜测 > 1000 将涵盖多少次购买。查询，并会根据它选择一个计划。

但是，它无法猜测 UDF 查询将覆盖多少个，因此可能会选择不同的计划。因为它是无知的，它可能比其他计划更好或更差，这取决于它的猜测有多好。

您可以看到生成的计划，它会告诉您每个计划中的估计行数以及实际行数。这些估计数字说明了每种情况下的计划选择。