SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Usage of CTE - Trick with Dates


Usage of CTE - Trick with Dates

Author
Message
Suresh Kumar Maganti
Suresh Kumar Maganti
SSC-Addicted
SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)

Group: General Forum Members
Points: 493 Visits: 125
Comments posted to this topic are about the item Usage of CTE - Trick with Dates


Kindest Regards,

M Suresh Kumar

GPO
GPO
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1734 Visits: 1938
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 one's work is terribly important.
Bertrand Russell

ATFL
ATFL
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 21
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
GPO
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1734 Visits: 1938
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 one's work is terribly important.
Bertrand Russell

Ganesh Kamal
Ganesh Kamal
SSC-Addicted
SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)

Group: General Forum Members
Points: 473 Visits: 78
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
MartJ
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 231
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
Kingston Dhasian
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6050 Visits: 5280
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
ChrisM@Work
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41749 Visits: 20006
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 '-------------------------------------------------------------------------'






“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
Exploring Recursive CTEs by Example Dwain Camps
Jonathan AC Roberts
Jonathan AC Roberts
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1844 Visits: 1932
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 [url=http://www.sqlservercentral.com/articles/T-SQL/62867][/url]
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41749 Visits: 20006
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 :-D


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
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search