Indexed column ORDER BY is still slow

Question

I have the following query that takes <1s when using ORDER BY b.Price , and more than 10 seconds when using ORDER BY b.Price DESC

 select * from ( select /* When changed to ORDER BY b.Price DESC it 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 quite sure what else I can do here ...

For reference, I include a CREATE script for both indices 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 mentioned, these are all wild assumptions without seeing the query plan. But I would be surprised if the query used either an index anyway. Even if they span indexes, you filter the result of a window function in a subquery, the scheduler does not know for which rows the row_number function will return 100-110 until it analyzes the entire result set in sub, and you actually ordered a subquery by price, so it would not be of any use, using either an index. I can’t explain why this happens faster in the upstream case under these conditions, but we need to see a query plan in order to understand this, but I suspect that something else might be in the game.

It looks like you are performing a window function to implement swap, if so, and you are using 2012 or higher, try using offset / fetch, for example:

 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 can understand that he can use the index. Although it probably should be a clustered or encompassing index in order to make any difference, to be honest.

If you are in 2008 or earlier, try placing an explicit order at the upper end of the subquery so that the scheduler understands that it can use the index. You can still use the window function and filter in the external query to make a search call, but in this way it will hopefully run the window function on a much smaller number of lines:

 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 

Answer 2

I would look at the evaluation plan of the request in SSMS (Ctrl + L). My suspicion is that it does not use any of the indexes as they do not cover.

In addition, in the case of a slower descent option, he introduces another sorting option, since the data is probably already in the sorted file in order to fulfill the selected merge strategy.

Not looking at the actual query plan, it's all just wild speculation

Answer 3

Have you tried to use only the ascending index in decreasing order? According to this article, it should be just as fast to do it so that it has an upward index and an increasing order and eliminates the need for a downward index. It is worth making a quick experiment. Creating SQL Server Indexes in Descending Order

Answer 4

I would try hard code the indices into a Select statement. Because you have a predefined index.

Syntax

 With (NOLOCK, Index(Index_Name)) 

Answer 5

Retrieve the query plan by simply adding “explain” before the SELECT SQL query. He will answer the question of whether indexes are used at all to evaluate a query.

Answer 6

Do you use the indexes you specify in both scenarios? or this is an example of an ASC using another index that does not need to look for a key to select ". *".