4 ms·
Even in databases without the same performance issues as PSQL, like SQL Server, "with clauses" can cause performance issues on their on own. I recently refacto
by wvenable 9y ago
Even in databases without the same performance issues as PSQL, like SQL Server, "with clauses" can cause performance issues on their on own.
I recently refactored a long SQL query with a half-dozen with-expressions to a single query and increased the performance by an order of magnitude. They had used "with" to build up a query from a bunch of independent sets and then union them together and, while perfectly logical, it was a performance nightmare. In the end, I just took all the conditions that made up each query and combined it into one with the appropriate joins. I'd even argue that ultimately the finished product was easier to understand.
- mtone 9y agoThe trade-off is modularity. During development working with CTEs paves the way for establishing views later, possibly highlight recurring requirements or gaps in the schema, and the logical separation of general purpose sets (ie functions) from the specifics of a particular query is valuable. Like in everything, a balance is ideal. For reporting/analysis/testing purposes, I think nesting views and CTEs is more productive in the long run, while production application code is probably best kept as straightforward as possible.