5 ms·
> CTEs >First, yes they can be an optimisation boundary. But they can also make your query much more read-able and prevent you from doing the wrong thing becau
by bigato 10y ago
> CTEs
>First, yes they can be an optimisation boundary. But they can also make your query much more read-able and prevent you from doing the wrong thing because you couldn’t reason about a query.
In my experience, CTEs may be simpler for the writer to reason about, but they make the queries complicated for the reader to understand. If you use a CTE because you find the problem too complicated, chances are that you are not yet understanding the problem well enough to describe it in a single plain SQL query.
- blowski 10y agoSometimes I understand the problem perfectly well, but a single beautiful query is not performant enough. Anything that can help to write and maintain big ugly queries without a cost in performance is welcome.
- bigato 10y agoCare to elaborate on what situation would a CTE improve performance? Because the OP itself mentioned CTEs degrading performance.
- blowski 10y agoIt wouldn't improve performance, but it might improve readability without denigrating performance. I don't have much experience with CTEs, but I do have lots of experience trying to debug unreadable SQL.
- bigato 10y ago> Sometimes I understand the problem perfectly well, but a single beautiful query is not performant enough. > It wouldn't improve performance, but it might improve readability without denigrating performance. These two statements of yours are negating each other, unless people started using "performant" as synonymous for readable now.
- blowski 10y agoOK, a clarification. My first attempt at a query is readable but performance is terrible. I fix the performance problem, but the query is now much less readable. I use a CTE as a compromise between performance and readability.
- wvenable 10y agoCTE's are necessary for some types of queries but I've seen developers use them where a simple join would have worked. It can add a lot of unnecessary noise.
- bigato 10y agoExactly! In some cases like in recursive queries, CTEs may be the only way to achieve some result. In almost all other cases, people use them just because they apparently can't reason about joins, or don't like to do so. I frequently have to deal with SQL that looks like the person was writing an imperative algorithm.