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
Jonathan AC Roberts
Jonathan AC Roberts
SSC Eights!
SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)

Group: General Forum Members
Points: 915 Visits: 1907
ChrisM@Work (6/18/2013)

It is, see the post above yours :-D

I see :-P
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 [url=http://beyondrelational.com/puzzles/tsql/posts/english/1984/tsql-challenges-tally-table-script.aspx][/url] 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
sagesmith
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 65
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
sagesmith
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 65
Nice!
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16798 Visits: 19557
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
Exploring Recursive CTEs by Example Dwain Camps
nycdotnet
nycdotnet
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 360
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.
Kingston Dhasian
Kingston Dhasian
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3755 Visits: 5180
ChrisM@Work (6/18/2013)
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 ;-)







:-D That was really funny.
But, I think( and truly hope ) it was a typo by the poster when he said "cursor far outperforms the recursive cte".
If in case he really meant it, even I would love to be enlightened :-)


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/
sagesmith
sagesmith
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 65
ChrisM@Work (6/18/2013)
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 ;-)







My comment was for the author of the article who was comparing rCTE to Cursor. Your Tally approach is great for that sample scenario, no argument. I have found, as others have mentioned, that as you attempt to scale the rCTE approach the performance seems to go in an inverse relationship to the amount of rows. In these scenarios a Cursor or the Tally approach (inline or maybe faster as a Numbers table on disk?) does much better than the rCTE.

I have seen these enlightened articles on rCTE before and I think they should come with a word of caution (unless I'm the one who is not enlightened? please prove me wrong).
Habib Salim
Habib Salim
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 30
If I may make a fine point:
It is more accurate to say that in a multi-statement batch, the statement (query) preceding a CTE should be terminated by a semi-colon.

I find the following just a little easier to grasp: (Change MonthCount to int)
With CTE_Base (SeqNo, Begin_Date, Month_Count, Payment)
as
(select SeqNo, Date_Field AS Begin_Date,Month_Count, Payment from dbo.abc
UNION all
select SeqNo, dateadd(mm, 1, Begin_Date), Month_Count -1, Payment from CTE_Base
where Month_Count > 1
)


SELECT SeqNo, Begin_Date, Month_Count, Payment
FROM CTE_Base
order by SeqNo, Begin_Date
sagesmith
sagesmith
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 65
your Tally kills the rCTE and hybrid Cursor/Numbers as you can see. On my box:
Inline Tally = 146 milliseconds
rCTE = 2113 milliseconds
Cursor/Numbers = 1183 milliseconds

The Cursor based solution is only partially so, I still couldn't bring myself to copy the author's while inner loop...yikes. But the point I'm trying to make is with bashing cursors:

http://stackoverflow.com/questions/287445/why-do-people-hate-sql-cursors-so-much

see most popular answer under The Size Issue heading.


--If the table exists, drop it.
if exists (select * from sys.tables
where name = 'abc'
and Schema_Name(schema_id) = 'dbo'
and [type] = 'U')
drop table dbo.abc
go
--Create the source table, dbo.abc.
create table dbo.abc
(SeqNo smallint
,Date_Field datetime
,Month_Count int
,Payment decimal(10,2))
go

--Populate the source table, dbo.abc
insert into dbo.abc (SeqNo, Date_Field, Month_Count, Payment)
values (1, '20090101', 95000, 100)
,(2, '20100101', 7000, 200)
,(3, '20110101', 5000, 300)
go
--If exists, drop the destination table dbo.def
if exists (select * from sys.tables
where name = 'def'
and Schema_NAME(schema_id) = 'dbo'
and [type] = 'U')
drop table dbo.def
go
--Create the destination table, dbo.def
create table dbo.def
(SeqNo smallint
,Date_Field smalldatetime
,Payment decimal(10,2))
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 'Inline Tally = ' + CAST(DATEDIFF(MS,@StartTime,GETDATE()) AS VARCHAR) + ' milliseconds'
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 'rCTE = ' + CAST(DATEDIFF(MS,@StartTime,GETDATE()) AS VARCHAR) + ' milliseconds'
PRINT '-------------------------------------------------------------------------'

-------------- CURSOR version ------------------------------



SET @StartTime = GETDATE()
declare @l_SeqNo int
,@l_date_field DATETIME
,@l_Month_Count int
,@l_Payment decimal(10, 2)
,@l_counter SMALLINT
,@l_max_month_count INT = (SELECT MAX(Month_Count) FROM dbo.abc);

SELECT TOP (@l_max_month_count) IDENTITY(int,1,1) AS number
into #Numbers
FROM sys.objects s1
CROSS JOIN sys.objects s2


select @l_counter = 0
set nocount on;
declare i_Cursor CURSOR FAST_FORWARD
FOR
select SeqNo, Date_Field, Month_Count, Payment from dbo.abc

open i_Cursor

fetch next from i_Cursor into
@l_SeqNo
,@l_date_field
,@l_Month_Count
,@l_Payment

while @@fetch_status = 0
begin
select number, dateadd(mm, number-1, @l_date_field), @l_Payment
FROM #Numbers
WHERE number<=@l_Month_Count

fetch next from i_Cursor into
@l_SeqNo
,@l_date_field
,@l_Month_Count
,@l_Payment
end
close i_Cursor
deallocate i_Cursor
set nocount off;

SELECT 'Cursor/Numbers = ' + CAST(DATEDIFF(MS,@StartTime,GETDATE()) AS VARCHAR) + ' milliseconds'
PRINT '-------------------------------------------------------------------------'



DROP TABLE #Numbers;



ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16798 Visits: 19557
sagesmith (6/18/2013)
ChrisM@Work (6/18/2013)
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 ;-)







My comment was for the author of the article who was comparing rCTE to Cursor. Your Tally approach is great for that sample scenario, no argument. I have found, as others have mentioned, that as you attempt to scale the rCTE approach the performance seems to go in an inverse relationship to the amount of rows. In these scenarios a Cursor or the Tally approach (inline or maybe faster as a Numbers table on disk?) does much better than the rCTE.

I have seen these enlightened articles on rCTE before and I think they should come with a word of caution (unless I'm the one who is not enlightened? please prove me wrong).


I can show you rCTE's scaling well and performing remarkably well too - threads and articles are linked in Dwain's article in my signature below. One or two will surprise you including a super-fast distinct documented by Paul White, and very fast running totals. I can tell you haven't yet read it ;-)
I've seen rCTE's performing poorly against other coding methods too. Even posted one or two. What I haven't seen is this "inverse relationship" i.e. where the rCTE fails to scale. Do you have an example?

“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