使用函数的SQL Server查询速度非常慢

Question

这个问题有点类似之前的一个问题：

SQL Server查询根据Where子句超时

其中一个查询会根据where子句而极其缓慢地运行。我使用了CTE重写了该查询，并成功避免了这个问题，但始终没有找到答案。

最近调整了另一个类似的查询以添加另一个字段，结果性能从大约30秒运行并返回10,000行下降到需要超过10小时（最终返回相同的结果集）。今天我开始排除这个问题，发现了一些奇怪的东西。

我不断地需要从日期时间值中提取仅日期部分，因此我将逻辑编写成了一个函数:

CREATE FUNCTION [dbo].[cDate] ( @pInputDate    DATETIME )
RETURNS DATETIME
BEGIN
        RETURN CAST(CONVERT(VARCHAR(10), @pInputDate, 111) AS DATETIME)
END
GO

我发现在这个新的低效查询中，如果我用CAST(CONVERT(VARCHAR(10), @pInputDate, 111) AS DATETIME)替换那个函数，查询的执行速度从大约10个小时降至不到2秒。我在预计的执行计划中看不出任何区别。顺便说一句，这不是新增字段。我想添加其他字段可能会改变执行计划并放大上述条件。

我的问题是，这是正常的吗？像上面那样，我会使用函数来处理重复的过程，因为它们更容易维护、记忆和更新，如果您找到了更有效的方法来完成某些操作。我应该对我的函数做些什么以提高它们的性能吗？

- Molloch

---

是的，这是众所周知的。搜索标量值函数。这就是RBAR... - Mitch Wheat

你使用的 SQL Server 版本是什么？也许 CAST(@pInputDate AS DATE) 会更有效率（适用于 2008 或更新版本）... - marc_s

很不幸，我目前被困在2005年，所以这不是一个选择。 - Molloch

谢谢，Mitch。看起来我需要重新设计一下... - Molloch

Answer 1

如果你必须将此封装在函数中，请参考“标量函数、内联和性能：一个无聊帖子的有趣标题”（链接）一文。请按照以下方法重写它。

CREATE FUNCTION dbo.cDate_Inline
(
    @pInputDate DATETIME
)
RETURNS TABLE
AS
    RETURN
    (
        SELECT DATEADD(day, DATEDIFF(Day, 0, @pInputDate), 0) AS [Date]
    )

然后，不是使用

SELECT *,
       [dbo].[cDate](modify_date) AS modified
FROM   sys.objects 

使用

SELECT *,
       ca.Date AS modified
FROM   sys.objects
       CROSS APPLY dbo.cDate_Inline(modify_date) ca 

这将由查询优化器内联到计划中。

- Martin Smith

---

救命稻草，谢谢！省去我大量的重构 :) - xhafan

Answer 2

我建议尝试添加另一个变量来进行声明和返回。这样做的思路可能是，你的转换和修改输入日期时间的强制转换可能是瓶颈。虽然不一定如此，但通常情况下，如果在作用域中设置一个新变量，对其进行条件处理并将其绑定到该变量上并返回它，这可能有助于提高速度。尽可能避免使用标量函数，因为当你开始使用它们处理更大的数据集时，它们会有很多性能问题。这只是一个建议，可能有帮助，也可能没有帮助，但它会将返回值隔离到一个与输入无关的新对象中：

CREATE FUNCTION [dbo].[cDate] ( @pInputDate    DATETIME )
RETURNS DATETIME
BEGIN
Declare @Output datetime  = CAST(CONVERT(VARCHAR(10), @pInputDate, 111) AS DATETIME)  -- 2008 method and newer

Declare @Output datetime;

Select @Output  = CAST(CONVERT(VARCHAR(10), @pInputDate, 111) AS DATETIME)  -- 2005 and prior method 


return @Output
END
GO

- djangojazz