March 13, 2012 at 5:00 am
Yes, rCTE is expensive until if you use only tiny records, I have used only 12 records and i think it is fine..
March 13, 2012 at 5:11 am
First of all, I am really a fan of CTEs, as they give a lot of flexibility and readable code. However, in this case I find that the CTE is abused. It is really too complex code for a very simple task. My solution would be a table, possibly with a language column to be able to store month names in various languages.
March 13, 2012 at 6:38 am
david.howell (3/13/2012)
Anyway, what you are doing is tiny - only 12 rows so none of this matters....{snip}... Thanks for the article, it prompted me to read the rCTE BOL entry. 🙂
Read the following article for the reason why that's not such a good thing to say especially in this case. And, no, the performance problems associated with "counting rCTEs" aren't documented in BOL.
{EDIT} Sorry, fogot the link. Here it is...
http://www.sqlservercentral.com/articles/T-SQL/74118/
--Jeff Moden
Change is inevitable... Change for the better is not.
March 13, 2012 at 6:40 am
okbangas (3/13/2012)
First of all, I am really a fan of CTEs, as they give a lot of flexibility and readable code. However, in this case I find that the CTE is abused. It is really too complex code for a very simple task. My solution would be a table, possibly with a language column to be able to store month names in various languages.
I think this sounds like a much better option.
Regards,
Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)
http://basitaalishan.comMarch 13, 2012 at 6:42 am
Jonathan AC Roberts (3/13/2012)
Peformance won't be an issue./code]
Comparatively speaking, it's a large issue especially if someone uses it for something else, Jonathan. Please see the following article and search for "red sky rocket".
http://www.sqlservercentral.com/articles/T-SQL/74118/
--Jeff Moden
Change is inevitable... Change for the better is not.
March 13, 2012 at 6:46 am
Krtyknm (3/13/2012)
Yes, rCTE is expensive until if you use only tiny records, I have used only 12 records and i think it is fine..
Thats a very common misconception especially where IO (reads) is concerned. Please see the following article.
http://www.sqlservercentral.com/articles/T-SQL/71511/
--Jeff Moden
Change is inevitable... Change for the better is not.
March 13, 2012 at 6:50 am
abhishekgupta109 (3/13/2012)
Interesting one....but why would you display a month name, if we do not have any data for it. (just for the sake of displaying a blank report.)
Why not? Many users would prefer to select an argument and see the report return nothing than not see the argument at all. That said, the point is moot as the decision will most likely be driven by user requirements.
Consider the scenario where multiple data regions, each sourced from different tables, are to be fed by a single month parameter.
Which table do you source your month parameter dataset from? If months are missing from the adopted source table, other data regions (sourced from tables where the month is NOT missing) would be impacted. A recursive CTE would provide all arguments all of the time.
Moreover, the recursive CTE solution scales better. Sourcing the parameter list from an underlying table becomes more expensive as the table grows while recursive CTE solution does not. Even over a small source table (150,000 records), the cost of the CTE is millions of times lower.
If you start employing cascading parameters, the cost of generating the month parameters could easily become a nuissance.
IMO, this is a great application of a recursive CTE. Thanks!
March 13, 2012 at 6:54 am
andrew.diniz (3/13/2012)
Consider the scenario where multiple data regions, each sourced from different tables, are to be fed by a single month parameter.Which table do you source your month parameter dataset from? If months are missing from the adopted source table, other data regions (sourced from tables where the month is NOT missing) would be impacted. A recursive CTE would provide all arguments all of the time.
Agreed that a query returning all months can be more interesting than an adaptive one, especially if multiple sources are present.
andrew.diniz (3/13/2012)
Moreover, the recursive CTE solution scales better.
But saying the CTE scales better? I hope you are just referring that it scales better than cascading parameters, because there are plenty of scripts and links to articles that contradict that statement in this discussion.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 13, 2012 at 7:05 am
andrew.diniz (3/13/2012)
...Moreover, the recursive CTE solution scales better. Sourcing the parameter list from an underlying table becomes more expensive as the table grows while recursive CTE solution does not. Even over a small source table (150,000 records), the cost of the CTE is millions of times lower.
Apart from rCTE's scaling linearly, this statement is not correct. Read Jeff's article. A table, however large or small, will always perform many times faster than a rCTE.
...IMO, this is a great application of a recursive CTE...
It's a poor implementation of a rCTE for reasons already mentioned. rCTE's have to do much more work than this to be a worthwhile choice. A CTE as shown by Koen is a far better choice.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 13, 2012 at 7:26 am
Krtyknm (3/13/2012)
Nice thoughts and suggestions!! But My idea is to keep the SQL script very simple, instead of creating the monthname statically we can go with the dynamic way.Thanks,
Karthik
Recursive CTE is not a simple way to do this. They have their uses, but this is a poor choice for using them.
March 13, 2012 at 7:28 am
Koen Verbeeck (3/13/2012)
alfredoapereira (3/13/2012)
Hi you all.Well, I prefer to use a tally table. more clean.
I prefer to use a calendar table, even more clean 😉
I use a Tally table to maintain my calendar table. 🙂
March 13, 2012 at 9:02 am
Jeff Moden (3/13/2012)
Jonathan AC Roberts (3/13/2012)
Peformance won't be an issue./code]Comparatively speaking, it's a large issue especially if someone uses it for something else, Jonathan. Please see the following article and search for "red sky rocket".
Yes, I know it is poor for larger selects but for 12 items it's not going to grind his server into the ground, that was my point, so don't think I am advocating using recursive CTEs unless they really are necessary. Anyway, just as a quick demonstration of the performance of the recursive CTE with 12 items compared to your tally table here is some code.
PRINT 'Recursive CTE Start'
DECLARE @StartTime datetime
DECLARE @x int
DECLARE @y varchar(20)
DECLARE @MaxIterations int
SET @MaxIterations = 10000
SET @StartTime = GETDATE()
DECLARE @i int
SET @i = 0
WHILE @i < @MaxIterations
BEGIN
;WITH CTEMonth
AS
(
SELECT 1 AS MonNum
UNION ALL
SELECT MonNum + 1 -- add month number to 1 recursively
FROM CTEMonth
WHERE MonNum < 12 -- just to restrict the monthnumber upto 12
)
SELECT
@x = MonNum
,@y = DATENAME(MONTH,DATEADD(MONTH,MonNum,0)- 1) -- function to list the monthname.
FROM CTEMonth
SET @i = @i + 1
END
--END WHILE
PRINT 'Recursive CTE milliseconds'
PRINT DATEDIFF(ms, @StartTime , GETDATE())
PRINT 'Recursive Tally Start'
SET @StartTime = GETDATE()
SET @i = 0
WHILE @i < @MaxIterations
BEGIN
SELECT @x = N ,
@y = DATENAME(MONTH,DATEADD(MONTH,N,0)- 1) -- function to list the monthname.
FROM dbo.Tally
WHERE N <= 12
ORDER BY N
SET @i = @i + 1
END
--END WHILE
PRINT 'Tally milliseconds'
PRINT DATEDIFF(ms, @StartTime , GETDATE())
March 13, 2012 at 9:07 am
Koen Verbeeck (3/13/2012)
andrew.diniz (3/13/2012)
Consider the scenario where multiple data regions, each sourced from different tables, are to be fed by a single month parameter.Which table do you source your month parameter dataset from? If months are missing from the adopted source table, other data regions (sourced from tables where the month is NOT missing) would be impacted. A recursive CTE would provide all arguments all of the time.
Agreed that a query returning all months can be more interesting than an adaptive one, especially if multiple sources are present.
andrew.diniz (3/13/2012)
Moreover, the recursive CTE solution scales better.But saying the CTE scales better? I hope you are just referring that it scales better than cascading parameters, because there are plenty of scripts and links to articles that contradict that statement in this discussion.
I'm saying that a rCTE to generate a chronologically sorted list of the 12 months of the year is faster than ordering the list of distinct 'month names' computed over a datetime field within a table.
March 13, 2012 at 9:12 am
Just because I was in the mood for a little fun but another option is to use the syslanguages table to get the information like you want. There is a months field and you could use it to get alternate languages by supplying a different id if you need. The field is a comma delimited list but you can break it up into rows using XML like so. May not be the most efficient but is another option.
CREATE PROCEDURE Usp_GetMonthnames
@LangID int = 0
AS
BEGIN
SET NOCOUNT ON;
DECLARE @x XML
SET @x = CAST((select replace((select months from master.sys.syslanguages where langid = @LangID for xml path),',','</months></row><row><months>')) AS XML)
SELECT
row_number() over (order by t.c) Month_Number,
t.c.value('.','varchar(50)') as Months
FROM
@x.nodes('row/months') t(c)
END
GO
March 13, 2012 at 9:17 am
Apart from rCTE's scaling linearly, this statement is not correct. Read Jeff's article. A table, however large or small, will always perform many times faster than a rCTE.
That's simply not true. In 2005 the cost of a rCTE to generate the list of 12 chronologically sorted months is a fraction of the cost of selecting distinct month names across a table.
It's a poor implementation of a rCTE for reasons already mentioned. rCTE's have to do much more work than this to be a worthwhile choice. A CTE as shown by Koen is a far better choice.
Far more work than what?
Viewing 15 posts - 31 through 45 (of 129 total)
You must be logged in to reply to this topic. Login to reply