Get month names ordered using recursion

  • Yes, rCTE is expensive until if you use only tiny records, I have used only 12 records and i think it is fine..

  • 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.



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • 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.com
  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • 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!

  • 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

  • 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.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • 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. 🙂



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • 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".

    http://www.sqlservercentral.com/articles/T-SQL/74118/

    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())

  • 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.

  • 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

  • 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