SQLServerCentral / Discuss Content Posted by Peter He / Article Discussions / Article Discussions by Author / CTE Performance / Latest Posts

RE: CTE Performance

Jeff Moden — Wed, 31 Dec 2008 09:23:07 GMT

Yep... agreed. And the difference between a pyramid scheme and a legal MLM is a legal MLM actually sells product or tangible services.

RE: CTE Performance

SQLRNNR — Wed, 31 Dec 2008 08:02:59 GMT

Pyramid schemes are illegal - and matrix, unilevel and binary trees could all be a pyramid scheme. The Fed monitors MLMs closely and makes sure they are in compliance with the law. MLMs and Pyramid schemes are not that much different - just one is legal and the other isn't.

RE: CTE Performance

Jeff Moden — Tue, 30 Dec 2008 17:28:05 GMT

[quote][b]jason brimhall (12/30/2008)[/b][hr]Binary Trees in the MLM industry.[/quote]Heh... I thought binaries were illegal in the U.S. I thought uni-level was the only legal method for an MLM.

RE: CTE Performance

SQLRNNR — Tue, 30 Dec 2008 16:15:36 GMT

some are worse than others as far as strung out. Most of it depends on the business model employed. I have seen one tree strung out to about 1200 levels but with only 500k nodes.

RE: CTE Performance

RBarryYoung — Tue, 30 Dec 2008 15:34:42 GMT

Ah. OK....So at 700 levels and say 10 million nodes, that would mean that each node had an average of 1.023 children? Wow, that's a pretty strung-out hierarchy.

RE: CTE Performance

