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.
| Author |
Topic |
|
pards
Starting Member
3 Posts |
 Posted - 2009-06-30 : 06:24:46
|
| Hi .. I'm trying to add dynamic sorting to my code without the use of Dynamic SQL. I've been successful except that it runs slow (even slower than a dynamic SQL version). When I say slow - this is in comparison to statements I have that use Indexed fields. The Execution Plan shows that using direct hardcoded sorting uses the Non-clustered index for the field so the statement runs very fast (less than a second), but adding the dynamic sorting case statement bypasses the indexes and the sorting is done explicitly and the process runs noticeably slower (a couple of seconds).Is there a way to make Dynamic sorting use the indexes in the database (maybe via hints or something)?My Dynamic Sorting Script :DECLARE @SortField INTselect @SortField = 1select top 100 ROW_NUMBER() OVER ( ORDER BY CASE @SortField WHEN 1 THEN dateStart WHEN 2 THEN price END ASC ) AS SortRowNumber,*FROM tblTableName |
|
|
pards
Starting Member
3 Posts |
Posted - 2009-07-01 : 10:24:26
|
Seems there's no answer. I'm not surprised though as from what I've read - using Case for Dynamic sorting will 'skip' indexes. Anyway I found a workaround using an inelegant brute force approach  but it works - 300 ms searches on 10 million plus records. |
 |
|
|
|
|
|
Can Dynamic Sorting use Indexes