Q: SQL Server : returning sum value from inner select over rownum I'm on this site every day, but I've never posted before. I'm having the hardest time understanding why my query is taking so long.
Because SQL Server doesn't support returning a range of rows, we've had to get pretty fancy, so we're using a 
SELECT FROM 
    (SELECT ROW_NUMBER() AS rownum...) 
WHERE rownum BETWEEN foo AND bar] 

statement.
The inner select returns ~13,000 rows in < 1 second (run separately). One of the columns returned is a SUM() from a joined table.  
The outer select takes approximately 14 seconds to return the inner select values with a range of 500 - 1000. However, if I comment out the returned SUM, it takes < 1 second - which is what I would expect.  
My question is, why does just the act of returning that SUM in the outer query take so long?
Examples below
Example full query (~14 seconds):
SELECT yadayada, number
FROM (SELECT yadayada, SUM(innerNum) AS number, 
          ROW_NUMBER() OVER (ORDER BY yadayada DESC) AS rownum
      FROM table1
      JOIN table2 ON table1.id = table2.id
      GROUP BY yadayada)
WHERE rownum BETWEEN 500 AND 1000

Example inner query (< 1 second):
SELECT yadayada, SUM(innerNum) AS number, 
          ROW_NUMBER() OVER (ORDER BY yadayada DESC) AS rownum
FROM table1
JOIN table2 ON table1.id = table2.id
GROUP BY yadayada

Example full query w/o returned SUM (< 1 second):
SELECT yadayada --, number (commented out, still returned in inner query)
FROM (SELECT yadayada, SUM(innerNum) AS number, 
          ROW_NUMBER() OVER (ORDER BY yadayada DESC) AS rownum
      FROM table1
      JOIN table2 ON table1.id = table2.id
      GROUP BY yadayada)
WHERE rownum BETWEEN 500 AND 1000


A: I will hazard two guesses, which might explain the problem.
First, when you are measuring the inner subquery, are you measuring the time until the first row appears or the time until the entire result set appears?  My guess is that you are looknig at the first row.
My second guess is that you have an index on yadayada and id.  This means that the subquery can all be satisfied using indexes only.  When you include the additional column innerNum, the query engine actually has to read in the pages from one of the tables.
So, the overhead for the entire query is a combination of two things:


*

*The need to return the full result set

*The need to access pages in th original table


I might be totally off, but this might give you some ideas on what could be causing the problem.two 

A: Just try this:

;WITH CTE as 
(
SELECT yadayada, SUM(innerNum) AS number, 
          ROW_NUMBER() OVER (ORDER BY yadayada DESC) AS rownum
      FROM table1
      JOIN table2 ON table1.id = table2.id
      GROUP BY yadayada
)

select yadayada, number from CTE where rownum between 500 and 1000

