Fast UDF from SQL 2000 is very slow in 2005
This UDF runs relatively quickly in SQL 2000...usually doesn't take more than 1 second if it's added to a query. I have a query in 2005 that takes 25 seconds longer to execute if this function is part of it. I tried putting the bank holidays in a table and checking to see if the parameters were between the two of them but that took 10 times longer than the original function. I'm hopelessly outclassed here.
The point of this UDF is to determine how many working days have occurred between @aDate and @aLaterDate, then work out how many of those days were bank holidays and subtract them.
Code:
CREATE FUNCTION [dbo].[fnBankHolidays] (@aDate as smalldatetime, @aLaterDate as smalldatetime)
RETURNS Int AS
BEGIN
DECLARE @BaseWorkingDays Int
SET @BaseWorkingDays = dbo.fnWorkingDays(@aDate, @aLaterDate)
-- 2005 Bank Holidays
IF @aDate <= '01/01/05' AND @aLaterDate > '01/01/05'
BEGIN
SET @BaseWorkingDays = @BaseWorkingDays - 1
END
IF @aDate <= '03/25/05' AND @aLaterDate > '03/25/05'
BEGIN
SET @BaseWorkingDays = @BaseWorkingDays - 1
END
IF @aDate <= '03/28/05' AND @aLaterDate > '03/28/05'
BEGIN
SET @BaseWorkingDays = @BaseWorkingDays - 1
END
IF @aDate <= '05/02/05' AND @aLaterDate > '05/02/05'
BEGIN
SET @BaseWorkingDays = @BaseWorkingDays - 1
END
IF @aDate <= '05/30/05' AND @aLaterDate > '05/30/05'
BEGIN
SET @BaseWorkingDays = @BaseWorkingDays - 1
END
IF @aDate <= '08/29/05' AND @aLaterDate > '08/29/05'
BEGIN
SET @BaseWorkingDays = @BaseWorkingDays -1
END
IF @aDate <= '12/26/05' AND @aLaterDate > '12/26/05'
BEGIN
SET @BaseWorkingDays = @BaseWorkingDays - 1
END
IF @aDate <= '12/27/05' AND @aLaterDate > '12/27/05'
BEGIN
SET @BaseWorkingDays = @BaseWorkingDays - 1
END
-- 2006 Bank Holidays
IF @aDate <= '01/01/06' AND @aLaterDate > '01/01/06'
BEGIN
SET @BaseWorkingDays = @BaseWorkingDays - 1
END
IF @aDate <= '04/14/06' AND @aLaterDate > '04/14/06'
BEGIN
SET @BaseWorkingDays = @BaseWorkingDays - 1
END
IF @aDate <= '04/17/06' AND @aLaterDate > '04/17/06'
BEGIN
SET @BaseWorkingDays = @BaseWorkingDays - 1
END
IF @aDate <= '05/01/06' AND @aLaterDate > '05/01/06'
BEGIN
SET @BaseWorkingDays = @BaseWorkingDays - 1
END
IF @aDate <= '05/29/06' AND @aLaterDate > '05/29/06'
BEGIN
SET @BaseWorkingDays = @BaseWorkingDays - 1
END
IF @aDate <= '08/25/06' AND @aLaterDate > '08/25/06'
BEGIN
SET @BaseWorkingDays = @BaseWorkingDays - 1
END
IF @aDate <= '12/25/06' AND @aLaterDate > '12/25/06'
BEGIN
SET @BaseWorkingDays = @BaseWorkingDays - 1
END
IF @aDate <= '12/26/06' AND @aLaterDate > '12/26/06'
BEGIN
SET @BaseWorkingDays = @BaseWorkingDays - 1
END
-- 2007 Bank Holidays
IF @aDate <= '01/01/07' AND @aLaterDate > '01/01/07'
BEGIN
SET @BaseWorkingDays = @BaseWorkingDays - 1
END
IF @aDate <= '04/06/07' AND @aLaterDate > '04/06/07'
BEGIN
SET @BaseWorkingDays = @BaseWorkingDays - 1
END
IF @aDate <= '04/09/07' AND @aLaterDate > '04/09/07'
BEGIN
SET @BaseWorkingDays = @BaseWorkingDays - 1
END
IF @aDate <= '05/07/07' AND @aLaterDate > '05/07/07'
BEGIN
SET @BaseWorkingDays = @BaseWorkingDays - 1
END
IF @aDate <= '05/28/07' AND @aLaterDate > '05/28/07'
BEGIN
SET @BaseWorkingDays = @BaseWorkingDays - 1
END
IF @aDate <= '08/27/07' AND @aLaterDate > '08/27/07'
BEGIN
SET @BaseWorkingDays = @BaseWorkingDays - 1
END
IF @aDate <= '12/25/07' AND @aLaterDate > '12/25/07'
BEGIN
SET @BaseWorkingDays = @BaseWorkingDays - 1
END
IF @aDate <= '12/26/07' AND @aLaterDate > '12/26/07'
BEGIN
SET @BaseWorkingDays = @BaseWorkingDays - 1
END
RETURN @BaseWorkingDays
END
And here's the code for the fnWorkingDays function in case it's necessary:
Code:
CREATE FUNCTION [dbo].[fnWorkingDays] (@StartDate as smalldatetime, @EndDate as smalldatetime)
RETURNS Int AS
BEGIN
RETURN (DATEDIFF(dd, @StartDate, @EndDate) -2*(DATEDIFF(wk, @StartDate, @EndDate)))
END
And so you guys can point and laugh at the n00b, here's the UDF that took three minutes to return a result on 12000 rows:
Code:
CREATE FUNCTION dbo.fnBankHolidays2(@aDate AS smalldatetime, @aLaterDate AS smalldatetime)
RETURNS Int AS
BEGIN
DECLARE @BaseWorkingDays Int
DECLARE @Count Int
SET @BaseWorkingDays = dbo.fnWorkingDays(@aDate, @aLaterDate)
SET @Count = (SELECT COUNT(BankHoliday) FROM BankHolidays WHERE BankHoliday BETWEEN @aDate AND @aLaterDate)
SET @BaseWorkingDays = @BaseWorkingDays - @Count
RETURN @BaseWorkingDays
END
Re: Fast UDF from SQL 2000 is very slow in 2005
Like you said, try putting the date items in a table, but then see what the query plan shows. The other option is to not use the function and just join to the date table.