Matt Miller (#4) — Tue, 30 Dec 2008 15:25:24 GMT

MLM=multi-level marketing.

RE: CTE Performance

RBarryYoung — Tue, 30 Dec 2008 15:17:03 GMT

What is the MLM industry and why would they need hierarchies that deep?

RE: CTE Performance

SQLRNNR — Tue, 30 Dec 2008 14:50:29 GMT

Binary Trees in the MLM industry.

RE: CTE Performance

RBarryYoung — Tue, 30 Dec 2008 13:46:13 GMT

[quote][b]jason brimhall (12/30/2008)[/b][hr]I have to agree with Jason Adams on this topic. This article seems to miss one of the most important aspects of DBA work - proper indexing. I currently have the need in an environment to create trees recursively 700+ levels deep and with several million nodes. To perform this using a loop takes 10-15 times longer than to use CTEs. That is using the same indexing between the two sets of coding philosophies. I have seen the rare occasion where a CTE does not outperform the loop by that much, but have yet to see a scenario where the CTE is outperformed by a loop.BTW, the loops had been optimized numerous times to gain every ms possible out of them - introduce the CTE's and we are leaps and bounds beyond historic SQL performance levels.[/quote]OK, NOW I'm curious: What kind of hierarchy goes that deep?

RE: CTE Performance

SQLRNNR — Tue, 30 Dec 2008 12:43:56 GMT

I have to agree with Jason Adams on this topic. This article seems to miss one of the most important aspects of DBA work - proper indexing. I currently have the need in an environment to create trees recursively 700+ levels deep and with several million nodes. To perform this using a loop takes 10-15 times longer than to use CTEs. That is using the same indexing between the two sets of coding philosophies. I have seen the rare occasion where a CTE does not outperform the loop by that much, but have yet to see a scenario where the CTE is outperformed by a loop.BTW, the loops had been optimized numerous times to gain every ms possible out of them - introduce the CTE's and we are leaps and bounds beyond historic SQL performance levels.

RE: CTE Performance

Jeff Moden — Thu, 12 Jun 2008 19:29:23 GMT

This is probably the wrong place to ask for such help... and we'd really need to know which version of SQL Server you're using. It would probably be better if you started a thread in the correct forum for this.

RE: CTE Performance

Tech_Newbie — Thu, 12 Jun 2008 18:53:51 GMT

help please.I have the following situation:I want to populate two related tables Person (PersonID, PersonName, PersonLName)PersonAddress(PersonAddressID, PersonID, PersonAddr, PersonCity, PersonState, PersonZip, County)from a table which contains all the data neededMasterList(PersonName, PersonLName, PersonAddr, PersonCity, PersonState, Personzip, County)The thing is I am not doing this sequentially, I want to select every 7th person starting from the person in the MasterList table at position 3.For example:I have 100 people in the master listLets say that I want 30 people to be put into the Persons table and into thier associated address tables. I want to be able to increment through the MasterList table starting at the person from position 3, and insert every 7th person thereafter into the tables.If I get to the end of the table and I have not reached my 30 people, I need to loop back around starting at a new position - 2 and then repeat selecting every 7th person from then on out.Important things: 1. PersonID is an identity, and an auto - incremented value, but within this loop, when I create my entry for Person, I want to turn around and create an entry for PersonAddress, using the newly created PersonID identity value as a foreign key inside of PersonAddress. How should I write a cte to be able to acomplish this ? Thanks for any assistance provided

RE: CTE Performance

GSquared — Mon, 17 Mar 2008 09:27:32 GMT

[quote][b]peterhe (3/14/2008)[/b][hr]I found your loop and CTE are not equivalent. In the loop, you have the code WHERE ...and c2.structureid not in (select structureid from @hierarchy)which slows it down.[/quote]That step is necessary in the loop, and not in the CTE. Otherwise, a repeating hierarchy level causes an error in the loop.For example, insert into a hierarchy table:ID 1, Parent 2ID 2, Parent 1orID 1, Parent 1In either case, the loop without the Not In (or a left join), will cause an error as it hits the max recursion level. The CTE doesn't have that problem and doesn't need the code.For an example of how this can happen:Joe is a manager of a sales departmentJoe is also a salesman in that departmentIn this case, it is necessary for Joe to be listed as the manager, since that determines his "sales manager" commission, and it is necessary for him to be listed as a salesperson, since that determines his direct sales commissions, and his own sales count for calculating his manager commission. Separating him into two entities with two separate IDs would necessitate giving him two separate paychecks, and would thus mess up income tax, Social Security, Medicare, 401(k), etc., calculations.A loop without controls for that kind of thing will go into an infinite loop and will error out. (Yes, I know, real life isn't as neat and tidy like we'd like it to be.)I've tested the above scenario in CTEs, and they don't error out and do return the correct hierarchy.If you can guarantee that the hierarchy will never include recursive elements, then you don't need that step in the loop. The loop without that step, on data pre-tested for non-recursion, is still slower than the CTE on the same data, in my tests.

RE: CTE Performance

peterhe — Fri, 14 Mar 2008 14:37:52 GMT

I found your loop and CTE are not equivalent. In the loop, you have the code WHERE ...and c2.structureid not in (select structureid from @hierarchy)which slows it down.

RE: CTE Performance

GSquared — Fri, 14 Mar 2008 07:00:40 GMT

Joe, I haven't been able to work out an efficient way to use your nested sets structure on dynamic hierarchies. On relatively static data, it works great, but I have trouble with it as soon as I need to move levels around, or expand the hierarchy, or anything else other than adding to the "right side" of the hierarchy.For example, in a managers to employees hierarchy, if a department is moved to be under a different manager, the nested sets model pretty much forces a complete rebuild of the whole hierarchy, so far as I can tell. A standard hierarchy table (ID and ParentID), allows the same update by changing a single value in a single row.I could be missing something on this (probably am), but I just can't get my head around using the nested sets for data that requires any sort of frequent updates.I've tested it, and it does work really, really well for selects. But it adds so much complexity and so many referential integrity issues to updates and inserts, that I just can't see how to use it.Can you point me to something that helps in that regard?Or is it strictly meant for static/nearly static data?

RE: CTE Performance

GSquared — Fri, 14 Mar 2008 06:49:01 GMT

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.[code]set statistics io onset statistics time ondeclare @TopStructureID_in intselect @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 intinsert into @hierarchy (lvl, structureid, parentid, state)select 1, structureid, parentstructureid, rowstatefrom dbo.corporatestructurewhere structureid = @topstructureid_inand rowstate != 'del'select @rows = @@rowcountwhile @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 = @@rowcountendselect *from @hierarchy[/code]==============================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.=====================================[code]set statistics io onset statistics time ondeclare @TopStructureID_in intselect @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 hierarchyselect *from @hierarchy[/code]====================================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.

RE: CTE Performance

Troy Gatchell-386101 — Tue, 15 May 2007 14:29:00 GMT

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.

RE: CTE Performance

peterhe — Thu, 10 May 2007 07:35:00 GMT

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

RE: CTE Performance

Jason Adams — Wed, 09 May 2007 10:52:00 GMT

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)

