3 ms·
CTE's (at least in MS SQL land) are a syntax level operation, meaning CTE's get expanded to be as if you wrote the same subquery at each place a CTE was, which
by RaftPeople 2y ago
CTE's (at least in MS SQL land) are a syntax level operation, meaning CTE's get expanded to be as if you wrote the same subquery at each place a CTE was, which frequently impacts the optimizer and performance.
I like the idea of CTE's, but I typically use temp tables instead of CTE's to avoid optimizer issues.
- wvenable 2y agoIf you use temp tables you're subverting the optimizer. Sometimes that's what you want but often it's not.
- RaftPeople 2y agoI use them on purpose to "help" the optimizer by reducing the search space for query plan ((knowing that query plan optimization is a combinatorial problem and the optimizer frequently can't evaluate enough plans in a reasonable amount of time).