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»»»

CTE Performance Expand / Collapse
Author
Message
Posted Tuesday, May 8, 2007 7:47 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 3:11 PM
Points: 31,368, Visits: 15,837
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
Post #363993
Posted Tuesday, May 8, 2007 7:50 AM


SSC Eights!

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

Group: General Forum Members
Last Login: Today @ 12:44 AM
Points: 821, Visits: 2,029

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.

 



ATB

Charles Kincaid

Post #363997
Posted Tuesday, May 8, 2007 9:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 9:03 AM
Points: 28, Visits: 125
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.



Post #364076
Posted Tuesday, May 8, 2007 10:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 7:32 PM
Points: 214, Visits: 376
nice article, but besides stating CTE as a neat way to implement recursive queries. this article is missing concept & meaning about CTE.
Post #364100
Posted Tuesday, May 8, 2007 3:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 10, 2012 6:53 AM
Points: 8, 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.
Post #364221
Posted Tuesday, May 8, 2007 5:03 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, December 19, 2014 12:12 PM
Points: 1,945, Visits: 3,180
Here is a way to avoid recursion and loops:

CREATE TABLE InputStrings
(keycol CHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL);

INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
etc.

This will be the table that gets the outputs, in the form of the original key column and one parameter per row.

It makes life easier if the lists in the input strings start and end with a comma. You will need a table of sequential numbers -- a standard SQL programming trick, Now, the query, in SQL-92 syntax (translate into your local dialect):

CREATE VIEW ParmList (keycol, place, parm)
AS
SELECT keycol,
COUNT(S2.seq), -- reverse order
CAST (SUBSTRING (I1.input_string
FROM S1.seq
FOR MIN(S2.seq) - S1.seq -1)
AS INTEGER)
FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
WHERE SUBSTRING (',' || I1.input_string || ',' FROM S1.seq FOR 1) = ','
AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) = ','
AND S1.seq < S2.seq
GROUP BY I1.keycol, I1.input_string, S1.seq;

The S1 and S2 copies of Sequence are used to locate bracketing pairs of commas, and the entire set of substrings located between them is extracted and cast as integers in one non-procedural step. The trick is to be sure that the right hand comma of the bracketing pair is the closest one to the first comma. The relative postion of each element in the list is given by the value of "place", but it does a count down so you can plan horizonal placement in columns.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #364236
Posted Wednesday, May 9, 2007 10:52 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, May 10, 2010 1:18 PM
Points: 667, 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)

Post #364474
Posted Thursday, May 10, 2007 7:35 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 7:18 AM
Points: 531, Visits: 433

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

 




Post #364702
Posted Tuesday, May 15, 2007 2:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 20, 2012 4:42 PM
Points: 5, 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. 

 

Post #366121
Posted Friday, March 14, 2008 6:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #469342
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse