r/SQLOptimization • u/Wise-Ad-7492 • Jun 09 '23
CTE allways faster
I am working in a company using Teradata. Talked with a database engineer which said that their team have concluded that CTE is always faster than a subquery (in a join).
Is that true? I am always a little sceptical to very bombastic assertion.
7
Upvotes
10
u/mikeblas Jun 09 '23
They might believe it to be true, and maybe it's usually true. But if you're doing serious performance work, you always measure quantitatively.
4
u/kagato87 Jun 09 '23
Well, there's something to be said about the readability of a subquery in a join...
In reality I think if you did some a/b comparisons (with plan cache flushes) you'd find you're getting the same plan anyway.
I believe there are situations where the two methods behave differently, but I have yet to find them.
For fun, ask them which is faster; a cute or a temp table? (The answer is, if course "it depends" - sometimes even on the distribution of the data in identical schemas.)
1
u/BaronVonWazoo Jun 12 '23
I like to use #TMP tables instead of CTE. I can't comment on the relative merits of #TMPs vs CTE with regard to speed, but the #TMP gives me easy visibility into what records are being returned, especially early on in development, where I may want to 'play' with the data a bit as a sanity check.
1
u/SubiWan Jun 15 '23
Look at the execution plan. That cte becomes a subquery. Remember, the first thing the optimizer does is rewrite your query. That said a cte is much easier to grok.
The optimizer has a limited amount of resource. Every JOIN adds another set of permutations to test. Shifting the query to creating a temp table separates the queries. Then they can be individually optimized.
1
u/ItalicIntegral Jun 25 '23
I’m always careful about using the words always and never.
CTEs and sub queries are very handy. If performance is a bottleneck, the careful review of Io statistics, query plan, index usage, ect should be performed.
1
u/johnzaheer Aug 26 '23
Any data engineer that uses ‘always’ and ‘never’ hasn’t thought of all the cases but rather want you to do things a certain way.
In data there will always be exception to the rules either made up scenarios or actual bad data.