Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Usage of CTE - Trick with Dates Expand / Collapse
Author
Message
Posted Monday, June 17, 2013 9:28 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 27, 2013 9:51 AM
Points: 141, Visits: 113
Comments posted to this topic are about the item Usage of CTE - Trick with Dates


Kindest Regards,

M Suresh Kumar

Post #1464445
Posted Monday, June 17, 2013 10:18 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:35 PM
Points: 831, Visits: 1,581
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
Post #1464451
Posted Monday, June 17, 2013 11:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 4:50 AM
Points: 6, Visits: 17
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.
Post #1464458
Posted Monday, June 17, 2013 11:50 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:35 PM
Points: 831, Visits: 1,581
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
Post #1464459
Posted Tuesday, June 18, 2013 12:37 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, May 18, 2014 4:48 PM
Points: 414, Visits: 74
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.
Post #1464469
Posted Tuesday, June 18, 2013 12:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 15, 2014 6:38 AM
Points: 41, Visits: 180
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
Post #1464471
Posted Tuesday, June 18, 2013 1:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:51 AM
Points: 2,657, Visits: 4,732
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/
Post #1464478
Posted Tuesday, June 18, 2013 3:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 6,719, Visits: 13,828
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
Post #1464526
Posted Tuesday, June 18, 2013 3:53 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 8:23 AM
Points: 338, Visits: 1,429
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]
Post #1464536
Posted Tuesday, June 18, 2013 3:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 6,719, Visits: 13,828
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1464541
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse