Case statement significantly effected performance

[makeitwork09]

I am using SQL Server 2005

We created the following function, which change the @date_to_check to the next business day, if that date is on a weekend or a holiday.

NOTE, the procedure that calls the function returns 70,000+ records and with the following code the stored procedure returns records in about 15 seconds.

ALTER FUNCTION [dbo].[get_next_bus_day_udf](@date_to_check datetime   ) RETURNS datetime
AS
BEGIN 
-- Then Check if next day is weekend or holiday, keep incrementing to next business day:
WHILE DATENAME(weekday, @date_to_check) = 'Saturday' or 
      DATENAME(weekday, @date_to_check) = 'Sunday' or
      @date_to_check in (select HOLIDAY from lms_nyl..globcal where country_code = 'USA' )  
select @date_to_check =  dateadd(day,1,@date_to_check)
return @date_to_check
END

However, when the above was modified so that if the @date_to_check was on the 10th the previous business day returns, rather than the next business day, the query that calls the function went from running in 15 seconds to after 11 minutes still not returning any records.

The modified code looks as follows:

ALTER FUNCTION [dbo].[get_next_bus_day_udf](@date_to_check datetime   ) RETURNS datetime
AS
BEGIN 
-- Then Check if next day is weekend or holiday, keep incrementing to next business day:
WHILE DATENAME(weekday, @date_to_check) = 'Saturday' or 
      DATENAME(weekday, @date_to_check) = 'Sunday' or
      @date_to_check in (select HOLIDAY from lms_nyl..globcal where country_code = 'USA' )  
select @date_to_check =  dateadd(day,case when datepart(day,@date_to_check) = 10 then -1 else 1 end,@date_to_check)
return @date_to_check
END

How can this function be modfied to avoid performance issues?

Plesae not too, that is test environment is using VMWare.

Thanks

 

[bborissov]

This is normal,.
You put your statement in endless loop.

This is your loop:

WHILE DATENAME(weekday, @date_to_check) = 'Saturday' or
      DATENAME(weekday, @date_to_check) = 'Sunday' or
      @date_to_check in (select HOLIDAY from lms_nyl..globcal where country_code = 'USA' )  

select @date_to_check =  dateadd(day,case when datepart(day,@date_to_check) = 10 then -1 else 1 end,@date_to_check)

So, if ay of this date is 10 you always return to 9th then to 10th, then to 9th etc.


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[makeitwork09]

Thanks for pointing that out Borislav

The following, I do believe, did the trick

WHILE DATENAME(weekday, @date_to_check) = 'Saturday' or 
      DATENAME(weekday, @date_to_check) = 'Sunday' or
      @date_to_check in (select HOLIDAY from lms_nyl..globcal where country_code = 'USA' )  
select @date_to_check =  dateadd(day,case when datepart(day,@date_to_check) = 10 then 
				case when DATENAME(weekday, @date_to_check) = 'Saturday' then -1
                     when DATENAME(weekday, @date_to_check) = 'Sunday' then -2 
                end else 1 end,@date_to_check)

Thanks

PB

 

[bborissov]

What you try to do?
Why not use the first statement?

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[makeitwork09]

I do not understand your question Borislav.

What you try to do?
Why not use the first statement?

Do you mean the first statement in my original post?

 

[bborissov]

Yep,
What you are trying to do?
Why you need CASE statement?

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[makeitwork09]

The case statement is to test if the date being evaluated is the 10th of the month. The requester wants payment dates of the 10th that fall on the weekend or on a holiday to default back to the previous business day. For all other dates that fall on a weekend or a holiday they want the payment date to default to the next business date.

I suppose my original post was not clear?

 

[bborissov]

Then this function could be something like this (NOT TESTED!!!!!!!)

ALTER FUNCTION [dbo].[get_next_bus_day_udf](@date_to_check datetime   ) RETURNS datetime
AS
BEGIN
-- Then Check if next day is weekend or holiday, keep incrementing to next business day:
WHILE DATENAME(weekday, @date_to_check) = 'Saturday' or
      DATENAME(weekday, @date_to_check) = 'Sunday' or
      @date_to_check in (select HOLIDAY from lms_nyl..globcal where country_code = 'USA' )  
      BEGIN
          SET @date_to_check =  dateadd(day,1,@date_to_check)
          IF datepart(day,@date_to_check) <> 10
             -- :-) Just empty IF

          ELSE IF DATENAME(weekday, @date_to_check) = 'Saturday' 
             BEGIN 
                SET @date_to_check =  dateadd(day,-1,@date_to_check)
                EXIT
             END
          ELSE IF DATENAME(weekday, @date_to_check) = 'Sunday' 
             BEGIN 
                SET @date_to_check =  dateadd(day,-2,@date_to_check)
                EXIT
             END
      END
return @date_to_check
END

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.