在SQL Server 2005中帮助缓慢的UDF

I have a table of dates call [BadDates], it has just one column where every record is a date to exclude. I have a UDF as follows:

我有一个日期调用表[BadDates]，它只有一列，其中每个记录都是要排除的日期。我有一个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

This UDF works great, but it is slow in processing. The stored procedure that uses this runs the UDF in every record. Is there other ways to provide this same functionality in a faster method.

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

Any help is greatly appreciated!

任何帮助是极大的赞赏！

5 个解决方案

#1

I haven't tested this but in theory it should work. I add up the number of days. Then I check if there were any baddates in that range. If there were I add the number of bad days and check if there were anymore baddates in the range I just added. Repeat until no bad dates.

我没有测试过这个，但从理论上说它应该可行。我把天数加起来。然后我检查该范围内是否有任何坏消息。如果有，我添加了不良日期的数量，并检查我刚刚添加的范围内是否有更多的坏消息。重复直到没有错误的日期。

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

#2

I'm assuming that what you are trying to do is calculate the date that is x working days past a given date.e.g. what date is 10 working days from today. I am also assuming that your baddates table contains non-working days e.g. Weekends and bank holidays.

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

I have encountered similar requirements in the past and usually ended up with days table that contains all possible dates along with a flag that indicates whether a particular date is a working day or not.

我在过去遇到过类似的要求，通常最终会有包含所有可能日期的天数表以及一个标志，指示某个特定日期是否为工作日。

I then use that table to calculate what date is x working days from the provided date by selecting the record that is x days after the starting date.

然后，我使用该表通过选择开始日期后x天的记录来计算从提供日期起x个工作日的日期。

So something like this

所以这样的事情

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

Where day_state is value of
D - Working Day
W - Weekend
B - Bank Holiday

其中day_state是D的值 - 工作日W - 周末B - 银行假日

The SQL to find the date after x working days then becomes

然后，在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
)

This code is untested but should give you the general idea. You may not want to differentiate between weekends and public holidays in which case you could rename the day_state to working_day and make it a BIT field.

这段代码未经测试，但应该给你一般的想法。您可能不希望区分周末和公共假期，在这种情况下，您可以将day_state重命名为working_day并使其成为BIT字段。

You should create a composite unique index on dated and day_state.

您应该在date和day_state上创建复合唯一索引。

#3

You may want to put an index on BadDates.StartDate, but there may be other, better solutions.

您可能希望在BadDates.StartDate上添加索引，但可能还有其他更好的解决方案。

#4

Ok why are you counting when you can use the EXISTS keyword? If its because you can have multiple dates of the same type in Badates this seems wrong. COUNT will probably looking through the whole table to count the instances of startdate when all you need is 1 to exclude.

好的，为什么你可以使用EXISTS关键字？如果是因为你可以在Badates中有多个相同类型的日期，这似乎是错误的。 COUNT可能会查看整个表来计算startdate的实例，只需要1即可排除。

Have you had a look at the query plan to see what is happening?

你有没有看过查询计划，看看发生了什么？

#5

Looks like you are using this UDF to calculate the difference between two dates. If i am interpreting this correctly then I would recommend that you use the built-in datediff function.

看起来您正在使用此UDF来计算两个日期之间的差异。如果我正确解释这个，那么我建议你使用内置的约会功能。

#1

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

#2