How to Optimize this Query

I'm learning MS SQL Server 2008 R2 so please excuse my ignorance. This query takes 3 sec and I would like to do it in less than 1 sec. the query is only for testing purposes, in reality I would join on different fields.

select * from
(
select row_number() over(order by t1.id) as n, t1.id as id1, t2.id as id2, t3.id as id3, t4.id as id4, t5.id as id5
from dbo.Context t1
inner join dbo.Context t2 on t1.id = t2.test
inner join dbo.Context t3 on t2.id = t3.test
inner join dbo.Context t4 on t3.id = t4.test
inner join dbo.Context t5 on t4.id = t5.test
) as t
where t.n between 950000 and 950009;

I'm afraid this will be worse by the time I have several billion records in this table.

Do I need to enable multi-threadin开发者_StackOverflow中文版g from configuration or something?

---

There is no real way to optimize the paging portion of such a query, the part that is

t.n between 950000 and 950009

Which is really

{ ROW_NUMBER } between 950000 and 950009

Without fully materializing the INNER JOINs, there is no way to accurately row-number the result. This is unlike a single table with Row_Number - the Query Optimizer can sometimes just count the index keys and go to a direct range.

The only thing you can do is ensure that the JOIN conditions are all fully indexed and have the indexes INCLUDE the columns that will be selected (so they become COVERING INDEXes). There is no point showing specifics since those are not your real columns.

Do I need to enable multi-threading from configuration or something?

By default, parallelism is [already] turned on so such a query will very likely gather the data in multiple streams.

---

I'd suggest creating the inner query as an indexed view and then running your paging off of that. Since an indexed view actually has a real index on it the same optimization tricks that work on tables can be used.

See here for more information on indexed views including the limitations.