I have to sort a fairly large set of data based on a computed column.
Here is part of the query that does the sorting

Select Row_Number() over(Order By
CASE WHEN @sortBy Is Not Null AND @sortBy='relevance'
THEN
aRank*0.4+bRank*.2+cRank*.4
END DESC
,CASE WHEN @sortBy Is Not Null AND @sortBy='date' THEN CreateDate END DESC
) As rowNumber
......
from X
OUTER APPLY
(
Select SkillMatchRank...........
) SMR
Where
SkillMatchRank>0

3 Answers
3

Take a look at the execution plan and see where your problem spots are for certain. If it is the computed value you could look into creating a calculated column in your table or maybe creating an indexed view.

Both of these are persisted forms of the data, meaning it actually takes up space because the calculated values are being stored in advance rather than calculated at run time.

Here are a couple of links to get you started on understanding the execution plan:

It is possible to make index on tables in memory, if you want to add rownumber on a table with a massive rowcount, an index is required. Otherwise you will experience very slow responsive times or at worst it may even fail when running out of memory.

Could you try something like this on your in memory tables ? I don't have access to a database until tomorrow