performance of a SELECT of a SELECT

Question

I was wondering if a SELECT of a SELECT incurs any additional performance, or if the database is smart enough to optimize this.

For example, I have some selects I do like the following:

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

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.

This isn't the full query, but just an example. I know I could just simply doing this from a single SELECT, but I was wondering if doing a SELECT of a SELECT would incur some sort of performance penalty, or if this all gets optimized magically.

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 select with another table, and selecting out the columns from the underlying SELECT 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.

Have 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. — fkerber, Jan 08 '12 at 21:51
I 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... — Yahia, Jan 08 '12 at 21:54

score 4 · Accepted Answer · edited May 23 '17 at 12:12

4

Nesting SELECTs like this are considered one statement to the optimiser.

You can see this here: Why use Select Top 100 Percent? and How can I query 'between' numeric data on a not numeric field? In these examples, the nesting here has no effect unless you force it which is generally a bad idea

edited May 23 '17 at 12:12

Community

1
1

answered Jan 08 '12 at 21:58

gbn

422,506
82
585
676

score 2 · Answer 2 · answered Jan 08 '12 at 21:57

Put these 2 queries in your query window

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

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 DATEPART(year, transactionTime), DATEPART(month, transactionTime), DATEPART(day, transactionTime)

Then look at the actual execution plan,

http://technet.microsoft.com/en-us/library/ms189562.aspx

it will show you the execution plan for both queries plus the relative cost for both of them, my guess that both of them will be 50% meaning that both have the same cost (but I might be wrong, please let us know what you find out)

Thanks, I didn't know about this, it did come out as 50% for both. — steve8918, Jan 09 '12 at 14:29
@steve8918 I use this all the time when I am in doubt, really important tool and now you have a way to always know for sure without having to guess — Bassam Mehanni, Jan 09 '12 at 14:30

a1ex07 · Answer 3 · 2012-01-08T22:06:41.383

2

Using derived tables or CTE will probably add a very small overhead during the parsing stage. I'd rather neglect it for the sake of readability.

Update I'm talking about cases when you technically don't have to use it (as in your question)

edited Jan 08 '12 at 22:06

answered Jan 08 '12 at 21:58

a1ex07

36,826
12
90
103

score 0 · Answer 4 · answered Jan 08 '12 at 21:57

It can slow down the query, but it depends what you are actually selecting. In this case, the main level select doesn't do anything fancy, and it isn't even needed. The server will probably optimize this query as if it was just a single select. But that's not always the case, it depends on the query. And on the database as well. MySQL, Oracle and SQL Server all have great optimizers, but the kinds of optimizations differ quite a lot.

performance of a SELECT of a SELECT

4 Answers4