Performance when Ordering By Computed Column

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' TH开发者_开发问答EN CreateDate END DESC
        ) As rowNumber
......
from X
OUTER APPLY
    (
        Select SkillMatchRank...........
    ) SMR
Where 
SkillMatchRank>0

Problem is this runs extremely slow.

My Observation:

SkillMatchRank > 0 seem to be whats causing the problem.

Any idea how we could improve this?

---

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:

• http://www.codeproject.com/KB/database/sql-tuning-tutorial-1.aspx
• http://www.sql-server-performance.com/2006/query-execution-plan-analysis/
• http://www.simple-talk.com/sql/performance/execution-plan-basics/

---

Could you make the computed column part of the select statement and just disregard it?

select row_number(), computedcol = aRank*0.4+bRank*.2+cRank*.4
ORDER BY
computedcol

---

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

Alter table <yourtable> add column sortrank cast(arank*.4 + brank*.2 + crank*.4 as real)

CREATE NONCLUSTERED INDEX Idx1 ON <yourtable>(SortRank);

CREATE NONCLUSTERED INDEX Idx2 ON <yourtable>(CreateDate);

Select Row_Number() over(Order By CASE WHEN @sortBy='relevance' THEN Sortrank END DESC 
,CASE WHEN @sortBy='date' THEN CreateDate END DESC) As rowNumber