Usage of CTE - Trick with Dates

  • Comments posted to this topic are about the item Usage of CTE - Trick with Dates


    Kindest Regards,

    M Suresh Kumar

  • Interesting problem. Thanks for posting. I must confess I have not yet read the whole article, but from what I've seen (replacing a cursor or a while loop with a recursive CTE), I have a number of questions:

    1) Wouldn't it be possible to do the same thing using a tally table? See http://www.sqlservercentral.com/articles/T-SQL/62867/

    2) What metrics are you using to determine which method is faster?

    As far as I'm aware, recursive CTEs are RBAR just like cursors and while loops.

    Cheers

    GPO

    (now I'll go back and read it properly to see whether I've just made a goose out of myself!)

    ...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell

  • It is very cool to have written something of use to change the style from cursor to while loop then to CTE style.

    Nevertheless, please note Recursive function can only be up to 100 times.

    If you set the Month_Count = 120, you will see the error below:

    Msg 530, Level 16, State 1, Line 3

    The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

  • Hi adelinetfl.smkss

    You can increase the number to something greater than 100. Not advocating anything here πŸ™‚

    See Query Hints in SQL Server Books online:

    ...MAXRECURSION number

    Specifies the maximum number of recursions allowed for this query. number is a non-negative integer between 0 and 32767. When 0 is specified, no limit is applied. If this option is not specified, the default limit for the server is 100....

    ...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell

  • Thanks for posting. It will be easy for me to learn, if you give some example for invalid queries for the first point under important points.

  • Hi adelinetfl.smkss

    GPO is correct, you can up the value for max recursion. The value of 100 is kind of a built in protection to stop endless recursion.

    I did a quick blog last week on a similar topic that adds five years of holiday dates so for that the max recursion needed to be 1825, you can view it here: http://devondba.blogspot.co.uk/2013/06/create-table-of-dates-showing-holidays.html

    Hope this helps

    Martyn

    P.S. the use of the semi colon before WITH is to end the previous statement so it's only needed if there is one.

    For some further reading try http://stevestedman.com πŸ™‚

  • GPO (6/17/2013)


    Interesting problem. Thanks for posting. I must confess I have not yet read the whole article, but from what I've seen (replacing a cursor or a while loop with a recursive CTE), I have a number of questions:

    1) Wouldn't it be possible to do the same thing using a tally table? See http://www.sqlservercentral.com/articles/T-SQL/62867/

    2) What metrics are you using to determine which method is faster?

    As far as I'm aware, recursive CTEs are RBAR just like cursors and while loops.

    Cheers

    GPO

    (now I'll go back and read it properly to see whether I've just made a goose out of myself!)

    I will have to agree with GPO here

    CTE's may or may not be better in terms of performance compared to CURSORS

    The problem you have taken can be solved using a Tally table as well

    Please go through the article below which compares the performance of CTE's and Tally table in such situations

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


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Ramp up the output row count to about a quarter million and blackhole the output (an attempt to eliminate io from the execution time), and a tally table version runs about 30 times faster than the recursive CTE script. rCTE's are expensive and I wouldn't recommend using a rCTE for something so simple as this - it's a sledgehammer on a nut.

    What might come as a surprise is how fast it runs - around 2 seconds to generate 220,000 rows. That's not really RBAR figures and compared to some data generator times it's pretty darned good. Not as good as the inline tally table though:

    -- Alter the seed table to support a larger data set

    /*

    ALTER TABLE dbo.abc ALTER COLUMN Month_Count INT NULL

    ALTER TABLE dbo.abc ALTER COLUMN Date_Field DATETIME NULL

    GO

    UPDATE dbo.abc SET Month_Count = Month_Count*5000

    GO

    */

    ------------ Inline Tally version ------------------

    -- black hole variables:

    DECLARE @SeqNo SMALLINT, @Date_Field DATETIME, @Payment DECIMAL (10,2)

    DECLARE @StartTime DATETIME;

    -- time store

    SET @StartTime = GETDATE()

    ;WITH E1(n) AS (

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), -- 10 rows

    E2(n) AS (SELECT 1 FROM E1 a, E1 b), -- 10x10 rows

    E3(n) AS (SELECT 1 FROM E2 a, E2 b, E2 c) -- 100x100x100 rows

    SELECT

    @SeqNo = SeqNo,

    @Date_Field = DATEADD(MONTH,x.n,Date_Field),

    @Payment = Payment

    FROM dbo.abc

    CROSS APPLY(SELECT TOP(Month_Count) n = -1+ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E3) x

    SELECT DATEDIFF(MS,@StartTime,GETDATE())

    PRINT '-------------------------------------------------------------------------'

    -------------- rCTE version ------------------------------

    SET @StartTime = GETDATE()

    ;with CTE_Base (SeqNo, Date_Field, Month_Count, Payment, Begin_Date, End_Date, Frequency)

    as

    (select SeqNo, Date_Field, Month_Count, Payment, Date_Field, dateadd(mm, Month_Count-1, Date_Field), 1 from dbo.abc

    union all

    select SeqNo, dateadd(mm, Frequency, Date_Field), Month_Count, Payment, Begin_Date, End_Date, Frequency

    from CTE_Base

    where dateadd(mm, Frequency, Date_Field) between Begin_Date and End_Date)

    select

    @SeqNo = SeqNo,

    @Date_Field = Date_Field,

    @Payment = Payment

    from CTE_Base

    where Date_Field between Begin_Date and End_Date

    order by SeqNo, Date_Field

    OPTION(MAXRECURSION 0)

    SELECT DATEDIFF(MS,@StartTime,GETDATE())

    PRINT '-------------------------------------------------------------------------'

    [font="Arial"]β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • I thought this looked a good candidate for CROSS APPLY:

    insert into dbo.def 
    (
    SeqNo,
    Date_Field,
    Payment
    )
    SELECT A.Seqno,
    X.Date_Field,
    A.Payment
    FROM dbo.abc A
    CROSS APPLY (SELECT DATEADD(mm, T.N, A.Date_field) Date_Field
    FROM dbo.Tally T
    WHERE T.N BETWEEN 0
    AND A.Month_Count - 1) AS X

    I have found that the performance of recursive CTEs degrades a lot as the number of rows increase.

    You will need to create Jeff Moden's tally table to use it

  • Jonathan AC Roberts (6/18/2013)


    I thought this looked a good candidate for CROSS APPLY:

    insert into dbo.def (SeqNo, Date_Field, Payment)

    SELECT A.Seqno, X.Date_Field, A.Payment

    FROM dbo.abc A

    CROSS APPLY (SELECT DATEADD(mm, T.N, A.Date_field) Date_Field

    FROM dbo.tsqlc_Tally T

    WHERE T.N BETWEEN 0 AND A.Month_Count - 1) AS X

    ...

    It is, see the post above yours πŸ˜€

    I have found that the performance of recursive CTEs degrades a lot as the number of rows increase.

    Most of the rCTE's I've played with haven't stacked with rowcount - execution time has increased arithmetically with rows processed.

    [font="Arial"]β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • ChrisM@Work (6/18/2013)


    It is, see the post above yours πŸ˜€

    I see πŸ˜›

    It can also be done even more simply with an INNER JOIN and tally table (and is faster than the rCTE version)

    SELECT A.Seqno,
    DATEADD(month, T.N, A.Date_field) Date_Field,
    A.Payment
    FROM dbo.abc A
    INNER JOIN dbo.tsqlc_Tally T
    ON T.N < A.Month_Count

    I use this tally table it starts from zero and has just over a million rows.

    Most of the rCTE's I've played with haven't stacked with rowcount - execution time has increased arithmetically with rows processed.

    Yes, they look elegant but I generally use them as only a last resort as I find their performance unpredictable.

  • Please post the times each approach takes, this is the only basis for a conclusion. As others have already mentioned, in many scenarios the cursor far outperforms the recursive cte.

  • Nice!

  • sagesmith (6/18/2013)


    Please post the times each approach takes, this is the only basis for a conclusion. As others have already mentioned, in many scenarios the cursor far outperforms the recursive cte.

    Over to you then. Sample data setup is a page or two back. I've compared iTally vs rCTE here:

    http://www.sqlservercentral.com/Forums/FindPost1464526.aspx

    Feel free to write your fastest cursor-based solution πŸ˜‰

    [font="Arial"]β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • Interesting article. Regarding the paradox of semicolons on CTEs - the rule is "the statement before the CTE has to end with a semicolon". The reason is that the WITH keyword is overloaded in T-SQL. Without the semicolon, SQL can't tell if you're saying with as a statement start or qualifying something about the previous statement.

    People have gotten used to putting a semicolon in front of the WITH, but this is not required - you can just as easily put it at the end of your last statement.

    This also explains why this causes an error in an inline Table-value function. Inline table-value functions must be only a single statement. Therefore if you use a semi-colon in it (which is the statement terminator symbol), then you are causing the TVF to have two statements and it breaks.

Viewing 15 posts - 1 through 15 (of 69 total)

You must be logged in to reply to this topic. Login to reply