ORDER BY indexed column is still slow

Question

I have the following query that takes < 1s when ORDER BY b.Price is used, and over 10s when ORDER BY b.Price DESC is used

select * from
(
    select  
        /* When changed to ORDER BY b.Price DESC it's 10x slower! */
        (row_number() over (ORDER BY b.Price)) as RowNumber,
        b.*     
    from
        Books b (nolock) 
        inner join BookPublishRegions p (nolock)
          on b.BookKey = bp.BookKey
    where       
        contains(p.PublishRegionName, 'France')
) as t1
where t1.RowNumber between 100 and 110

Any thoughts on why?

I have both an ascending and descending index on b.Price. I'm not really sure what else I can do here...

For reference, I'm including the CREATE script for both indexes below:

CREATE NONCLUSTERED INDEX [IX_Books_PriceDesc] ON [dbo].[Books] 
(
    [Price] DESC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]


CREATE NONCLUSTERED INDEX [IX_Books_Price] ON [dbo].[Books] 
(
    [Price] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Answer 1

As another user has mentioned, it is all wild speculation without seeing a query plan. But I would be surprised if the query used either index in either case. Even if they were covering indexes, you are filtering on the result of a window function in a sub query, the planner has no way of knowing for which rows the row_number function will return 100-110 until it has parsed the entire result set in the sub query, and you haven't actually ordered the subquery by price either, so there'd be no benefit in it using either index. I can't explain why it is faster in the ascending case in these conditions though, we'd have to see a query plan to figure that out, but I suspect something else might be at play.

It looks as if you are doing the window function to implement paging, if so, and you are using 2012 or higher, try using offset/fetch instead, eg:

select  
    b.*     
from
    Books b (nolock) 
    inner join BookPublishRegions p (nolock)
      on b.BookKey = bp.BookKey
where       
    contains(p.PublishRegionName, 'France')
order by price desc 
offset 100 fetch 10

The planner might realise it can use the index then. Although it would probably need to be a clustered or covering index to make any difference to be honest.

If you're on 2008 or earlier, try putting an explicit order by and top in the subquery so the planner realises it can use the index. You can still use the window function and filter in an outer query to do the paging, but this way it will hopefully run the window function over far fewer rows:

select * from
(
select top 110
    (row_number() over (ORDER BY b.Price DESC)) as RowNumber,
    b.*     
from
    Books b (nolock) 
    inner join BookPublishRegions p (nolock)
      on b.BookKey = bp.BookKey
where       
    contains(p.PublishRegionName, 'France')
ORDER BY b.Price DESC
) as t1
where t1.RowNumber between 100 and 110