RE: CTE Performance

CELKO — Tue, 08 May 2007 17:03:00 GMT

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)ASSELECT 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.

RE: CTE Performance

Tito Jermaine — Tue, 08 May 2007 15:18:00 GMT

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.

RE: CTE Performance

sqldba-294117 — Tue, 08 May 2007 10:11:00 GMT

nice article, but besides stating CTE as a neat way to implement recursive queries. this article is missing concept & meaning about CTE.

RE: CTE Performance

VadimK — Tue, 08 May 2007 09:34:00 GMT

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=289849but I guess it was too early for anybody to respond.

RE: CTE Performance

Charles Kincaid — Tue, 08 May 2007 07:50:00 GMT

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.

RE: CTE Performance

Steve Jones - SSC Editor — Tue, 08 May 2007 07:47:00 GMT

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.

RE: CTE Performance

CELKO — Tue, 08 May 2007 07:34:00 GMT

You might also want to try a Nested Sets model against a CTE and a loop. For that depth, it should beat both of them as well as use only one simple query.

RE: CTE Performance

peterhe — Tue, 08 May 2007 07:11:00 GMT

I just sent an email to Steve about the broken of the link to the scripts.

If it's not possible to fix the link, I can send the scripts to your guys later.

RE: CTE Performance

noeld — Tue, 08 May 2007 07:03:00 GMT

I agree with Grant. I have to see the queries.

RE: CTE Performance

peterhe — Tue, 08 May 2007 06:46:00 GMT

Thanks everyone.

The scripts were all submitted to SQLCentral.com with the article. I am not sure why the link was broken.

To Charles Kincaid:

What I meant in "The plans do not give any clue to their performance difference." is that the CTE execution plan doesn't give the details in the recursive execution. The row number (the thickness of the arrow) is actually the final output. SQL server generates only one execution plan for a CTE.

The execution plan of the loop method showed in the article is for the one execution of the looping statement. You know, SQL server generates one execution plan for every execution of the same statement in the loop. So the arrow (row number) is thinner in each plan.

RE: CTE Performance

Grant Fritchey — Tue, 08 May 2007 06:17:00 GMT

I'd sure like to see the queries before I can completely agree with the conclusions.

RE: CTE Performance

Vic Kirkpatrick-173212 — Tue, 08 May 2007 05:44:00 GMT

Wow, this is really disappointing. I hadn't yet tested much with CTE's on the performance, as the db I am working with now is relatively small, but I just kind of figured that since CTE's were working in a set-based fashion it would perform better than the looping. Hmmmm.

RE: CTE Performance

Laxmi Narayan — Tue, 08 May 2007 04:00:00 GMT

Hey It was a nice article...But it would have been more exciting if the scripts were available...Anyway I would look forward to see the script as well to have a better understanding in it...Thanks and Really gr8 effort to put such things on board.

RE: CTE Performance

Charles Kincaid — Tue, 08 May 2007 00:34:00 GMT

You said, "The plans do not give any clue to their performance difference." But there ARE indications. The thickness of the arrows are a real indicator. The thicker the arrow, the more rows in that part of the plan. I can see that there are thicker arrows with the CTE. More rows will, generally, take more time. Hover with the mouse over the arrows to see the number of rows.

RE: CTE Performance

ALZDBA — Mon, 07 May 2007 23:50:00 GMT

the scripts are unavailable.

Nice article.

CTE Performance

peterhe — Wed, 14 Mar 2007 10:21:00 GMT

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/phe/2926.asp