Help with Slow UDFs in SQL Server 2005
I have a date table calling [BadDates], it only has one column where each record is the date to be excluded. I have a UDF like this:
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 to process. A stored procedure that uses this runs a UDF on every record. Are there other ways to provide this same functionality to a faster method.
Any help is greatly appreciated!
a source to share
I haven't tested this, but in theory it should work. I add the number of days. Then I check if there are any errors in this range. If I were adding the number of bad days and checking if there were more baddats in the range I just added. Repeat until there are 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
a source to share
I am assuming that what you are trying to do is to compute a date that is x business days since a given date. what date is 10 business days from today. I also assume that the baddates table contains non-business days, for example. Weekends and holidays.
I've come across similar requirements in the past and usually ended up with a table of days containing all possible dates along with a flag that indicates whether a particular date is a business day or not.
I then use this table to calculate which date is x business days from the provided date by selecting a record that is x days after the start date.
So something like this
CREATE TABLE all_days (
dated DATETIME,
day_state CHAR(1)
)
Where day_state - value D - Working day
W - Weekend
B - Bank Holiday
SQL to find the date after x business days then becomes
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 a general idea. You cannot distinguish between weekends and holidays, in which case you can rename day_state to work_day and make it a BIT field.
You should create a composite unique index on dates and days.
a source to share
Okay, why are you considering when you can use the EXISTS keyword? If it is because you can have multiple dates of the same type in the signs, it seems wrong. COUNT will probably go through the entire table to count the startdate instances when you only need 1 to exclude.
Have you looked at the query plan to see what's going on?
a source to share