CODEWITHSUNDEEP
sqlasp.net.netsql-serverperformance
Ricky
Ricky

Reputation: 2907

SQL CASE statement in simple query causing extreme slowness

I've constructed a very simple query that's taking extremely long when using a CASE statement versus without. Could you please offer any insight?

Example 1: No CASE statement. Runs < 1s

select * from
(
    select  
        (row_number() over (order by b.BookName)) 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

Example 2: Using CASE statement takes over 30 seconds

select * from
(
    select  
        case @SortBy    
            when 'Price' then
                (row_number() over (order by b.Price))
            else
                (row_number() over (order by b.BookName))           
        end 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

I've actually run those queries as-is with the above hardcoded values. Parameter substitution for hardcoded values offers no benefit.

EDIT: Explicitly selecting the columns instead of b.* also offers no benefit.

Any thoughts?

Upvotes: 1

Views: 1359

Answers (3)

CrimsonKing
CrimsonKing

Reputation: 2896

I can't test this query but I think putting the case INSIDE the row_number function may solve your problem.

select * from
(
    select  
        (row_number() over (case @SortBy when 'Price' then b.Price else b.BookName end))           
        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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269933

Can you try this version?

select * from
(
    select row_number() over (order by b.Price) as rn_price,
           row_number() over (order by b.BookName) as rn_bookname,    
           b.*     
    from
        Books b (nolock) 
        inner join BookPublishRegions p (nolock)
          on b.BookKey = bp.BookKey
    where       
        contains(p.PublishRegionName, 'France')
) as t1
where (t1.rn_price between 100 and 110 and @SortBy = 'Price') or
      (t1.rn_name between 100 and 110 and @SortBy <> 'Price')

I suspect this will also be slow. In fact, you might have a situation where you just cannot speed up this query with options on the sort. I am thinking that you have an index on Books(BookName) and Books(Price). These indexes get used for the join because SQL Server is smart enough to recognize that the index is useful for the row_number(), so returning the rows in order by the desired column is a good optimization. The problem would then be that only one of these indexes could be used, so the case logic (or multiple row numbers) would prevent this optimization.

This is just a guess, but it might make optimizing your query rather difficult.

If this is the issue, then you can solve it by using dynamic SQL. That would create the SQL for the specific option, and the optimizer would do the right thing.

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93724

If you are using Sql Server 2012+ the use OFFSET-FETCH approach instead of Row_number. Try this.

SELECT b.*
FROM   Books b (nolock)
       INNER JOIN BookPublishRegions p (nolock)
               ON b.BookKey = bp.BookKey
WHERE  CONTAINS(p.PublishRegionName, 'France')
ORDER  BY ( CASE
              WHEN @SortBy = 'Price' THEN Price
            END ),
          ( CASE
              WHEN @SortBy = 'Price' THEN NULL
              ELSE b.BookName
            END ) 
            OFFSET 99 ROWSFETCH NEXT 11 ROWS ONLY;

Upvotes: 0

Related Questions