Wow it just keeps going. Extremely thorough.
Good CTE, Bad CTE
31–40 of 47 posts
Re: Good CTE, Bad CTE
#32Sometimes easy performance trick is to split the CTE to separate queries, put the results to unlogged temporary tables and add whatever indexes the next step needs.
Obviously makes only sense for stuff like analytical queries that are not running constantly.
Re: Good CTE, Bad CTE
#33Sometimes easy performance trick is to split the CTE to separate queries, put the results to unlogged temporary tables and add whatever indexes the next step needs. Obviously makes only sense for stuff like analytical queries that are not running constantly.
Worth underlying the OLAP versus OLTP divide you are speaking to on the close, there.
Re: Good CTE, Bad CTE
#34Sometimes easy performance trick is to split the CTE to separate queries, put the results to unlogged temporary tables and add whatever indexes the next step needs. Obviously makes only sense for stuff like analytical queries that are not running constantly.
An issue that has arise for me in some situations is that for more expensive/reporting queries we point to a db replica, where temporary tables are not an option.
Re: Good CTE, Bad CTE
#35I just wish that SQL Server had a materialize keyword like Postgres.
I'll write some nice clean CTEs and then have to refactor it to temp tables due to the lack of materialization.
Re: Good CTE, Bad CTE
#36I consulted at some Indian startups. Such bad queries were written which were costing them so much compute
I just rewrote all queries with claude code and next day and compute decreased to 1/5th.
Re: Good CTE, Bad CTE
#37Use the term, never define the term, classic. CTE stands for Common Table Expressions in SQL. They are temporary result sets defined within a single query using the WITH clause, acting like named subqueries to improve readability and structure.
Eh, almost every link on the homepage has an initialism or acronym in the title, and roughly none of them are actually defining the term they're using. Indeed, not to point fingers, your own submissions make the same mistake.
Sure, yes, OP should (and now has) defined the term. But at the same time it's reasonable to expect that someone reading a blog post on BoringSQL.com would already know the term just as much as we could expect people interested in Clojure would know what a REPL is.
Re: Good CTE, Bad CTE
#38Love CYEs, use them all the time, great article.
Re: Good CTE, Bad CTE
#39Re: Good CTE, Bad CTE
#40Love CYEs, use them all the time, great article.
Ah typo, sorry CTEs!