Isn't it more readable to make the code snippet with the case when statements into a CTE? I feel like I am misunderstanding the proposed benefits here.
CTEs as lookup tables
61–70 of 115 posts
Re: CTEs as lookup tables
#62Removing CTEs from your codebase by replacing them with the creation of a temp table and then using separate queries with as few joins as possible to populate it will give you low-locking performance hundreds of times better nearly every time. No problem with readability.
I see this pattern all the time in mssql code, but hardly ever elsewhere. I've always wondered if there is a reason for that. Are you coming from that background? I'm curious if anyone knows why it is so favored there? I'm not sure I agree re performance btw. I've fixed a lot of slow mssql queries by changing them in the other direction (EDIT: or just adding a join). I do see how temp tables might shorten the time yo…
It depends on the use of the database. Where there is intense transactional writes (e.g. case management system, etc.) the CTE approach can easily get locked up, especially if the write queries do a lot of their own lookups. I have come from an MSSQL background, so maybe that is an artifact either of the DB or the way it is used in my own history :) Good question though, and I'd like to hear from someone who has experience in other DBs.
WRT subverting the transaction isolation level, using temp tables is a tacit decision to do so and best for non-vital read queries in my own experience. That is, I used it for lookups of tables that did not _need_ to be completely up to date, or when I knew the data _would_ be up to date.
Re: CTEs as lookup tables
#63sqlite doesn't have linear regression functions, and doing the math manually is a bit awkward because "b" relies on "m". Instead of duplicating the math to calculate "b", here's how to do it with CTEs: CREATE TABLE vals (x, y); INSERT INTO vals VALUES (1, 1), (2, 0.5), (3, 0.4), (4, 0.1), (5, 0); WITH m(v) AS ( SELECT ((COUNT(*) * (SUM(x * y))) - (SUM(x) * SUM(y))) / ((COUNT(*) * SUM(POW(x, 2))) - (POW(SUM(x), 2))) F…
I'd love to see this in blog post form! This seems like a case where it would be nice to be able to pass in a parameter `vals (x, y)`.
I've tried to get myself writing a few times but never succeeded, so I don't have a blog. But - I am actually doing parameters like so in the view where I copied the above from:
CREATE TABLE view_confs (key, value);
INSERT INTO view_confs VALUE ('start', 2), ('end', 4);
vals_range(x, y) AS (
SELECT x, y
FROM vals
WHERE x >= (SELECT value FROM view_confs WHERE key = 'start')
AND x Re: CTEs as lookup tables
#64This 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…
`NOT MATERIALIZED` is now the default in Postgres, and has been since PG12 I believe
Only when the CTE is referenced once. If it's referenced more than once, it is automatically materialized.
Re: CTEs as lookup tables
#65Earlier quoted context omitted.
I see this pattern all the time in mssql code, but hardly ever elsewhere. I've always wondered if there is a reason for that. Are you coming from that background? I'm curious if anyone knows why it is so favored there? I'm not sure I agree re performance btw. I've fixed a lot of slow mssql queries by changing them in the other direction (EDIT: or just adding a join). I do see how temp tables might shorten the time yo…
I have also seen the performance boost by using separate temp tables rather than CTEs (in my case in Redshift). My hypothesis is that while memory use my be the same either way, you don't have the same transactional/locking requirements/overhead/consistency with multi statements as you do with a single big set of CTEs. And for analytics you rarely need that transactional isolation required by a single big CTE.
That's because Redshift doesn't support MATERIALIZED CTEs. In mainline Postgres, a MATERIALIZED CTE acts more like a temporary table with the performance characteristics you're looking for.
Re: CTEs as lookup tables
#66CTEs (common table expressions) are wonderful, they make SQL grokkable and maintainable. But, before some dev goes crazy refactoring all of the SQL in their codebase into maintainable CTEs: always benchmark. CTEs can cause your query planner to optimize incorrectly. In some cases, CTEs can force the query optimizer to choose a plan it otherwise is not choosing and be more performant - perhaps up to a certain point. (…
100% true.
I wrote some very elegant, readable SQL to perform a complex query. It was dog slow.
I handed it to a DBA, they ripped out the CTEs and replaced them all with temp tables. The query was an unreadable mess at the end, but boy was it orders of magnitude faster.
Re: CTEs as lookup tables
#67This 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…
This is a pretty huge caveat, one that I'm very thankful to know of. Thanks for the heads up!
Re: CTEs as lookup tables
#68Earlier quoted context omitted.
I have also seen the performance boost by using separate temp tables rather than CTEs (in my case in Redshift). My hypothesis is that while memory use my be the same either way, you don't have the same transactional/locking requirements/overhead/consistency with multi statements as you do with a single big set of CTEs. And for analytics you rarely need that transactional isolation required by a single big CTE.
That's because Redshift doesn't support MATERIALIZED CTEs. In mainline Postgres, a MATERIALIZED CTE acts more like a temporary table with the performance characteristics you're looking for.
Except those won’t have any indexes on them, so any joins will be horrifically slow?
Re: CTEs as lookup tables
#69Earlier quoted context omitted.
I have strongly encouraged reckless use of CTEs throughout our product. We use in-memory instances of SQLite to evaluate any queries which would leverage CTEs. These datasets are usually small enough to reside within L1 (and certainly within L2).
In my experience a huge portion of the userbase who loves CTEs are analysts or devs running queries on decently large datasets, and they mostly like them because of readability and don't understand performance possibilities. I appreciate that tools like dbt allow materialization options as both CTEs and views/tables/etc because being able to pivot between them is super nice.
Strange take you have there in assuming they don't understand performance possibilities. Many of the problems that analysts and such work on aren't performance bound because focusing on the performance would be a net waste of time. "Don't optimize early" and all that. For example, I had to share SQL recently where the absolute most important thing, beyond all else was to make it readable. CTEs fit the role.
Re: CTEs as lookup tables
#70Seems like an interesting idea, but could use a better example, at least for those who aren't yet intermediate level in SQL. In what world is this WITH countries (code, name) AS ( ...> SELECT \* FROM (VALUES ...> ('us', 'United States'), ('fr', 'France'), ('in', 'India') ...> ) AS codes ...> ) ...> SELECT data.code, name FROM data LEFT JOIN countries ON countries.code = data.code; easier to read than this SELECT code…
To me the first example makes me assume that there isn’t yet a `country` table to select from. Hence the `from values()` clause.
If you were to run the first query on a fresh db, it’d return data. Running the second would fail.