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


CTE Performance


CTE Performance

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)

Group: Administrators
Points: 81963 Visits: 19217
The links should be fixed and my apologies to everyone. We've tightened security and I forgot .sql scripts will not download. They are renamed to .txt.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Charles Kincaid
Charles Kincaid
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2133 Visits: 2384

Ah ha. Right you are. So we can only go on the available evidence. Your charts (even with broken links ) and the pictures of the execution plans are enough to make me shy away from CTEs in this looping type of scenario.

Always the compromises: Ease of code vs. production performance. The folks in Redmond don't like to talk about benchmarks. It's groups like this where the authors take the time to write up a finding and readers contribute their experience that are of great benefit.



ATBCharles Kincaid
VadimK
VadimK
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 157
It's kind of funny. I posted similar question about CTE performance almost a year ago:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=149&messageid=289849

but I guess it was too early for anybody to respond.



sqldba-294117
sqldba-294117
Mr or Mrs. 500
Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)Mr or Mrs. 500 (554 reputation)

Group: General Forum Members
Points: 554 Visits: 396
nice article, but besides stating CTE as a neat way to implement recursive queries. this article is missing concept & meaning about CTE.
Tito Jermaine
Tito Jermaine
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 14
The performance isn't necessarily all bad. I had a query in which a large portion of it was splitting a delimited string into multiple columns. I originally used a function I used on SQL 2000 which was basically a loop using CHARINDEX and SUBSTRING to iterate over each "column" in the string and split it into a table. I rewrote the function to use a recursive CTE rather than the loop, but everything else was essentially the same. The stored procedure I was using it in that was taking over 50 minutes. After that single change, it was running around 16 minutes.
Jason Adams
Jason Adams
Say Hey Kid
Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)Say Hey Kid (677 reputation)

Group: General Forum Members
Points: 677 Visits: 69

The intial issue with the Key Lookup in test case 2 was caused with a poor index.

If the following index is added, the CTE is actually outperforming the loops in all cases for me.
I have used CTEs a lot, and with a little work, you can get them to almost always out perform loops. Of course there are always the exception.

CREATE INDEX ix_Groups_ParentGroupId_IN_GroupId_GroupName ON dbo.Groups (ParentGroupId) INCLUDE (GroupId, GroupName)


peterhe
peterhe
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1302 Visits: 451

The purpose of the "inlcuded columns" in the index is to remove the key lookup. In the test4 of the article, it achieved this by removing those columns (GroupName, ParentGroupName,ParentGroupID) in the recursive part. Though CTE method performance improved a lot, you can see the results were still favour to the Loop method.

I tested the way to eliminate the key lookup by creating inlcuded columns in the table as recommended by Newbie (thanks). On my server (which is different from that the original tests were taken), the results showed CTE can benifit more than the Loop from the new index and the performance is close to Loop, but it's still shy of it:

Test 2: Two roots, three children under a node, 10 levels

-- 1) @GroupID=3, 9841 rows, 9 levels

By CTE: Starts at 2007-05-10 09:30:23.563, Finishes at 2007-05-10 09:30:24.597, Takes 1033ms

By Loop: Starts at 2007-05-10 09:30:53.237, Finishes at 2007-05-10 09:30:53.953, Takes 716ms

By CTE: Starts at 2007-05-10 09:31:37.253, Finishes at 2007-05-10 09:31:38.207, Takes 953ms

By Loop: Starts at 2007-05-10 09:31:53.393, Finishes at 2007-05-10 09:31:54.127, Takes 733ms





Troy Gatchell-386101
Troy Gatchell-386101
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 38

I found the exact opposite on my dev enviornment. The CTE procs out performed the loop procedures. I used the scripts and ran through all the test scenarios and found the CTE ran 50% faster than the loop. I'm running Windows 2003 Enterprise Edition, SQL 2005 Enterprise Edition SP2, 4 Dual Core (3.0 GHZ) Processors and 2 GB RAM.

I also use Recursive CTEs in a real production environment (3.2 TB Database) on 200 million and almost Billion row tables with a hierachly data structure and many nodes. I've never had an issue with CTE performance as long as indexes are tuned properly. I would not shy away from using CTE just because of this article.


