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 Tuesday, June 18, 2013 5:22 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:23 AM
Points: 338, Visits: 1,429
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 [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.
Post #1464578
Posted Tuesday, June 18, 2013 5:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 5:20 PM
Points: 29, Visits: 42
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.
Post #1464596
Posted Tuesday, June 18, 2013 6:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 5:20 PM
Points: 29, Visits: 42
Nice!
Post #1464599
Posted Tuesday, June 18, 2013 6:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:19 AM
Points: 6,721, Visits: 13,832
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
Post #1464603
Posted Tuesday, June 18, 2013 6:27 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, September 4, 2014 2:36 PM
Points: 136, Visits: 324
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.
Post #1464607
Posted Tuesday, June 18, 2013 6:30 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:25 AM
Points: 2,658, Visits: 4,733
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







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/
Post #1464610
Posted Tuesday, June 18, 2013 6:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 5:20 PM
Points: 29, Visits: 42
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).
Post #1464615
Posted Tuesday, June 18, 2013 7:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 6, 2014 5:11 AM
Points: 4, Visits: 27
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

Post #1464659
Posted Tuesday, June 18, 2013 7:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 5:20 PM
Points: 29, Visits: 42
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;


Post #1464663
Posted Tuesday, June 18, 2013 7:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:19 AM
Points: 6,721, Visits: 13,832
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
Post #1464665
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse