On: SQL queries don't start with SELECT

[hobs]

Which 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.

[magicalhippo → hobs]

Seems 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 → magicalhippo]

It 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.

[jfim → hobs]

It'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.

[hobs → jfim]

Correct, 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!"

[wvenable → jfim]

> Are CTEs an optimization barrier in MSSQL?

They are not. They are, however, in Postgres.

[dragonwriter → wvenable]

They are not (or at least less likely to be) in Postgres 12, as they are inlined in a wide variety of cases.

[jeltz → wvenable]

Not anymore since PostgreSQL 12 was released yesterday.