21–30 of 115 posts
Removing 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.
CTEs (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. (…
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).
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.
CTEs (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. (…
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).
CTEs (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. (…
Removing 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'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 you hold locks---but then you might be subverting your transaction isolation level.
Why is this better than just joining an actual table that expands the codes?
It's better when you need a lookup to multiple columns but will not be needed in any other query/ETL job. Might be steps in a funnel or values in a bespoke customer discount query.
Some DBs do allow struct/array fields but joining against a constant CTE is portable and has low overhead.
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;
SELECT code,
...> CASE code
...> WHEN 'us' THEN 'United States'
...> WHEN 'fr' THEN 'France'
...> WHEN 'in' THEN 'India'
...> END AS country
...> FROM data;Seems 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…
Well, if you have multiple queries, you'd still have to write the cte multiple times. You could also use views for the same thing, and those are much more reusable.
Removing 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…
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.