Fast UDF from SQL 2000 is very slow in 2005

[disruptivehair]

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.

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:

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:

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

[wordracr]

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.