GSquared
GSquared
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30233 Visits: 9730
That's odd. I've tested CTEs vs loops on hierarchy data and ended up with slightly better performance in the CTE than the loop. Directly contradicts what you ended up with.


set statistics io on
set statistics time on

declare @TopStructureID_in int

select @topstructureid_in =
(select structureid
from dbo.campaigns
where campaignid = 560)

declare @Hierarchy table (
Lvl int not null,
StructureID int not null,
ParentID int,
primary key (lvl, structureid),
State varchar(3))

declare @Rows int

insert into @hierarchy (lvl, structureid, parentid, state)
select 1, structureid, parentstructureid, rowstate
from dbo.corporatestructure
where structureid = @topstructureid_in
and rowstate != 'del'

select @rows = @@rowcount

while @rows > 0
begin

insert into @hierarchy (lvl, structureid, parentid, state)
select 1 + h2.lvl,
c2.structureid, c2.parentstructureid, c2.RowState
from dbo.corporatestructure c2
inner join @hierarchy h2
on c2.parentstructureid = h2.structureid
where c2.rowstate != 'del'
and c2.structureid not in
(select structureid
from @hierarchy)

select @rows = @@rowcount

end

select *
from @hierarchy



==============================
Table 'Campaigns'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table '#131B4459'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CorporateStructure'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table '#131B4459'. Scan count 2, logical reads 71, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 37, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CorporateStructure'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 1 ms.

(17 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table '#131B4459'. Scan count 2, logical reads 381, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 174, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CorporateStructure'. Scan count 18, logical reads 36, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 3 ms.

(86 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table '#131B4459'. Scan count 2, logical reads 729, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 262, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CorporateStructure'. Scan count 104, logical reads 208, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 8 ms.

(130 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table '#131B4459'. Scan count 2, logical reads 1099, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 314, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CorporateStructure'. Scan count 234, logical reads 472, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 21 ms.

(156 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table '#131B4459'. Scan count 2, logical reads 1054, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CorporateStructure'. Scan count 390, logical reads 784, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 22 ms.

(5 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table '#131B4459'. Scan count 2, logical reads 1044, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CorporateStructure'. Scan count 395, logical reads 794, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 22 ms.

(0 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

(395 row(s) affected)
Table '#131B4459'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
=====================================


set statistics io on
set statistics time on

declare @TopStructureID_in int

select @topstructureid_in =
(select structureid
from dbo.campaigns
where campaignid = 560)

declare @Hierarchy table (
Lvl int not null,
StructureID int not null,
ParentID int,
primary key (lvl, structureid),
State varchar(3))

;with Hierarchy (Lvl, StructureID, ParentID, RowState)
as
(select 0, StructureID, ParentStructureID, c1.RowState
from dbo.corporatestructure c1
where structureid = @topstructureid_in
union all
select 1 + h2.lvl,
c2.structureid, c2.parentstructureid, c2.RowState
from dbo.corporatestructure c2
inner join hierarchy h2
on c2.parentstructureid = h2.structureid
where c2.rowstate != 'del'
and h2.rowstate != 'del')
insert into @hierarchy (lvl, structureid, parentid, state)
select lvl, structureid, parentid, rowstate
from hierarchy

select *
from @hierarchy



====================================
Table 'Campaigns'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table '#441E6E67'. Scan count 0, logical reads 797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 2371, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CorporateStructure'. Scan count 790, logical reads 1584, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 24 ms.

(395 row(s) affected)

(395 row(s) affected)
Table '#441E6E67'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
[/code]

Both run multiple times, to make sure data is in the cache.

The loop method (which may differ from the loop you're using), took 62 milliseconds of CPU time, and doesn't improve from that in the next five runs. Took 1,142 total table scans of the CorporateStructure table (the one with the hierarchy in it).

The CTE took 31 milliseconds CPU as its longest run, and took as little as 15 milliseconds CPU on several of the runs. 790 total scans of the table.

From that, I have to say the CTE is better in all regards. Faster, and less IO bottleneck.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
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