October 14, 2016 at 11:26 am
whenriksen (10/14/2016)
ScottPletcher (10/14/2016)
Wow, that's a fascinating statement. Computers are known for doing billions of certain mathematical calcs per second (graphics chips hit trillions). Under no circumstances I know of can a computer do billions of logical I/Os in anything close to one second.My (admittedly wide) calendar table has 55k rows. I highly doubt using it versus an expression would add billions of I/Os to the query.
For me, the performance aspect also includes how well the solution will perform when used by other developers or with different parameter values. The consistency of including it in my calendar table ensures a consistent usage and therefore a consistent performance expectation whether used by me (the author) or a junior developer getting their feet wet.
I suppose the calc could be wrapped into a udf to accomplish the same thing, but I expect that would hurt performance more than a simple join, (even without the billions of IOs ;-))
It doesn't need to add billions of I/Os to add significant overhead. That's the point. Billions of math calcs are basically irrelevant to the total query time, whereas even a few million I/Os could give a noticeable delay. Whether that delay is actually significant or not would vary, of course.
Any junior DBA I would hire would be able to understand a variance off a base date calculation. Isn't it straightforward enough:
1) take a known Monday (for example)
2) see how many days past that Monday you are, ignoring whole weeks
3) subtract result 2 from the starting to back up to monday
I.e., if it's Wednesday, that's two days ahead of monday, so if i back up 2 days, I must end at a Monday.
Btw, I have created NonWorkDays calendar tables here: a control table and the actual dates table. App code uses only the dates table unless it needs the day description, such as "Thanksgiving Day", which appears only in the control table.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 14, 2016 at 3:28 pm
You made me curious and its been a quiet day.
SELECT COUNT(*),
CAST(DATEADD(WEEK, DATEDIFF(WEEK, '20061231', DATEADD(DAY, 7
- CASE DATEPART(WEEKDAY, o.modify_date) WHEN 1 THEN 7
ELSE DATEPART(WEEKDAY, o.modify_date) - 1
END, o.modify_date)) % 2, DATEADD(DAY, 7
- CASE DATEPART(WEEKDAY, o.modify_date) WHEN 1 THEN 7
ELSE DATEPART(WEEKDAY, o.modify_date) - 1
END, o.modify_date)) AS DATE)
FROM sys.objects AS o --this returns 6.7 million rows for my system
CROSS JOIN sys.columns AS c
GROUP BY CAST(DATEADD(WEEK, DATEDIFF(WEEK, '20061231', DATEADD(DAY, 7
- CASE DATEPART(WEEKDAY, o.modify_date) WHEN 1 THEN 7
ELSE DATEPART(WEEKDAY, o.modify_date) - 1
END, o.modify_date)) % 2, DATEADD(DAY, 7
- CASE DATEPART(WEEKDAY, o.modify_date) WHEN 1 THEN 7
ELSE DATEPART(WEEKDAY, o.modify_date) - 1
END, o.modify_date)) AS DATE)
The pure expression version returned:
(163 row(s) affected)
Table 'sysschobjs'. Scan count 1, logical reads 49, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 90, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 6 ms.
The join to the calendar table returned:
(163 row(s) affected)
Table 'tDates'. Scan count 0, logical reads 2646, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 49, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 90, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 11 ms.
Incidentally, simplifying the expression and using Set DateFirst 1 returns the same results.
Depending on your perspective, that is either an insignificant difference or a massive difference. π
My company has a number of business analysts that write their own SQL, most of which are not experts. The consistent performance afforded by the pre-calc'd column is more important to me than the minimal gain. It makes it easier for my analysts to write good performing code.
In light of the results, I'll retract my statement that performance is a benefit of pre-calc'd calendar columns. I will instead substitute it with Ease of Performance or Ease of Use as a benefit. π
Wes
(A solid design is always preferable to a creative workaround)
October 14, 2016 at 4:04 pm
whenriksen (10/14/2016)
You made me curious and its been a quiet day.
SELECT COUNT(*),CAST(DATEADD(WEEK, DATEDIFF(WEEK, '20061231', DATEADD(DAY, 7
- CASE DATEPART(WEEKDAY, o.modify_date) WHEN 1 THEN 7
ELSE DATEPART(WEEKDAY, o.modify_date) - 1
END, o.modify_date)) % 2, DATEADD(DAY, 7
- CASE DATEPART(WEEKDAY, o.modify_date) WHEN 1 THEN 7
ELSE DATEPART(WEEKDAY, o.modify_date) - 1
END, o.modify_date)) AS DATE)
FROM sys.objects AS o --this returns 6.7 million rows for my system
CROSS JOIN sys.columns AS c
GROUP BY CAST(DATEADD(WEEK, DATEDIFF(WEEK, '20061231', DATEADD(DAY, 7
- CASE DATEPART(WEEKDAY, o.modify_date) WHEN 1 THEN 7
ELSE DATEPART(WEEKDAY, o.modify_date) - 1
END, o.modify_date)) % 2, DATEADD(DAY, 7
- CASE DATEPART(WEEKDAY, o.modify_date) WHEN 1 THEN 7
ELSE DATEPART(WEEKDAY, o.modify_date) - 1
END, o.modify_date)) AS DATE)
The pure expression version returned:
(163 row(s) affected)Table 'sysschobjs'. Scan count 1, logical reads 49, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 90, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 6 ms.
The join to the calendar table returned:
(163 row(s) affected)Table 'tDates'. Scan count 0, logical reads 2646, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 49, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 90, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 11 ms.
Incidentally, simplifying the expression and using Set DateFirst 1 returns the same results.
Depending on your perspective, that is either an insignificant difference or a massive difference. π
My company has a number of business analysts that write their own SQL, most of which are not experts. The consistent performance afforded by the pre-calc'd column is more important to me than the minimal gain. It makes it easier for my analysts to write good performing code.
In light of the results, I'll retract my statement that performance is a benefit of pre-calc'd calendar columns. I will instead substitute it with Ease of Performance or Ease of Use as a benefit. π
What is all the DATEPART stuff? That's never needed.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 14, 2016 at 4:14 pm
First day of the business week is Monday.
What do you recommend?
Wes
(A solid design is always preferable to a creative workaround)
October 14, 2016 at 4:30 pm
whenriksen (10/14/2016)
First day of the business week is Monday.What do you recommend?
I posted code that did that type of calc already. What specifically are you trying to list?
If you just want a list of Sunday dates that are two weeks apart from a given starting date:
SELECT COUNT(*),
DATEADD(DAY, -DATEDIFF(DAY, '19000107', o.modify_date) % 14 + 14, o.modify_date)
FROM sys.objects AS o
CROSS JOIN sys.columns AS c
GROUP BY DATEADD(DAY, -DATEDIFF(DAY, '19000107', o.modify_date) % 14 + 14, o.modify_date)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 14, 2016 at 5:28 pm
ScottPletcher (10/14/2016)
whenriksen (10/14/2016)
First day of the business week is Monday.What do you recommend?
I posted code that did that type of calc already. What specifically are you trying to list?
If you just want a list of Sunday dates that are two weeks apart from a given starting date:
SELECT COUNT(*),
DATEADD(DAY, -DATEDIFF(DAY, '19000107', o.modify_date) % 14 + 14, o.modify_date)
FROM sys.objects AS o
CROSS JOIN sys.columns AS c
GROUP BY DATEADD(DAY, -DATEDIFF(DAY, '19000107', o.modify_date) % 14 + 14, o.modify_date)
You're code isn't returning the same thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 14, 2016 at 5:33 pm
whenriksen (10/14/2016)
You made me curious and its been a quiet day.
SELECT COUNT(*),CAST(DATEADD(WEEK, DATEDIFF(WEEK, '20061231', DATEADD(DAY, 7
- CASE DATEPART(WEEKDAY, o.modify_date) WHEN 1 THEN 7
ELSE DATEPART(WEEKDAY, o.modify_date) - 1
END, o.modify_date)) % 2, DATEADD(DAY, 7
- CASE DATEPART(WEEKDAY, o.modify_date) WHEN 1 THEN 7
ELSE DATEPART(WEEKDAY, o.modify_date) - 1
END, o.modify_date)) AS DATE)
FROM sys.objects AS o --this returns 6.7 million rows for my system
CROSS JOIN sys.columns AS c
GROUP BY CAST(DATEADD(WEEK, DATEDIFF(WEEK, '20061231', DATEADD(DAY, 7
- CASE DATEPART(WEEKDAY, o.modify_date) WHEN 1 THEN 7
ELSE DATEPART(WEEKDAY, o.modify_date) - 1
END, o.modify_date)) % 2, DATEADD(DAY, 7
- CASE DATEPART(WEEKDAY, o.modify_date) WHEN 1 THEN 7
ELSE DATEPART(WEEKDAY, o.modify_date) - 1
END, o.modify_date)) AS DATE)
The pure expression version returned:
(163 row(s) affected)Table 'sysschobjs'. Scan count 1, logical reads 49, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 90, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 6 ms.
The join to the calendar table returned:
(163 row(s) affected)Table 'tDates'. Scan count 0, logical reads 2646, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 49, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 90, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 11 ms.
Incidentally, simplifying the expression and using Set DateFirst 1 returns the same results.
Depending on your perspective, that is either an insignificant difference or a massive difference. π
My company has a number of business analysts that write their own SQL, most of which are not experts. The consistent performance afforded by the pre-calc'd column is more important to me than the minimal gain. It makes it easier for my analysts to write good performing code.
In light of the results, I'll retract my statement that performance is a benefit of pre-calc'd calendar columns. I will instead substitute it with Ease of Performance or Ease of Use as a benefit. π
Haven't read through all of this thread. The code above appears to be counting things by week and the dates appear to be starting dates (Sundays, specifically) for the week period. Is that correct?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 17, 2016 at 8:21 am
ScottPletcher (10/14/2016)
If you just want a list of Sunday dates that are two weeks apart from a given starting date:
SELECT COUNT(*),
DATEADD(DAY, -DATEDIFF(DAY, '19000107', o.modify_date) % 14 + 14, o.modify_date)
FROM sys.objects AS o
CROSS JOIN sys.columns AS c
GROUP BY DATEADD(DAY, -DATEDIFF(DAY, '19000107', o.modify_date) % 14 + 14, o.modify_date)
This isn't accurate. Although it is calculating the correct date for the pay period End Date, the grouping is associating the ending Sunday to the next pay period. For example, PPED = 2013-05-26 is returning data between 2013-05-12 and 2013-05-25. The records for 2013-05-12 should be included in the PPED 2013-05-12, not 05-26.
Saturday May 11, 20132013-05-12
Sunday May 12, 20132013-05-26
Monday May 13, 20132013-05-26
In addition, it doesn't properly calculate dates earlier than the seed range. Using '20161009' as a seed generates:
FullDatePayrollPeriodEndDate
Saturday May 11, 20132013-05-26
Sunday May 12, 20132013-05-26
Monday May 13, 20132013-06-09
Jeff Moden (10/14/2016)
Haven't read through all of this thread. The code above appears to be counting things by week and the dates appear to be starting dates (Sundays, specifically) for the week period. Is that correct?
It is counting records by two week periods with Sunday as the Ending date, based upon a seed date to determine the division of the two week periods. The original business problem was to associate daily labor records to the ending date of the pay period in which they occurred. My company ends the pay period on Sunday. The PPED may then be used in additional filters (e.g. Fiscal Year starting 5/1)
FullDatePayrollPeriodEndDate
Saturday May 11, 20132013-05-12
Sunday May 12, 20132013-05-12
Monday May 13, 20132013-05-26
Wes
(A solid design is always preferable to a creative workaround)
October 17, 2016 at 8:31 am
whenriksen (10/17/2016)
ScottPletcher (10/14/2016)
If you just want a list of Sunday dates that are two weeks apart from a given starting date:
SELECT COUNT(*),
DATEADD(DAY, -DATEDIFF(DAY, '19000107', o.modify_date) % 14 + 14, o.modify_date)
FROM sys.objects AS o
CROSS JOIN sys.columns AS c
GROUP BY DATEADD(DAY, -DATEDIFF(DAY, '19000107', o.modify_date) % 14 + 14, o.modify_date)
This isn't accurate. Although it is calculating the correct date for the pay period End Date, the grouping is associating the ending Sunday to the next pay period. For example, PPED = 2013-05-26 is returning data between 2013-05-12 and 2013-05-25. The records for 2013-05-12 should be included in the PPED 2013-05-12, not 05-26.
Saturday May 11, 20132013-05-12
Sunday May 12, 20132013-05-26
Monday May 13, 20132013-05-26
In addition, it doesn't properly calculate dates earlier than the seed range. Using '20161009' as a seed generates:
FullDatePayrollPeriodEndDate
Saturday May 11, 20132013-05-26
Sunday May 12, 20132013-05-26
Monday May 13, 20132013-06-09
Jeff Moden (10/14/2016)
Haven't read through all of this thread. The code above appears to be counting things by week and the dates appear to be starting dates (Sundays, specifically) for the week period. Is that correct?It is counting records by two week periods with Sunday as the Ending date, based upon a seed date to determine the division of the two week periods. The original business problem was to associate daily labor records to the ending date of the pay period in which they occurred. My company ends the pay period on Sunday. The PPED may then be used in additional filters (e.g. Fiscal Year starting 5/1)
FullDatePayrollPeriodEndDate
Saturday May 11, 20132013-05-12
Sunday May 12, 20132013-05-12
Monday May 13, 20132013-05-26
Rather unfair to cut off the part where I asked what specifically are your requirements, because you've never stated them clearly, and then to blast me for not meeting those unstated requirements(!).
I will note in passing, though, that as often occurs in these cases, the I/O on the calendar table is 19+ times the I/O on the actual data itself.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 17, 2016 at 9:01 am
ScottPletcher (10/17/2016)
Rather unfair to cut off the part where I asked what specifically are your requirements, because you've never stated them clearly, and then to blast me for not meeting those unstated requirements(!).I will note in passing, though, that as often occurs in these cases, the I/O on the calendar table is 19+ times the I/O on the actual data itself.
My apologies if I offended. That was not my intent.
The general requirements were described in my second post in response to your post questioning the performance benefit. I included a small sample of the result showing the date matched to the Pay Period End Date. In addition I posted working, running sample code which could be used to compare results, in which I also confirmed your statement that using a calendar table would not improve performance, but stated instead that it does make it easier to write good performing code.
This has been a fun exercise and I have learned from it, which makes it worthwhile. I'm still interested to see a less complicated expression, but in the meantime, here is my version that leveraged changing the DateFirst value:
SET DATEFIRST 1;
SELECT COUNT(*),
CAST(DATEADD(week,DATEDIFF(WEEK,'20061231',DATEADD(DAY,7-DATEPART(WEEKDAY,o.modify_date),o.modify_date))%2,DATEADD(DAY,7-DATEPART(WEEKDAY,o.modify_date),o.modify_date)) AS DATE)
FROM sys.objects AS o
CROSS JOIN sys.columns AS c
GROUP BY CAST(DATEADD(week,DATEDIFF(WEEK,'20061231',DATEADD(DAY,7-DATEPART(WEEKDAY,o.modify_date),o.modify_date))%2,DATEADD(DAY,7-DATEPART(WEEKDAY,o.modify_date),o.modify_date)) AS DATE)
ORDER BY 2
Although this removes the case statement and is easier to read and maintain, I prefer to avoid statements that modify the session environment. I've found that my peer developers have a tendency to overlook statements that exist outside of the query statement. Good help can be hard to find.
Wes
(A solid design is always preferable to a creative workaround)
October 17, 2016 at 9:40 am
whenriksen (10/17/2016)
ScottPletcher (10/17/2016)
Rather unfair to cut off the part where I asked what specifically are your requirements, because you've never stated them clearly, and then to blast me for not meeting those unstated requirements(!).I will note in passing, though, that as often occurs in these cases, the I/O on the calendar table is 19+ times the I/O on the actual data itself.
My apologies if I offended. That was not my intent.
The general requirements were described in my second post in response to your post questioning the performance benefit. I included a small sample of the result showing the date matched to the Pay Period End Date. In addition I posted working, running sample code which could be used to compare results, in which I also confirmed your statement that using a calendar table would not improve performance, but stated instead that it does make it easier to write good performing code.
This has been a fun exercise and I have learned from it, which makes it worthwhile. I'm still interested to see a less complicated expression, but in the meantime, here is my version that leveraged changing the DateFirst value:
SET DATEFIRST 1;SELECT COUNT(*),
CAST(DATEADD(week,DATEDIFF(WEEK,'20061231',DATEADD(DAY,7-DATEPART(WEEKDAY,o.modify_date),o.modify_date))%2,DATEADD(DAY,7-DATEPART(WEEKDAY,o.modify_date),o.modify_date)) AS DATE)
FROM sys.objects AS o
CROSS JOIN sys.columns AS c
GROUP BY CAST(DATEADD(week,DATEDIFF(WEEK,'20061231',DATEADD(DAY,7-DATEPART(WEEKDAY,o.modify_date),o.modify_date))%2,DATEADD(DAY,7-DATEPART(WEEKDAY,o.modify_date),o.modify_date)) AS DATE)
ORDER BY 2
Although this removes the case statement and is easier to read and maintain, I prefer to avoid statements that modify the session environment. I've found that my peer developers have a tendency to overlook statements that exist outside of the query statement. Good help can be hard to find.
Yeah, I agree, I would never adjust DATEFIRST unless there was absolutely no alternative. Too much chance for unexpected errors and/or results.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 18, 2016 at 12:50 am
Hi,
There is no need for bad feelings. This is just good starting point for thinking. If you are in house dba or in house programmer, you probably will not used such solution.
But if you are have a lot of customers and tons of t-sql, you probably will use.
You can't rely on the set datefirst because it depends on language. And you can change language in any batch. See select * from sys.syslanguages for more information.
But you can determine weekdays by compare with dates you know.
E.g.
DECLARE @subota as int; -- Saturday on Croatian
DECLARE @nedjelja as int; --Sunday on Croatian
SET @nedjelja = DATEPART(WEEKDAY, '20161002'); --we know it was Sunday
SET @subota = DATEPART(WEEKDAY, '20161001'); --we know it was Saturday
So you code will be
--
, CASE WHEN DATEPART(DW, CalendarDate) IN( @subota, @nedjelja ) THEN 1
ELSE 0 END AS Weekend
, CASE WHEN DATEPART(DW, CalendarDate) NOT IN( @subota, @nedjelja ) AND
h.Datum IS NULL THEN 1
--
Then expression (DATEPART(weekday,CalendarDate) + @@DATEFIRST) % 7 will always produce 0 for Saturday, 1 for Sunday and so on.
So, you can change code like this in order to produce correct ( it means not depends ) day of week
--
, (DATEPART(weekday,CalendarDate) + @@DATEFIRST) % 7
AS [DayofWeek]
--
I hope I have helped you to discussion is going in the right direction.
D.
October 18, 2016 at 10:25 am
I rewrote it yet again, without case or firstdate:
CAST (DATEADD(WEEK, ABS(( DATEDIFF(WEEK, '20161009', DATEADD(DAY, -1, o.modify_date))
+ 1 ) % 2), DATEADD(DAY, ( DATEPART(WEEKDAY, DATEADD(DAY, -1, o.modify_date))
- 7 ) * -1, o.modify_date)) AS DATE)
I also inserted the cross join results into a temp table to more realistically represent the source table overhead with the expression version.
Turns out the join to the calendar table is the quickest on a large data set, even including the overhead of the parser.
--join to calendar table
SQL Server parse and compile time:
CPU time = 921 ms, elapsed time = 938 ms.
(163 row(s) affected)
Table '#temp_00000000E3BD'. Scan count 3, logical reads 14115, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tDates'. Scan count 0, logical reads 1055, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3026 ms, elapsed time = 1729 ms.
--expression without case statements
SQL Server parse and compile time:
CPU time = 5 ms, elapsed time = 5 ms.
(163 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#temp_00000000E3BD'. Scan count 3, logical reads 14115, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 7176 ms, elapsed time = 3618 ms.
--expression with case statements
SQL Server parse and compile time:
CPU time = 5 ms, elapsed time = 5 ms.
(163 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#temp_00000000E3BD'. Scan count 3, logical reads 14115, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 6099 ms, elapsed time = 3097 ms.
Wes
(A solid design is always preferable to a creative workaround)
October 18, 2016 at 10:33 am
whenriksen (10/18/2016)
I rewrote it yet again, without case or firstdate:
DATEADD(WEEK, ABS(( DATEDIFF(WEEK, '20161009', DATEADD(DAY, -1, o.modify_date))+ 1 ) % 2), DATEADD(DAY, ( DATEPART(WEEKDAY, DATEADD(DAY, -1, o.modify_date))
- 7 ) * -1, o.modify_date))
I also inserted the cross join results into a temp table to more realistically represent the source table overhead with the expression version.
Turns out the join to the calendar table is the quickest on a large data set, even including the overhead of the parser.
--join to calendar table
SQL Server parse and compile time:
CPU time = 921 ms, elapsed time = 938 ms.
(163 row(s) affected)
Table '#temp_00000000E3BD'. Scan count 3, logical reads 14115, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tDates'. Scan count 0, logical reads 1055, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3026 ms, elapsed time = 1729 ms.
--expression without case statements
SQL Server parse and compile time:
CPU time = 5 ms, elapsed time = 5 ms.
(163 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#temp_00000000E3BD'. Scan count 3, logical reads 14115, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 7176 ms, elapsed time = 3618 ms.
--expression with case statements
SQL Server parse and compile time:
CPU time = 5 ms, elapsed time = 5 ms.
(163 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#temp_00000000E3BD'. Scan count 3, logical reads 14115, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 6099 ms, elapsed time = 3097 ms.
You're absolutely determined to use a calendar table. That's your choice, I accept that, I'm moving on. You don't need to keep coding inefficient ways to do calcs to "prove" that the calendar table is better. Not enough of a query here to evaluate anyway. But, again, for the record, you should only need to compute one date, the first date, you can generate the rest by iteratively adding 14 days to it, then join to that inline table.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 21, 2016 at 9:33 am
If I may step for a moment, I would like to reiterate that the point of the calendar table is to simplify queries for all users, not just experienced SQL developers. It provides one unequivocal source of truth as to whether or not a day is a workday or not (Provided I fix Thanksgiving, thanks for the correction again, Scott.) The queries against it are simple enough for junior Reports Developers to get right on the first try, or to understand when they are given an example. The savings in man-hours this provides is not inconsequential.
On the subject of performance, I'm going to remain in the "It depends." camp, the primary variables being volume and complexity of the queries. I wish I had bookmarked it, but there was a question in the forums here *years* ago that turned into a seminar on date calculations performance and the calendar table was a major part of it. If I can find it again, I will post a link to it here.
Finally, although I've enjoyed reading the discussion, I didn't expect this article to cause a debate. It seems probable that the vast majority of SQL-based business systems use calendar tables. The intent here was to simply educate newcomers to SQL about the concept so that they wouldn't have to reinvent the wheel. Best regards to you all.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply