代码之家 › 专栏 › 技术社区 › Dustin Laine

SQL Server 2005中有关慢速UDF的帮助

user-defined-functions performance recursion sql

Dustin Laine · 技术社区 · 15 年前

我有一个日期表调用[坏日期],它只有一列,其中每个记录都是要排除的日期。我有一个UDF,如下所示:

CREATE FUNCTION [dbo].[udf_GetDateInBusinessDays]
(
  @StartDate datetime,  --Start Date
  @NumberDays int           --Good days ahead
)
RETURNS datetime
AS
BEGIN
-- Declare the return variable here
DECLARE @ReturnDate datetime
SET @ReturnDate = @StartDate
DECLARE @Counter int
SET @Counter = 0
WHILE   @Counter < @NumberDays
BEGIN
    SET @ReturnDate = DateAdd(d,1,@ReturnDate)
    IF ((SELECT COUNT(ID)
        FROM dbo.[BadDates]
        WHERE StartDate = @ReturnDate) = 0)
    BEGIN
        SET @Counter = @Counter + 1
    END
END
RETURN @ReturnDate
END

这个UDF工作得很好,但是处理速度很慢。使用它的存储过程在每个记录中运行UDF。是否有其他方法以更快的方法提供相同的功能?

非常感谢您的帮助!

5 回复 | 直到 15 年前

dotjoe 15 年前

我没有测试过这个,但理论上它应该可以工作。我把天数加起来。然后我检查在这个范围内是否有坏日期。如果有,我会添加坏的天数,并检查在我刚添加的范围内是否还有坏的日期。重复直到没有坏的约会。

CREATE FUNCTION [dbo].[udf_GetDateInBusinessDays]
(
  @StartDate datetime,  --Start Date
  @NumberDays int           --Good days ahead
)
RETURNS datetime
AS
BEGIN
-- Declare the return variable here
DECLARE @ReturnDate datetime
SET @ReturnDate = dateadd(d, @NumberDays, @StartDate);


DECLARE @d int;
SET @d = (select count(1) from baddates where startdate >= @StartDate and startdate <= @ReturnDate);

declare @t datetime;

WHILE   @d > 0
BEGIN
    set @t = @ReturnDate;
    set @ReturnDate = dateadd(d, @d, @ReturnDate);
    SET @d = (select count(1) from baddates where startdate > @t and startdate <= @ReturnDate);
END

RETURN @ReturnDate
END

Steve Weet 15 年前

我假设你要做的是计算一个给定日期之后x个工作日的日期。例如,从今天起10个工作日是什么日期。我还假设您的Baddates表包含非工作日,例如周末和银行假日。

我在过去遇到过类似的要求,通常以包含所有可能日期的Days表和指示特定日期是否为工作日的标志结束。

然后,我使用该表通过选择开始日期之后的X天记录来计算从提供的日期开始的X个工作日是什么日期。

所以像这样

 CREATE TABLE all_days (  
  dated DATETIME,  
  day_state CHAR(1)  
  )

其中,day\u state是
工作日
W周末
B-银行假日

在x个工作日之后查找日期的SQL将变为

SELECT MAX(dated)
FROM (
  SELECT TOP(@number_days) dated
  FROM all_days
  WHERE day_state = 'D'
  AND dated >= @start_date
  ORDER by dated ASC
)

此代码未经测试,但应给出一般性的概念。您可能不想区分周末和公共假日,在这种情况下,您可以将“工作日”状态重命名为“工作日”,使其成为一个位字段。

您应该在“日期”和“日期”状态下创建一个复合的唯一索引。

Mark Canlas 15 年前

您可能希望对baddates.startdate进行索引,但可能还有其他更好的解决方案。

Johnno Nolan 15 年前

好吧,你为什么要在你可以使用exists关键字的时候计数呢?如果它是因为你可以有多个相同类型的日期在坏人这似乎是错误的。当您只需要1来排除时,Count可能会查看整个表来计算StartDate的实例。

您是否查看了查询计划以了解正在发生的情况?

joe.liedtke 15 年前

似乎您正在使用此UDF计算两个日期之间的差异。如果我正确地解释了这一点,那么我建议您使用内置的datediff函数。