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

111–115 of 115 posts

Re: CTEs as lookup tables

#111

Post by drittich »

pjungwir wrote: post #25
pcblues wrote: post #17

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…

It's not necessarily about locking, it's a way to control what order joins get done in, which, at least in MS SQL, is up to the query engine to decide. By writing to a temp table you can override the query engine's plan, often with huge performance gains.

Less known is that you can get the same effect using a TOP N statement where N is a number larger than the rows you might get back. This presumably avoids the additional i/o penalty of the temp table creation, but I've never validated that. This trick likely is portable across db engines, too.

See https://bertwagner.com/posts/does-the-join-order-of-my-table... - Adam Machanic has some good videos diving deeper into this.

Re: CTEs as lookup tables

#112

Post by emodendroket »

wvenable wrote: post #107
emodendroket wrote: post #106

Earlier quoted context omitted.

If writing CTEs is breaking the optimization I’d argue that’s also an example of not really meeting the “declarative” aspect of the design fully.

I agree but all systems have limitations and SQL engines get better at the job every year. The result of the query is say declaration X. In this case, imagine you can construct a query to formulate X with out a CTE. The database goes off and computes X from the data. But if you start adding CTE's for say readability or because you're building up the query piece by piece and testing it in stages. You might be adding a…

Yes, the more complicated it is, the more likely the optimizer is to get confused... but also the greater the likelihood of bugs and the greater the benefit of something that makes your query easier to read.

Re: CTEs as lookup tables

#113

Post by fphhotchips »

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.

Apologies I'm late to reply to this, but yes. The way each compiler/optimizer handles CTEs can be dramatically different from RDBMS to RDBMS.

For this specific use case though, my comment was made because CASE statements tend to just be bad in comparison to CTEs. Probably dependent on engine though, I don't know them all by heart.

Re: CTEs as lookup tables

#114

Post by TheCleric »

mwigdahl wrote: post #100
TheCleric wrote: post #66

Earlier quoted context omitted.

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.

Not that I recall, but I found it much less readable personally.

Re: CTEs as lookup tables

#115

Post by hobs »

emodendroket wrote: post #59
hobs wrote: post #22

Earlier quoted context omitted.

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.

Readability over optimization is often a reasonable compromise, don't you think?

In my experience no, not in a production database that's doing real work.
Post reply on HN