To Jeff mostly,
To bolster my claim about function overhead being the great killer in this debate.....I read your article about the function that you wrote for determining the number of workdays in a week. I read it AFTER my previous post and did some quick benchmarks.
I used two functions:
1. One for calculating the day of the week in a deterministic way (no side effects)
2. One doing the actual work.
Your solution used one function that did everything on its own. Guess what....your solution was faster on my system (roughly 9 vs 15 seconds on 100.000 date ranges).
Then I switched to one function as well, by duplicating the work from my first function to the second calling function instead of doing the two calls. This function then became faster still (roughly 7 vs 9 seconds on 100.000 date ranges).
Thus for performing the exact same work my original solution wend from 15 to 7 seconds!!! The time difference totally attributable to the function call overhead! This IMHO proves the whole point about functions being implemented crap by SQL Server. And showcases exactly why in many instances set based code is unreasonable much faster then procedural.
As I said before I can rewrite the code to fit on one line and do it all and it would be even faster (maybe I will, just to stun you all) .... but would anyone still comprehend it?
New code:
create function dbo.fnWeekDaysInDateRange_solo( @firstday as datetime, @lastday as datetime ) returns int
with schemabinding as
begin
declare @delta as int
declare @excessdays as int
declare @daysdiff as int
declare @firstMonday as datetime
declare @lastMonday as datetime
-- Widen date range to encompas full calendar weeks
set @delta = datediff( d, {d '2007-01-01'}, @firstday ) % 7
set @firstMonday = dateadd( d, 0 - case when @delta < 0 then 7 + @delta else @delta end, @firstday )
set @delta = datediff( d, {d '2007-01-01'}, @lastday ) % 7
set @lastMonday = dateadd( d, 7 - case when @delta < 0 then 7 + @delta else @delta end, @lastday )
-- Compensate for extended days at start of daterange
set @daysdiff = datediff( d, @firstMonday, @firstday )
set @excessdays = case when @daysdiff > 5 then 5 else @daysdiff end
-- Compensate for extended days at end of daterange
set @daysdiff = datediff( d, @lastday, @lastMonday )
set @excessdays = @excessdays + case when @daysdiff >= 3 then @daysdiff - 2 when @daysdiff != 0 then 1 else 0 end
-- Calculate total number of workdays!
return (datediff( ww, @firstMonday, @lastMonday ) * 5) - @excessdays + 1
end
go
Inline table valued functions fair better until they overwhelm the optimizers capacity of course...this version does the same job in 3-4 seconds and needs to be used in conjunction with cross apply.
create function dbo.fnWeekDaysInDateRange_solo_inline( @firstday as datetime, @lastday as datetime ) returns table
as
return
(
with
-- Widen date range to encompas full calendar weeks - to first monday
firstDeltaQ (N) as ( select datediff( d, {d '2007-01-01'}, @firstday ) % 7 )
, firstMondayQ (N) as
(
select
dateadd( d, 0 - case when delta.N < 0 then 7 + delta.N else delta.N end, @firstday )
from
firstDeltaQ as delta
)
-- Widen date range to encompas full calendar weeks - to next monday
, lastDeltaQ (N) as ( select datediff( d, {d '2007-01-01'}, @lastday ) % 7 )
, lastMondayQ (N) as
(
select
dateadd( d, 7 - case when delta.N < 0 then 7 + delta.N else delta.N end, @lastday )
from
lastDeltaQ as delta
)
, excessdaysQ ( N ) as
(
select
-- Compensate for extended days at start of daterange
case
when datediff( d, firstMonday.N, @firstday ) > 5 then 5
else datediff( d, firstMonday.N, @firstday )
end
-- Compensate for extended days at end of daterange
+ case
when datediff( d, @lastday, lastMonday.N ) >= 3 then datediff( d, @lastday, lastMonday.N ) - 2
when datediff( d, @lastday, lastMonday.N ) != 0 then 1
else 0
end
from
firstMondayQ as firstMonday
cross join lastMondayQ as lastMonday
)
select
-- Calculate total number of workdays!
(datediff( ww, firstMonday.N, lastMonday.N ) * 5) - excessdays.N + 1 as n
from
firstMondayQ as firstMonday
cross join lastMondayQ as lastMonday
cross join excessdaysQ as excessdays
)
;
go
Annoying smileys all over my code, why?
BTW. I was mentioning seconds, but the last function does the job in like 0.8 seconds the rest is moving the results over to the client (grid mode). Corrected for this time (a little over 3 seconds in my test cases), the inline function is like 6 times faster then your original scalar function and 12 times faster then my own scalar original.
The overhead of scalar functions is as you can see monster like!