Using CTE - Does this help the optimiser

Question

Using CTE - Does this help the optimiser

Tim Walker.

SSCertifiable

Points: 5173
More actions
August 7, 2013 at 1:30 pm

#276735

If I use CTE, I can write a more readable SQL statement. But does this affect what the optimiser does?
One of the reasons I use temp tables or table variables is to break down the queries into simpler queries that the optimser can work out more easily.
Do I get the same effect with CTE? E.g if I have a query that joins 6 tables, but break it down into two CTEs that join 3 and then a final join of the two CTE results, will I get a more consistent plan?
I'm assuming that I can structure the CTE in asensible way that makes good use of the indexes.
Thanks
Tim
.

Viewing 7 posts - 1 through 6 (of 6 total)

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

ScottPletcher SSC Guru Points: 100951 More actions · Answer 1

CTEs do not do that, from what I've seen looking at query plans. Instead, SQL re-runs the entire query every time you refer to the CTE.

For the most performance gain, you would still need to use a temp table, which can be separately indexed; obviously the CTE will not have its own index in any case.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 2

Thanks for the reply Scott.

I think you are probably right, although I was hoping we we wrong! I'd like to be able to use this construct to return two small results sets from a shedload of disparate data and join them on something common, rather than have to stage through table variables or temporary tables.

I hope someone may offer an alternative opinion!

Thanks again,

Tim

.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 3

When you use a CTE, SQL Server treats it like a macro, and replaces all references to the CTE with the query text, and the optimizer works with the expanded query.

Thus, using a CTE does not help the optimizer.

Using an intermediate temp table may do, but of course, there is a price for the intermediate storage.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 4

To add to what has already been stated, CTEs are sometimes referred to as "inline views" and they act pretty much like a view would. It is not a replacement for storing an interim result in a TempTable to do the "Divide'n'Conquer" thing, which is frequently more effective in breaking up larger queries than using CTEs.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 5

Erland, Jeff, your replies are as always much appreciated.

I now have confidence that I know how this all hangs together.

Shame in a way though. Table variables always compile as one line things and the overhead of stats on temporary tables can be significant.

I sort of hoped CTEs would give me an 'incremental compile plan that made it easier for the optimiser to deal with simpler building blocks in order to create the full result.

Instead of sometimes getting more complex queries very wrong!

Back to breaking them up and optimiser hints!

Thanls again.

Tim

.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 6

Tim Walker. (8/7/2013)
Erland, Jeff, your replies are as always much appreciated.
I now have confidence that I know how this all hangs together.
Shame in a way though. Table variables always compile as one line things and the overhead of stats on temporary tables can be significant.
I sort of hoped CTEs would give me an 'incremental compile plan that made it easier for the optimiser to deal with simpler building blocks in order to create the full result.
Instead of sometimes getting more complex queries very wrong!
Back to breaking them up and optimiser hints!
Thanls again.
Tim

Sometimes, the reorganization into CTEs adds clarity to the problem where you can identify a tweak or an index that you may have missed before. No one thing is a panacea. Try them all and those things you've yet to discover about this problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)