14 ms·
As far as I know, it supports the WITH keyword, so you can reuse those expressions.
by jfim 7y ago
As far as I know, it supports the WITH keyword, so you can reuse those expressions.
- magicalhippo 7y agoAh, well still a bit more tedious as far as I can see, but better than nothing. Thanks!
- hobs 7y agoWhich you probably shouldnt, because you are just writing the subquery each time - a temp table in MSSQL is a much better solution than CTEs 99/100 times.
- jfim 7y agoIt's not any different than selecting from a view. Are CTEs an optimization barrier in MSSQL? I would've thought that the query planner could move the execution plan nodes across the CTEs.
- wvenable 7y ago> Are CTEs an optimization barrier in MSSQL? They are not. They are, however, in Postgres.
- dragonwriter 7y agoThey are not (or at least less likely to be) in Postgres 12, as they are inlined in a wide variety of cases.
- jeltz 7y agoNot anymore since PostgreSQL 12 was released yesterday.
- hobs 7y agoCorrect, an inlined view - which again if you are referencing the view multiple times in the query, I would again offer a temp table as a likely performance improvement. edit: I say this from experience fixing hundreds of CTEs that came down to "I want to simplify the downstream query so I am going to package a heck ton of relations in this cte and combine it with this other one to hide the complexity as I do some other stuff!"
- magicalhippo 7y agoSeems the temp table is around for the duration of the connection, or until dropped (we avoid stored procs)? We do a lot of queries like select x.a, (select count(y.b) from tableY y where y.id = x.id) as cnt from tableX x where x.ref = :ref where cnt > 0 Typically a lot more going on than this, but this gets the point across I think. Would we have to drop for each select then?
- hobs 7y agoIt depends - generally you wouldnt ever drop a temp table because when it goes out of scope it dies. If you wanted the CTE/temp table to re-evaluate the query for each select, yeah, you'd effectively have to drop/truncate - but my read on your code is that the subquery would turn into a temp table, be re-used across all your selects, then go out of scope, and disappear.