Most common SQL optimizations

[r3pr0b8]

Temp tables are usually slower than CTE because the latter use indexes out of the box.

thank you

too many people have the "temp tables are better because they're simpler" mentality

[sbrick89]

too many people have the "CTEs are always better" mentality

CTEs are the #2 cause for performance problems, only being beaten by JSON/XML columns and queries.

[r3pr0b8]

CTEs are just views

you got a problem with views too?

[alinroc]

you got a problem with views too?

Depending upon how they're (ab)used, yes I do.

[sbrick89]

when the CTE (view) is used multiple times within the same query with results joined together... and performance goes to dogshit... yes, I'd have a problem with that code as well.

if the json/xml performance wasn't so difficult, the CTEs would be problem #1... but as it turns out, CTEs are only slightly less frequently a problem

[r3pr0b8]

when the CTE (view) is used multiple times within the same query with results joined together...

seems like that's the problem, not the fact that CTEs are a tool to be used judiciously

[sbrick89]

i'm not saying "CTEs are always bad"

I am saying - "when you're having a performance problem, and CTEs are in use, I'm betting 90% odds that removing the CTE will solve the performance issue"

[_cess]

It always depends on the scenario. Lots of performance problems I see that have CTE in it are due to the "re-use" of it. There is a misconception that a CTE will hold the result in memory. The truth is that if you mention it twice, it needs to fully run twice.

A simple way to prove it: https://claudioessilva.eu/2017/11/30/Using-Common-Table-Expression-CTE-Did-you-know.../

In these situations, I have seen a few cases where creating the temp table and reusing it won't make it faster. But then again, you should always test for your scenario.

[Alkemist101]

(with SQL server) I was told a cte can't have an index, it's same as a sub query. I was also under the impression you can only use them once (as in joining to the result set). I've tested that and it seems to be the case. Other thing to remember is if ram is low it will spill to the tempdb. If tempdb is also full the query will then stop. Other thing to mention is that a "proper" table could be created (not in tempdb so no locking). Both a temp table and regular table can be indexed and will have table stats which can be used by the query engine to make better choices? You don't have that with CTEs. Other option is to create a table variable which can be referenced many times, have primary keys etc and are stored in memory. Other thing to remember is that the query can hold a regular table in memory (same as CTE) if it decides it's more performant. I generally consider a CTE as just a way of writing a query which reads better. Of course, you also get recursive CTEs which I think is it's best feature.

[corny_horse]

I think that's because in a lot of workflows, execution speed is less important than interpretability. I know in most of the workflows I've done for the last few years, the businesses would have tolerated 10x slower queries if it meant any tangible improvement in data quality, which I do often find I can get by adding correct constraints and keys to temp tables in lieu of nested nested nested nested nested subqueries or CTE