SQL query optimization (CTE + range function) in SQL Server 2005

Question

I'm wondering whether such query could be potentially optimized. I've hugely simplified it, and you see the core of it.

with Rec (Id,Name,ParentId)
as
(
    select Id,Name,ParentId from Departments where ParentId is null
    union all
    select d.Id, d.Name, d.ParentId from Departments d join Rec r on 
    (d.ParentId=r.Id)
)
select q.* from (
select ROW_NUMBER() OVER (ORDER BY r.Id DESC) AS [ROW_NUMBER], r.* from Rec r
) as q
where q.[ROW_NUMBER] between 100 and 200

What it does is hierarchically query the desendent departments and then do a ranging upon it.

I'm ending up with a huge execution plan and wondering if it can be done in a different manner.

Thank you.

Answer 1

I quess it could be a bit better given the following assumptions:

1. You have proper index by ParentId
2. You retrieve a lot of data (most of the columns) from the table

What can be done: in order to reduce load on io subsystem we can first compose a list of Id's, page them (i.e. filter by RowNumber) and only after that include all other columns. This will effectively result in working with index by ParentId which should be a lot faster given the above two assumptions.

So here's my proposition 'in person' so to say:

with Rec (Id,ParentId)
as
(
    select Id,ParentId from Departments where ParentId is null
    union all
    select d.Id, d.ParentId from Departments d join Rec r on 
    (d.ParentId=r.Id)
),
Paged 
as
(
    select * from (
     select ROW_NUMBER() OVER (ORDER BY r.Id DESC) AS [ROW_NUMBER], r.* from Rec r
    ) as q
    where q.[ROW_NUMBER] between 100 and 200
)
select * 
from 
    Paged
    inner join Departments d on d.Id = Paged.Id