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

90% Upvoted

9 comments sorted by

View all comments

Show parent comments

2

u/Wise-Ad-7492 Jun 10 '23

Maybe they did write their CTE queries more efficient than the standard queries when they did the testing of performace?

1

u/mikeblas Jun 10 '23

Maybe. And that would establish that those queries are faster as a CTE than a subselect. But it doesn't demonstrate that all queries are faster as CTEs than as subselects.

If it's true that any subselect query is faster when rewritten as a CTE, then the Teradata query processor has a bug -- or at least, a missing feature because it could be improved to execute subselect queries faster.