使用函数查询非常慢

This sort of follows on from a previous question:

这是前面一个问题的答案:

SQL Server Query time out depending on Where Clause

SQL Server查询超时取决于Where子句

In which a query would run extremely slowly depending on a where clause. I rewrote that query use CTE and avoided the problem nicely, but never found an answer.

查询将根据where子句极其缓慢地运行。我使用CTE重写了这个查询，很好地避免了这个问题，但始终没有找到答案。

Another, similar, query was tweaked recently to add another field, and suddenly it's performance dropped from taking about ~30 seconds to run and return 10,000 rows, to taking over 10 hours (and eventually returning the same result set). Today I started to troubleshoot this one and found something weird.

另一个类似的查询在最近添加了另一个字段，突然间它的性能下降了大约30秒，然后返回10,000行，花费超过10个小时(最终返回相同的结果集)。今天我开始解决这个问题，发现了一些奇怪的东西。

I am constantly needing to extract the date only part from datetime values, and so I wrote the logic into a function:

我经常需要从datetime值中提取日期，所以我把逻辑写成了一个函数:

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

I found in this new, inefficient query that if I replaced that function with the CAST(CONVERT(VARCHAR(10), @pInputDate, 111) AS DATETIME) inline in the query, the speed of the query execution dropped from ~10 hours, to just under 2 seconds. I can see no difference in the estimated execution plan. By the way, this was not the field that was added. I assume adding the other field somehow caused the execution plan to change and amplified the above condition.

我发现，在这个新的、低效的查询中，如果我在查询中内联地使用CAST(VARCHAR(10)、@pInputDate(111)作为DATETIME)替换该函数，查询执行的速度将从大约10个小时下降到不到2秒。我看不出估计的执行计划有什么不同。顺便说一下，这不是添加的字段。我假设添加另一个字段以某种方式导致执行计划更改并放大了上述条件。

My question is, is this normal? I make use of functions for repetitive processes, as above, as they are easier to maintain, remember and to update if you find a more efficient way of doing something. Should I be doing something to my functions to improve their performance?

我的问题是，这是正常的吗?我将函数用于重复的过程，如上面所示，因为如果您找到一种更有效的方法，它们更容易维护、记住和更新。我应该对我的功能做些什么来提高它们的性能吗?

2 个解决方案

#1

If you must encapsulate this in a function see Scalar functions, inlining, and performance: An entertaining title for a boring post

如果您必须将其封装在函数中，请参阅标量函数、内联和性能:一个无聊的帖子的有趣标题

Rewrite it as follows

重写它,如下所示

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

Then instead of

然后,而不是

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

Use

使用

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

This will be inlined into the plan by the query optimiser.

这将被查询optimiser嵌入到计划中。

#2

I would try possibly adding another variable to do the declaration and return. The thought process for this could be that your cast and converting to alter the datetime for your input may be the bottleneck. This may not be the case but often if you set in the scope a new variable, do your conditioning and bind it to that variable and return that, that may help out with speed. I tend to stay away from scalar functions when possible though, they have lots of performance issues when you start using them with larger data sets. Just a suggestion it may or may not help but it would isolate the return to a new object independent of the input:

我将尝试添加另一个变量来进行声明和返回。这个想法的过程可能是您的cast和转换为您的输入的datetime可能是瓶颈。这可能不是这样，但是如果你在作用域中设置一个新的变量，做你的条件作用，把它绑定到那个变量上，然后返回那个变量，这可能对速度有帮助。如果可能的话，我倾向于远离标量函数，但是当您开始在更大的数据集中使用它们时，它们会有很多性能问题。只是一个建议，它可能会，也可能不会有帮助，但它会将返回的结果与独立于输入的新对象隔离开来:

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

#1