Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

CTEs as lookup tables

misfra.me

Post reply on HN
Print view View original

91–100 of 115 posts

Re: CTEs as lookup tables

#91

Post by alexvoda »

wvenable wrote: post #44
cfeduke wrote: post #13

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 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…

Indeed, SQL is tricky like that because there are plenty of performance consequences for a language that is meant to be declarative and optimized by a query planner.

Unless you have realistic data in the test database the performance behavior of a query can be unpredictable.

SQL is very leaky abstraction.

Re: CTEs as lookup tables

#92

Post by santialbo »

charlie0 wrote: post #27

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…

To me the advantanges comes when you use some sort of query builder. You can pass your list in code rather than having to write the SQL.

Re: CTEs as lookup tables

#93

Post by ComodoHacker »

cfeduke wrote: post #13

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. (…

>always benchmark

…on real or at least representative data.

Re: CTEs as lookup tables

#94

Post by JacKTrocinskI »

CTEs are just a nicer, more readable way of writing a subquery. Also, CTEs can be recursive. In Postgres, CTEs can be useful when used with the RETURNING clause. If you're using the same CTE in multiple different queries, then consider creating a view instead. I wouldn’t use a CTE for lookup values, create a lookup table instead.

Re: CTEs as lookup tables

#95

Post by jpalomaki »

I like CTEs, but for complicated queries I find it lot easier to make them run fast by splitting into individual queries and creating temporary tables with appropriate indexes.

Re: CTEs as lookup tables

#96

Post by Ntrails »

Bootvis wrote: post #87
hackernewds wrote: post #37

Earlier quoted context omitted.

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.

I guess the point is you could just stick the results of each step that is currently a CTE into #table as separate queries, and you're only doing the work once to step through the stages?

From a debug point of view that feels more convenient to _me_.

Re: CTEs as lookup tables

#97

Post by alexisread »

JacKTrocinskI wrote: post #94

CTEs are just a nicer, more readable way of writing a subquery. Also, CTEs can be recursive. In Postgres, CTEs can be useful when used with the RETURNING clause. If you're using the same CTE in multiple different queries, then consider creating a view instead. I wouldn’t use a CTE for lookup values, create a lookup table instead.

Not only that, but in Postgres you can do updates in the CTEs, and chain them together eg.

WITH CTE1 AS ( SELECT.... ) CTE2 AS ( INSERT....RETURNING insertid ) INSERT INTO TABLE2 insertid as FK, otherdata...

The CTEs are executed in define-order as a single complete transaction, no need to use multiple calls to insert relational data

Re: CTEs as lookup tables

#98

Post by dx034 »

hackernewds wrote: post #37
drx wrote: post #4

CTEs 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?

With CTEs, the optimizer won't execute the queries but create one optimized version. If you materialize them, execution times can be many magnitudes higher in cases you only end up using small parts of the queries.

Re: CTEs as lookup tables

#99

Post by no-s »

jbrwn wrote: post #14
fphhotchips wrote: post #10

Fun fact, this can also be more performant, depending on the engine.

CTEs can also perform very poorly and often in surprising ways. For example, predicate pushdown is a problem on both MSSQL and Postgresql.

> predicate pushdown is a problem on both MSSQL and Postgresql

Not sure you’re completely on target here regarding CTE performance. I don’t have deep insight into Postgresql (but “create temporary view", yeah!). MSSQL does a pretty good job in Sql2019. If the predicate is sargeable in some way pushdown is reliable. If performance is a concern, examining the residuals can lead to significant insights e.g. applying index filtering which solves obvious problems. Recursion is another story.

CTE is more likely used by data analyst queries because it is an abstraction of composition. It’s not a great abstraction but it’s better than nothing, which is mostly what you get with SQL.

Re: CTEs as lookup tables

#100

Post by mwigdahl »

TheCleric wrote: post #66
cfeduke wrote: post #13

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. (…

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.

Did they do something else other than just popping CTE queries into temp tables? In my experience, _just_ doing that doesn't affect readability much at all.
Post reply on HN