5 ms·

WITH (CTEs) make queries so much more readable and digestible. As a programmer who now does data and SQL, I latched on to these as soon as I found I could reduc

WITH (CTEs) make queries so much more readable and digestible. As a programmer who now does data and SQL, I latched on to these as soon as I found I could reduce repetition in a query with them.

3pt14159 6y ago
For monster queries (thousands of lines of SQL) I find that temporary tables are also great. You can index them and, depending on your DB and its settings, they're usually held in RAM so they're super fast.
- sumtechguy 6y ago
  That is implementation specific. In some db's putting an index on a temp table does nothing. In later versions of the same db it does but only in particular cases. Make sure you read the docs around that.
achr2 6y ago
CTEs do not get cached though, so they are actually quite bad for repetition without also using a temp table.
- ComodoHacker 6y ago
  Depends on DBMS and version. Some are smart enough to pipeline/tie or materialize it internally.
meritt 6y ago
Make sure you understand what optimization fences are and how they affect your performance. CTEs are nice to read but routinely destroy the performance. [1] https://thoughtbot.com/blog/advanced-postgres-performance-tips#common-table-expressions-and-subqueries https://thoughtbot.com/blog/advanced-postgres-performance-ti...
- bjt 6y ago
  As of Postgres 12 this has changed substantially. Instead of being materialized, CTEs are inlined and optimized with the rest of the query. Exceptions: 1. If the results of the CTE are used more than once then it is materialized by default, though you can override this by adding "NOT MATERIALIZED" to the call. 2. Recursive and INSERT/UPDATE/DELETE CTEs are always materialized. https://paquier.xyz/postgresql-2/postgres-12-with-materialize/ https://paquier.xyz/postgresql-2/postgres-12-with-materializ...
- oarabbus_ 6y ago
  >CTEs are nice to read but routinely destroy the performance. This may be true on archaic versions of MySQL and Postgres but is not the case today, barring some esoteric edge cases (bugs) where the optimizer gets thrown out of whack. Once while doing data science consulting I rewrote a ~1000 line query in Aurora (MySQL flavored) which had a ~2.5s runtime, which was far too slow for the client's use-case. After rewriting all the CTEs (there were many) into subqueries, there was a 2-3% increase in query speed, barely (on the order of under a tenth of a second). There was a very tiny improvement far smaller than the normal variance of the runtime. Then I rebuilt the query and the joins, and was able to get the query to consistently run in the range of 0.8 - 1.2s. For my own purposes I then duplicated the query, re-implemented the CTEs, and did validate that indeed there is only a negligible increase in query time when using CTE.

WITH (CTEs) make queries so much more readable and digestible. As a programmer who now does data and SQL, I latched on to these as soon as I found I could reduce repetition in a query with them.

3pt14159 6y ago

For monster queries (thousands of lines of SQL) I find that temporary tables are also great. You can index them and, depending on your DB and its settings, they're usually held in RAM so they're super fast.

sumtechguy 6y ago
That is implementation specific. In some db's putting an index on a temp table does nothing. In later versions of the same db it does but only in particular cases. Make sure you read the docs around that.

achr2 6y ago

CTEs do not get cached though, so they are actually quite bad for repetition without also using a temp table.

ComodoHacker 6y ago
Depends on DBMS and version. Some are smart enough to pipeline/tie or materialize it internally.

meritt 6y ago

Make sure you understand what optimization fences are and how they affect your performance. CTEs are nice to read but routinely destroy the performance. [1] https://thoughtbot.com/blog/advanced-postgres-performance-tips#common-table-expressions-and-subqueries https://thoughtbot.com/blog/advanced-postgres-performance-ti...

bjt 6y ago
As of Postgres 12 this has changed substantially. Instead of being materialized, CTEs are inlined and optimized with the rest of the query. Exceptions: 1. If the results of the CTE are used more than once then it is materialized by default, though you can override this by adding "NOT MATERIALIZED" to the call. 2. Recursive and INSERT/UPDATE/DELETE CTEs are always materialized. https://paquier.xyz/postgresql-2/postgres-12-with-materialize/ https://paquier.xyz/postgresql-2/postgres-12-with-materializ...
oarabbus_ 6y ago
>CTEs are nice to read but routinely destroy the performance. This may be true on archaic versions of MySQL and Postgres but is not the case today, barring some esoteric edge cases (bugs) where the optimizer gets thrown out of whack. Once while doing data science consulting I rewrote a ~1000 line query in Aurora (MySQL flavored) which had a ~2.5s runtime, which was far too slow for the client's use-case. After rewriting all the CTEs (there were many) into subqueries, there was a 2-3% increase in query speed, barely (on the order of under a tenth of a second). There was a very tiny improvement far smaller than the normal variance of the runtime. Then I rebuilt the query and the joins, and was able to get the query to consistently run in the range of 0.8 - 1.2s. For my own purposes I then duplicated the query, re-implemented the CTEs, and did validate that indeed there is only a negligible increase in query time when using CTE.