CODEWITHSUNDEEP
sqlasp.netsql-serverperformancesql-server-2008
Ricky
Ricky

Reputation: 2907

ORDER BY indexed column is still slow

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]

Upvotes: 7

Views: 1075

Answers (6)

Amir Pelled
Amir Pelled

Reputation: 601

Are both scenarios using the indexes you mentioned ? or is the ASC example using another index that doesn't need a key-lookup for the ".*" select.

Upvotes: 0

tellmewhy
tellmewhy

Reputation: 31

Please retrieve a query plan by just adding "explain " before your SELECT sql query. It will answer the question whether indexes are used at all for the evaluation of the query.

Upvotes: 0

EvilPuppetMaster
EvilPuppetMaster

Reputation: 8350

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

Upvotes: 2

Alejandro Gonzalez
Alejandro Gonzalez

Reputation: 77

I would try hard coding the indexes into the Select statement. Since you have the index predefined.

The syntax is

With (NOLOCK, Index(Index_Name))

Upvotes: 0

Lexi847942
Lexi847942

Reputation: 71

Have you tried using just the ascending index with the descending order by? According to this article it should be just as fast to do it this way as to have the ascending index and ascending order by and eliminates the need for the descending index. Worth a shot since a quick experiment. Building SQL Server Indexes in Ascending vs Descending Order

Upvotes: 0

Jason Horner
Jason Horner

Reputation: 3690

I'd look at the estimated query plan in SSMS(Ctrl+L). My suspicion is that it isn't using either index as they are not covering.

furthermore in the case of the slower descending option it is introducing another sort option since the data probably is already coming in sorted do to the selected join strategy.

Without looking at the actual query plan this is all just wild speculation

Upvotes: 0

Related Questions