Datetime function slowdowns

The function below is a bottleneck and I wonder if there is anything that can
be done to improve its performance.
FUNCTION [dbo].[fnDayDiffExcludingWeekEnds]
(@.StartDate datetime,
@.EndDate datetime)
RETURNS int
AS
BEGIN
declare @.ActualDateDiff int
declare @.NewStartDate datetime
declare @.Difference int
declare @.TempDifference int
set @.ActualDateDiff = datediff(dd, @.StartDate, @.EndDate)
select @.Difference =
case @.ActualDateDiff
when 0 then 0--Same Day (just calculate days)
when 1 then
case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
(1,7) then 0 else 1 end
when 2 then--Could be 1 weekend day
case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
(1,7) then 1 else 2 end
when 7 then 5 --Always Two weekend days
else
case when @.ActualDateDiff < 7 then --Less than a week
case @.ActualDateDiff + datepart(dw,@.Startdate) - 1
when 10 then @.actualdatediff - 2
when 9 then @.actualdatediff - 2
when 8 then @.ActualDateDiff - 2
when 7 then @.ActualDateDiff - 1
else
@.ActualDateDiff
end
else -- More than a week (always 5 work days)
5
end
end
if @.ActualDateDiff > 7
begin
while @.ActualDateDiff > 7
begin
set @.Difference = isnull(@.Difference,0) + 5
set @.NewStartDate = dateadd(dd,7,@.StartDate)
set @.ActualDateDiff = @.ActualDateDiff - 7
end
end
return @.Difference
END
Regards,
Jamie
Jamie,
It is highly unlikely that the code of this function is a performance
bottleneck. Even saying that it runs in milliseconds is an
overestimation.
If it is a performance bottleneck, then you are calling it too often.
Scalar UDFs can be very costly when called for large data sets.
One solution is to switch from a procedural approach (using a scalar
UDF) to a set based approach. One method to do that is to join to a
calendar table (google "Calendar Table"), count the days in the period
and omit the weekend days.
HTH,
Gert-Jan
thejamie wrote:
> The function below is a bottleneck and I wonder if there is anything that can
> be done to improve its performance.
> FUNCTION [dbo].[fnDayDiffExcludingWeekEnds]
> (@.StartDate datetime,
> @.EndDate datetime)
> RETURNS int
> AS
> BEGIN
> declare @.ActualDateDiff int
> declare @.NewStartDate datetime
> declare @.Difference int
> declare @.TempDifference int
> set @.ActualDateDiff = datediff(dd, @.StartDate, @.EndDate)
> select @.Difference =
> case @.ActualDateDiff
> when 0 then 0 --Same Day (just calculate days)
> when 1 then
> case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
> (1,7) then 0 else 1 end
> when 2 then --Could be 1 weekend day
> case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
> (1,7) then 1 else 2 end
> when 7 then 5 --Always Two weekend days
> else
> case when @.ActualDateDiff < 7 then --Less than a week
> case @.ActualDateDiff + datepart(dw,@.Startdate) - 1
> when 10 then @.actualdatediff - 2
> when 9 then @.actualdatediff - 2
> when 8 then @.ActualDateDiff - 2
> when 7 then @.ActualDateDiff - 1
> else
> @.ActualDateDiff
> end
> else -- More than a week (always 5 work days)
> 5
> end
> end
> if @.ActualDateDiff > 7
> begin
> while @.ActualDateDiff > 7
> begin
> set @.Difference = isnull(@.Difference,0) + 5
> set @.NewStartDate = dateadd(dd,7,@.StartDate)
> set @.ActualDateDiff = @.ActualDateDiff - 7
> end
> end
> return @.Difference
> END
> --
> Regards,
> Jamie

Datetime function slowdowns

The function below is a bottleneck and I wonder if there is anything that ca
n
be done to improve its performance.
FUNCTION [dbo].[fnDayDiffExcludingWeekEnds]
(@.StartDate datetime,
@.EndDate datetime)
RETURNS int
AS
BEGIN
declare @.ActualDateDiff int
declare @.NewStartDate datetime
declare @.Difference int
declare @.TempDifference int
set @.ActualDateDiff = datediff(dd, @.StartDate, @.EndDate)
select @.Difference =
case @.ActualDateDiff
when 0 then 0 --Same Day (just calculate days)
when 1 then
case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
(1,7) then 0 else 1 end
when 2 then --Could be 1 weekend day
case when datepart(dw,@.Startdate) in (1,7) or datepart(dw,@.EndDate) in
(1,7) then 1 else 2 end
when 7 then 5 --Always Two weekend days
else
case when @.ActualDateDiff < 7 then --Less than a week
case @.ActualDateDiff + datepart(dw,@.Startdate) - 1
when 10 then @.actualdatediff - 2
when 9 then @.actualdatediff - 2
when 8 then @.ActualDateDiff - 2
when 7 then @.ActualDateDiff - 1
else
@.ActualDateDiff
end
else -- More than a week (always 5 work days)
5
end
end
if @.ActualDateDiff > 7
begin
while @.ActualDateDiff > 7
begin
set @.Difference = isnull(@.Difference,0) + 5
set @.NewStartDate = dateadd(dd,7,@.StartDate)
set @.ActualDateDiff = @.ActualDateDiff - 7
end
end
return @.Difference
END
--
Regards,
JamieJamie,
It is highly unlikely that the code of this function is a performance
bottleneck. Even saying that it runs in milliseconds is an
overestimation.
If it is a performance bottleneck, then you are calling it too often.
Scalar UDFs can be very costly when called for large data sets.
One solution is to switch from a procedural approach (using a scalar
UDF) to a set based approach. One method to do that is to join to a
calendar table (google "Calendar Table"), count the days in the period
and omit the weekend days.
HTH,
Gert-Jan
thejamie wrote:
> The function below is a bottleneck and I wonder if there is anything that
can
> be done to improve its performance.
> FUNCTION [dbo].[fnDayDiffExcludingWeekEnds]
> (@.StartDate datetime,
> @.EndDate datetime)
> RETURNS int
> AS
> BEGIN
> declare @.ActualDateDiff int
> declare @.NewStartDate datetime
> declare @.Difference int
> declare @.TempDifference int
> set @.ActualDateDiff = datediff(dd, @.StartDate, @.EndDate)
> select @.Difference =
> case @.ActualDateDiff
> when 0 then 0 --Same Day (just calculate days)
> when 1 then
> case when datepart(dw,@.Startdate) in (1,7) or date
part(dw,@.EndDate) in
> (1,7) then 0 else 1 end
> when 2 then --Could be 1 weekend day
> case when datepart(dw,@.Startdate) in (1,7) or date
part(dw,@.EndDate) in
> (1,7) then 1 else 2 end
> when 7 then 5 --Always Two weekend days
> else
> case when @.ActualDateDiff < 7 then --Less than a week
> case @.ActualDateDiff + datepart(dw,@.Startdate)
- 1
> when 10 then @.actualdatediff - 2
> when 9 then @.actualdatediff - 2
> when 8 then @.ActualDateDiff - 2
> when 7 then @.ActualDateDiff - 1
> else
> @.ActualDateDiff
> end
> else -- More than a week (always 5 work days)
> 5
> end
> end
> if @.ActualDateDiff > 7
> begin
> while @.ActualDateDiff > 7
> begin
> set @.Difference = isnull(@.Difference,0) + 5
> set @.NewStartDate = dateadd(dd,7,@.StartDate)
> set @.ActualDateDiff = @.ActualDateDiff - 7
> end
> end
> return @.Difference
> END
> --
> Regards,
> Jamie