Databases
StackOverflow2013
Postsdbo
POperformance of a SELECT of a SELECT
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POperformance of a SELECT of a SELECT
    primarykey
    data
    text
    <p>I was wondering if a <code>SELECT</code> of a <code>SELECT</code> incurs any additional performance, or if the database is smart enough to optimize this.</p> <p>For example, I have some selects I do like the following:</p> <pre><code>SELECT a.year, b.month, c.day, totalVolume from (SELECT DATEPART(year, transactionTime) as year, DATEPART(month, transactionTime) as month, DATEPART(day, transactionTime) as day, SUM(volume) as totalVolume FROM DOW30 GROUP BY DATEPART(year, transactionTime), DATEPART(month, transactionTime), DATEPART(day, transactionTime)) a order by a.year, b.month, c.day </code></pre> <p>All this particular query does is group all the trades in the Dow30 by date, and sums up the total volume. I use this because the table stores every trade on a per second basis, and I want to roll it up on a per-day basis.</p> <p>This isn't the full query, but just an example. I know I could just simply doing this from a single <code>SELECT</code>, but I was wondering if doing a <code>SELECT</code> of a <code>SELECT</code> would incur some sort of performance penalty, or if this all gets optimized magically.</p> <p>The reason why I formulate the query this way is because that's how I visualize the query, and I actually join this top-level <code>select</code> with another table, and selecting out the columns from the underlying <code>SELECT</code> makes it easier for me to visualize the query. But having no formal DBA background, I'm not sure if this is wrong, and if I need to figure out a better way of writing my queries.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USYahia
    1. USsteve8918
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COHave you had a look for the query plan? I don't know how to get it for sql-server but perhaps you can find it out by yourself. It will show you, how the database sees your query.
      singulars
      1. POperformance of a SELECT of a SELECT
      1. USfkerber
    2. COI am no SQL Server expert but I would expect the server to optimize... since this is not about guessing and more about knowing the only way to know is to check the query plan for the query - then you can see what the server makes out of it, it might even give you some hint on whether an index is needed...
      singulars
      1. POperformance of a SELECT of a SELECT
      1. USYahia
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload