June 30, 2018 at 4:14 am
Comments posted to this topic are about the item Using a CTE as a Tally Table
June 30, 2018 at 10:36 pm
This part of your code causes a full table scan regardless of the TOP 52 because of the ORDER BY in the ROW_NUMBER(). Since you're doing the ORDER BY on the StockCode table, it also does a shedload of unnecessary reads. Also, there is no guarantee that you'll return 52 rows because there's no guarantee that the Data.Stock table will start with or be maintained in such a fashion as to always have at least 52 rows in it because it's a user table rather than a utility or fix system table.
SELECT TOP 52 ROW_NUMBER() OVER (ORDER BY StockCode) AS Num
FROM Data.Stock
This part of your code causes a full table scan because the WHERE clause is non-SARGable as well as there being no indexes on the table.
SELECT SUM(TotalSalePrice) AS SalesForTheWeek
,DatePart(wk, SaleDate) AS WeekNo
FROM Data.Sales
WHERE YEAR(SaleDate) = 2016
GROUP BY DatePart(wk, SaleDate)
Also, you should always use the 2 part naming convention both in the FROM clause (which you've dome) and the SELECT clause of a joined select (which you've not done).
Also, what is a "week"? DATEPART(wk) will only return 2 days for the first week of Jan 2016. The only reason why it returns 7 days for the last week of 2016 is because you get lucky and average of once every 7 years.
Last but not least, your sample data model in the PrestigeCars database is devoid of any and all Primary Keys and indexes.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 1, 2018 at 1:12 am
Cannot see any reason for scanning the table to produce the week numbers when it can be done more efficiently without it.
😎
Here is a far better method with only two constant scans and no sort operator.
USE TEEST;
GO
SET NOCOUNT ON;
;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) X(N))
, NUMS(N) AS (SELECT TOP (52) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N
FROM T T1,T T2)
SELECT
NM.N AS WEEK_NUM
FROM NUMS NM
ORDER BY NM.N;
August 9, 2018 at 2:20 am
Rather use a recursive cte to create your numbers.
WITH cte AS (
SELECT 1 as Num
UNION ALL
SELECT Num + 1 FROM cte WHERE Num < 52
)
SELECT * FROM cte
This method works well for populating Calendar tables, too.
But if you insist on using an existing table, sys.columns is just about guaranteed to always have enough entries.
August 9, 2018 at 2:47 am
BluePeter - Thursday, August 9, 2018 2:20 AMRather use a recursive cte to create your numbers.WITH cte AS (
SELECT 1 as Num
UNION ALL
SELECT Num + 1 FROM cte WHERE Num < 52
)
SELECT * FROM cteThis method works well for populating Calendar tables, too.
But if you insist on using an existing table, sys.columns is just about guaranteed to always have enough entries.
Be careful here, the recursive CTE is much slower than the inline tally table CTE method I posted, strongly advice against using a recursive CTE for this purpose.
😎
August 9, 2018 at 4:51 am
August 9, 2018 at 5:04 am
Thank you for the article!
In the discussion everything important is mentioned.But the most important: Without this article no discussion would be happen. Therefore Thank you once more!
Gerhard P.Advanced BI DeveloperAustria
August 9, 2018 at 5:07 am
Eirikur Eiriksson - Thursday, August 9, 2018 2:47 AMBe careful here, the recursive CTE is much slower than the inline tally table CTE method I posted, strongly advice against using a recursive CTE for this purpose.
😎
Agreed, it doesn't scale upwards, 10,000 is slower with a recursive cte.
But no difference for 52 (or 53)
I'll keep yours in mind, never seen it before.
August 9, 2018 at 8:22 am
BluePeter - Thursday, August 9, 2018 5:07 AMEirikur Eiriksson - Thursday, August 9, 2018 2:47 AMBe careful here, the recursive CTE is much slower than the inline tally table CTE method I posted, strongly advice against using a recursive CTE for this purpose.
😎Agreed, it doesn't scale upwards, 10,000 is slower with a recursive cte.
But no difference for 52 (or 53)
I'll keep yours in mind, never seen it before.
The reason you think there is no difference is because of what you're measuring and the way your measuring it. There's another thing to consider, as well...
If you practice the wrong way just because of low row counts, you'll never get good at the right way. You also don't have to actually build the cCTE method (Cascading CTEs rather than Recursive CTEs) each and every time you want a sequence of numbers. You can easily build an iTVF (Inline Table Valued Function) that will do that for you and, unlike scalar functions and mTVFs (Mult-statment Table Valued Functions), provides no additional hit if you refer to it within other iTVFs.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 9, 2018 at 8:30 am
53 is probably sufficient, but if a leap year starts on Saturday then Sunday Dec 31 will be in week 54. Hopefully they don't mind missing Sunday sales on New Year's Eve once every 28 years. This happens in 1916, 1944, 1972, 2000, 2028.
You could merge that day into week 53:
SELECT SUM(TotalSalePrice) AS SalesForTheWeek
,CASE w.WeekNo WHEN 54 THEN 53 ELSE w.WeekNo END AS WeekNo
FROM Data.Sales
CROSS APPLY ( SELECT WeekNo = DatePart(wk, SaleDate) ) w
WHERE YEAR(SaleDate) = 2016
GROUP BY CASE w.WeekNo WHEN 54 THEN 53 ELSE w.WeekNo END
August 9, 2018 at 8:37 am
Scott Coleman - Thursday, August 9, 2018 8:30 AM53 is probably sufficient, but if a leap year starts on Saturday then Sunday Dec 31 will be in week 54. Hopefully they don't mind missing Sunday sales on New Year's Eve once every 28 years. This happens in 1916, 1944, 1972, 2000, 2028.You could merge that day into week 53:
SELECT SUM(TotalSalePrice) AS SalesForTheWeek
,CASE w.WeekNo WHEN 54 THEN 53 ELSE w.WeekNo END AS WeekNo
FROM Data.Sales
CROSS APPLY ( SELECT WeekNo = DatePart(wk, SaleDate) ) w
WHERE YEAR(SaleDate) = 2016
GROUP BY CASE w.WeekNo WHEN 54 THEN 53 ELSE w.WeekNo END
Wow! Did not know that. We put the last "5" weeks in period 12, so we will likely merge that "extra" day into week 53 also. Will put a slight skew on YoY figures, but so does Easter, so we will live with it :-/ Tks for that
August 9, 2018 at 8:38 am
Jeff Moden - Thursday, August 9, 2018 8:22 AMBluePeter - Thursday, August 9, 2018 5:07 AMEirikur Eiriksson - Thursday, August 9, 2018 2:47 AMBe careful here, the recursive CTE is much slower than the inline tally table CTE method I posted, strongly advice against using a recursive CTE for this purpose.
😎Agreed, it doesn't scale upwards, 10,000 is slower with a recursive cte.
But no difference for 52 (or 53)
I'll keep yours in mind, never seen it before.The reason you think there is no difference is because of what you're measuring and the way your measuring it. There's another thing to consider, as well...
If you practice the wrong way just because of low row counts, you'll never get good at the right way. You also don't have to actually build the cCTE method (Cascading CTEs rather than Recursive CTEs) each and every time you want a sequence of numbers. You can easily build an iTVF (Inline Table Valued Function) that will do that for you and, unlike scalar functions and mTVFs (Mult-statment Table Valued Functions), provides no additional hit if you refer to it within other iTVFs.
I get that Jeff... Thanx.
My original point was not to rely on having rows in a User table. And the recursive cte method was the best I knew.
I always test code to scale. Glad to see this new method for generating numbers on the fly.
And it's a good tip about INLINE table valued functions.
August 9, 2018 at 9:18 am
Eirikur Eiriksson - Sunday, July 1, 2018 1:12 AMCannot see any reason for scanning the table to produce the week numbers when it can be done more efficiently without it.
😎Here is a far better method with only two constant scans and no sort operator.
USE TEEST;
GO
SET NOCOUNT ON;;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) X(N))
, NUMS(N) AS (SELECT TOP (52) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N
FROM T T1,T T2)
SELECT
NM.N AS WEEK_NUM
FROM NUMS NM
ORDER BY NM.N;
Eirikur, my only criticism of your code is the beginninator you have starting the CTE. Why? the previous statement is terminated by a semicolon so there is no need for the extraneous semicolon prior to the WITH for the CTE. Sorry, just a really big pet peeve of mine. Plus, it really looks weird seeing a statement starting with and ending with a semicolon.
August 9, 2018 at 10:20 am
Lynn Pettis - Thursday, August 9, 2018 9:18 AMEirikur Eiriksson - Sunday, July 1, 2018 1:12 AMCannot see any reason for scanning the table to produce the week numbers when it can be done more efficiently without it.
😎Here is a far better method with only two constant scans and no sort operator.
USE TEEST;
GO
SET NOCOUNT ON;;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) X(N))
, NUMS(N) AS (SELECT TOP (52) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N
FROM T T1,T T2)
SELECT
NM.N AS WEEK_NUM
FROM NUMS NM
ORDER BY NM.N;Eirikur, my only criticism of your code is the beginninator you have starting the CTE. Why? the previous statement is terminated by a semicolon so there is no need for the extraneous semicolon prior to the WITH for the CTE. Sorry, just a really big pet peeve of mine. Plus, it really looks weird seeing a statement starting with and ending with a semicolon.
Hi mate, the reason for me using the beginninator is because that way, I can copy and paste / snippet insert the code without worries, regardless of the previous statement being terminated or not.
😎
August 9, 2018 at 11:03 am
BluePeter - Thursday, August 9, 2018 8:38 AMJeff Moden - Thursday, August 9, 2018 8:22 AMBluePeter - Thursday, August 9, 2018 5:07 AMEirikur Eiriksson - Thursday, August 9, 2018 2:47 AMBe careful here, the recursive CTE is much slower than the inline tally table CTE method I posted, strongly advice against using a recursive CTE for this purpose.
😎Agreed, it doesn't scale upwards, 10,000 is slower with a recursive cte.
But no difference for 52 (or 53)
I'll keep yours in mind, never seen it before.The reason you think there is no difference is because of what you're measuring and the way your measuring it. There's another thing to consider, as well...
If you practice the wrong way just because of low row counts, you'll never get good at the right way. You also don't have to actually build the cCTE method (Cascading CTEs rather than Recursive CTEs) each and every time you want a sequence of numbers. You can easily build an iTVF (Inline Table Valued Function) that will do that for you and, unlike scalar functions and mTVFs (Mult-statment Table Valued Functions), provides no additional hit if you refer to it within other iTVFs.
I get that Jeff... Thanx.
My original point was not to rely on having rows in a User table. And the recursive cte method was the best I knew.
I always test code to scale. Glad to see this new method for generating numbers on the fly.
And it's a good tip about INLINE table valued functions.
Awesome. Thanks for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply