SQL Server性能问题:为什么简单的SQL语句和一个UD标量值函数看起来很昂贵

Question

我们的SQL 2008 r2数据库遇到了一些严重的性能问题。当我们在SQL Server Management Studio中运行活动监视器和返回活动的SP时，它显示以下三个事务非常昂贵:

查询1:

SET @DateMonth = '0' + @DateMonth

查询2:

SET @DateMonth = CAST(datepart(mm, @TheDate) AS VARCHAR(2))

功能:

CREATE FUNCTION [dbo].[DateToNGDate] (@TheDate datetime)
RETURNS VARCHAR(10)
AS
BEGIN
            DECLARE @DateYear VARCHAR(4)
            DECLARE @DateMonth VARCHAR(2)
            DECLARE @DateDay VARCHAR(2)
            SET @DateYear = CAST(datepart(yyyy, @TheDate) AS VARCHAR(4))
            SET @DateMonth = CAST(datepart(mm, @TheDate) AS VARCHAR(2))
            IF (LEN (@DateMonth) = 1) SET @DateMonth = '0' + @DateMonth
            SET @DateDay = CAST(datepart(dd, @TheDate) AS VARCHAR(2))
            IF (LEN (@DateDay) = 1) SET @DateDay = '0' + @DateDay
            RETURN @DateYear+@DateMonth+@DateDay
END

最后一个像这样回来，但我很确定它不是创建函数(它已经存在)，而只是运行它。它也是最常出现的性能杀手(它在我们的代码中使用)。

我确信这些不是真正导致问题的原因，但为什么它们会出现呢?

Answer 1

标量值函数是SQL Server中一个已知的性能问题。一种选择是像这样定义函数:

CREATE FUNCTION [dbo].[DateToNGDateX] (@TheDate datetime)
RETURNS table as return 
(
    select
        cast(
              CAST(datepart(yyyy, @TheDate) AS VARCHAR(4)) -- DateYear 
            + right('0' + CAST(datepart(mm, @TheDate) AS VARCHAR(2)),2) -- DateMonth 
            + right('0' + CAST(datepart(dd, @TheDate) AS VARCHAR(2)),2) -- DateDay 
        as varchar(30)) as Value
)

，并像这样引用它:

select Value from [dbo].[DateToNGDateX] ('20140110');

但是，对于您想要的特定功能，也要测试这个:

select convert(char(8), cast('20140110' as date), 112);

返回格式为yyyymmdd的日期。

Answer 2

如果选择语句返回大量行，则标量udf非常慢，因为它们将对返回的每一行执行一次。也许你可以重写为内联函数按照这个链接内联udf

Answer 3

我喜欢Pieter Geerkens的回答，我希望它已经解决了你的问题。标量函数将在任何使用它的地方返回每个匹配行的结果。这和游标有点类似。表值函数将接受一个集合并返回一个集合。

要验证两个进程之间的IO变化，management studio可能没有帮助，但是在您自己的机器上，您可以放置一个小跟踪，以查看您获得的性能大小。

干杯! !