Usage of CTE - Trick with Dates

Question

Usage of CTE - Trick with Dates

Suresh Kumar Maganti

Default port

Points: 1429
More actions
June 17, 2013 at 9:28 pm

#278812

Comments posted to this topic are about the item Usage of CTE - Trick with Dates
Kindest Regards,
M Suresh Kumar

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

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

GPO SSCarpal Tunnel Points: 4574 More actions · Answer 1

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

ATFL SSC Enthusiast Points: 128 More actions · Answer 2

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.

GPO SSCarpal Tunnel Points: 4574 More actions · Answer 3

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

Ganesh Kamal SSChasing Mays Points: 612 More actions · Answer 4

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.

MartJ SSC Eights! Points: 829 More actions · Answer 5

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 🙂

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 6

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/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

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), -- 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 '-------------------------------------------------------------------------'

^{“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

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 8

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

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.

^{“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

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 10

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.

sagesmith SSC Veteran Points: 251 More actions · Answer 11

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.

sagesmith SSC Veteran Points: 251 More actions · Answer 12

sagesmith

SSC Veteran

Points: 251

June 18, 2013 at 6:02 am

#1624978

Nice!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

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 😉

^{“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

nycdotnet Right there with Babe Points: 799 More actions · Answer 14

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.