I loathe using sub queries.
I use ctes for everything and do my joins at the end.
This seems far more readable.
CTEs as lookup tables
81–90 of 115 posts
Re: CTEs as lookup tables
#82Earlier quoted context omitted.
I had some code from a vendor that was hanging in SQL Server and I looked at the code and they had composed everything together using smaller CTE queries and it took forever to run. They had taken various complex criteria and executed one query for each and then combined the results -- I ended up spending a day refactoring the whole thing to single SELECT with all the criteria and it ran instantly. This was for a nig…
I'm also curious how turning some of those into views would compare, but this is also probably dependent on which database.
I'd say it would be almost as bad.
In SQL you're describing the result that you want -- you aren't (and shouldn't be) saying how to achieve that result. The engine goes off and (hopefully) finds the optimal way to create the result you are describing. When you break queries down too far, the engine has more trouble finding the optimal path.
SQL Server can "see through" views and CTEs optimize them combined with a query but depending on the calculations it might have to generate and process intermediate data that it doesn't need. In my case above, the engine was reprocessing the same data set over and over, generating duplicate rows, and combining everything removing the duplicates.
My query was significantly simpler but also, in a way, harder to follow. It wasn't as clear what the intent was. Where they had nice named CTE subqueries, I had to use comments.
Re: CTEs as lookup tables
#83This also works in MySQL 8.0.19 and above but the syntax of the CTE example needs to be tweaked to use the ROW() constructor:
WITH countries (code, name) AS (
SELECT * FROM (VALUES
ROW('us', 'United States'),
ROW('fr', 'France'),
ROW('in', 'India')
) AS codes
)
SELECT data.code, name FROM data LEFT JOIN countries.code = data.code;Re: CTEs as lookup tables
#84Sucks for localization though.
Re: CTEs as lookup tables
#85I do this with lateral joins instead.
Re: CTEs as lookup tables
#86This is a great idea if you disable materialization (which the author of this post does not mention). Yes, materialization is fine for small hardcoded values in the post, but for most other lookup tables, it's definitely not fine. For example, you read this article and think great, I'll make a lookup table to map various ids to be linked across tables. with ids as ( select u.user_id, u.token, s.customer_id from users…
side note: I asked chatgpt to rewrite this query to use subqueries and it did a reasonable job.
next step: include cte-to-subquery translation step as a part of your build pipeline. never needed tools like that but i guess they must exist since it's been such a common issue. using gpt for this is like nuking a mosquito from orbit.
Re: CTEs as lookup tables
#87CTEs are low-key one of the best features of SQL. Great for debugging big queries, such as: with source as ( select * from wherever ), transformed as ( ... ), joined as ( ... ), final as ( ... ) select * from final You can switch `final` to `transformed` to see what the query is doing internally. Almost like having good control flow. Almost.
I prefer actually materializing the tables so then I can check the output for what the transform tables and the joined tables look like. generally can't just swap transformed in final because final depends on the output of transformed?
The trick is to change the name of the CTE in your final select. This will allow you to inspect that particular step.
This works as long as your CTE’s are correct SQL and only the logic is wrong or suspect.
Re: CTEs as lookup tables
#88Earlier quoted context omitted.
CTEs used to be "optimization fences" in PostgreSQL, but that changed with v12. https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...
They still are sometimes. Whenever you have a CTE which is referenced more than once, to be specific.
Similarly, joining a CTE to itself can also be problematic. In that case materializing to a temp table can be faster.
Re: CTEs as lookup tables
#89Earlier quoted context omitted.
Note that there are basically no special performance considerations when using CTEs in recent versions of postgres… unless your CTE is recursive or does some weird side-effect (which is unlikely).
This is incorrect. If you use a CTE more than once in a query, Postgres will materialize that data into a temp table that has no indexes. If that CTE gets joined a lot, it will incur many O(N) searched through unindexed data. I've seen cases where a few GBs of product data gets pulled into a CTE this way. Queries take 10s to complete from processing GBs of unindexed data
[deleted]
Re: CTEs as lookup tables
#90CTEs are great for breaking down a large query into discrete steps. They're basically SQL's equivalent of a pipe function.