CTE Performance

Question

Post reply

CTE Performance

peterhe

SSChampion

Points: 11469
More actions
March 14, 2007 at 10:21 am

#171633

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

Viewing 15 posts - 1 through 15 (of 32 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135254 More actions · Answer 1

the scripts are unavailable.

Nice article.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Charles Kincaid SSChampion Points: 13593 More actions · Answer 2

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.

ATBCharles Kincaid

Laxmi Narayan SSC Veteran Points: 212 More actions · Answer 3

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.

Vic Kirkpatrick-173212 SSCommitted Points: 1831 More actions · Answer 4

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.

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 5

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

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

peterhe SSChampion Points: 11469 More actions · Answer 6

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.

noeld SSC Guru Points: 96590 More actions · Answer 7

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

* Noel

peterhe SSChampion Points: 11469 More actions · Answer 8

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.

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 9

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.

Charles Kincaid SSChampion Points: 13593 More actions · Answer 10

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 Mr or Mrs. 500 Points: 551 More actions · Answer 11

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 Hall of Fame Points: 3486 More actions · Answer 12

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

Tito Jermaine SSC Veteran Points: 242 More actions · Answer 13

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 SSC Eights! Points: 843 More actions · Answer 14

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)