Performance on CASE statement (dynamic ordering)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Performance on CASE statement (dynamic ordering)

Author

Topic

jchendy
Starting Member

1 Post

Posted - 2006-06-28 : 17:34:56

I have a query to return a certain number of rows to fill up a single page on a website, something like:

select * from

(

select ROW_NUMBER()

OVER(ORDER BY

performer)

as rowNum, *

from

[…]

) as temp

where (rowNum BETWEEN 0 AND 20)

That works fine and take somewhere around a few milliseconds. But, I need to be able to sort by any column, so I changed it to something like this:

declare @sortCol as nvarchar(100)

set @sortCol = 'artist'

select * from

(

Select ROW_NUMBER() OVER(ORDER BY

CASE WHEN @sortCol = 'artist' THEN performer

[…]

END

) as rowNum, *

from

[…]

) as temp

where (rowNum BETWEEN 0 AND 20)

Everything is the same except for the CASE, and the output is the same, but now it takes about 3 second to execute.

Looking at the execution plan, the first step is where the difference is. It’s a clustered index seek, and for the first query, the number of rows is 20, but for the second, it’s about 400,000.

Why doesn’t the new query stop when it gets to the required 20? How can I make it faster???

Thx,

Jeff

update: It has the same issue on these two more simple queries:

select top 100 *
from dbo.mrc_albumissuedata
order by performer

declare @sortCol as nvarchar(10)
set @sortCol = 'artist'
select top 100 *
from dbo.mrc_albumissuedata
order by
CASE WHEN @sortCol = 'artist' THEN performer
END

On the second, it is not using the index I have on the performer column, instead it is getting all of the rows (1.3 million) and sorting them itself. Ideas?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-06-28 : 23:01:55

Try using the case prior to the query, then use the set value

declare @sortCol nvarchar(10), @nSortCol nVarchar(10)

CASE WHEN @sortCol = 'artist' THEN set @nSortCol = 'performer'
END
exec('select top 100 ' + @nSortCol + ' from dbo.mrc_albumissuedata
order by ' + @nSortcol)

Omit the first @nsortcol if you do not wish to search only that column.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources