5 ms·

Unless I'm writing 'select *', I always do the select portion of a SQL query last. Starting with from enables helps with autocomplete on most editors too. I act

Unless I'm writing 'select *', I always do the select portion of a SQL query last. Starting with from enables helps with autocomplete on most editors too. I actually with 'select' was at the bottom. The only reason it would be useful at the top would be if it allowed you to use the new/computed column names as aliases later in the query, which doesn't work with most databases.

deleted 7y ago
[deleted]
magicalhippo 7y ago
> The only reason it would be useful at the top would be if it allowed you to use the new/computed column names as aliases later in the query I'm soooo gonna miss that when we have to add MSSQL support soonish. Having to repeat myself is tedious, especial for non-trivial expressions.
- jfim 7y ago
  As far as I know, it supports the WITH keyword, so you can reuse those expressions.
  - magicalhippo 7y ago
    Ah, well still a bit more tedious as far as I can see, but better than nothing. Thanks!
  - hobs 7y ago
    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.
    - jfim 7y ago
      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.
      - wvenable 7y ago
        > Are CTEs an optimization barrier in MSSQL? They are not. They are, however, in Postgres.
        dragonwriter 7y ago
        They are not (or at least less likely to be) in Postgres 12, as they are inlined in a wide variety of cases.
        jeltz 7y ago
        Not anymore since PostgreSQL 12 was released yesterday.
      - hobs 7y ago
        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!"
    - magicalhippo 7y ago
      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 7y ago
        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.

deleted 7y ago

[deleted]

magicalhippo 7y ago

> The only reason it would be useful at the top would be if it allowed you to use the new/computed column names as aliases later in the query I'm soooo gonna miss that when we have to add MSSQL support soonish. Having to repeat myself is tedious, especial for non-trivial expressions.

jfim 7y ago
As far as I know, it supports the WITH keyword, so you can reuse those expressions.
- magicalhippo 7y ago
  Ah, well still a bit more tedious as far as I can see, but better than nothing. Thanks!
- hobs 7y ago
  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.
  - jfim 7y ago
    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.
    - wvenable 7y ago
      > Are CTEs an optimization barrier in MSSQL? They are not. They are, however, in Postgres.
      - dragonwriter 7y ago
        They are not (or at least less likely to be) in Postgres 12, as they are inlined in a wide variety of cases.
      - jeltz 7y ago
        Not anymore since PostgreSQL 12 was released yesterday.
    - hobs 7y ago
      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!"
  - magicalhippo 7y ago
    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 7y ago
      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.