Performance issue in the sql server due to paging and order by clause
<p>we have a mvc.net application and we are a search functionality in a page. User can select multiple option and then search the records from the sql server database and showing in a page in the grid format. All the searched records will come in the sorted form and there is also paging button. so user can click on the pager button and go to any of the page. We have written all the sorting, searching and paging login in the sql server SP. Below are the query for this</p>

<pre><code>INSERT INTO #TempTable
(
  RowNumberCount,
  Field1,  
  Field2               
)
SELECT ROW_NUMBER() 
  OVER (ORDER BY   
      CASE WHEN @OrderBy = @Field1_ASC    THEN Field1 ELSE NULL END ASC,
      CASE WHEN @OrderBy = @Field1_DESC   THEN Field1 ELSE NULL END DESC,
      CASE WHEN @OrderBy = @Field2_ASC    THEN Field2 ELSE NULL END ASC,
      CASE WHEN @OrderBy = @Field2_DESC   THEN Field2 ELSE NULL END DESC,
  ,
  Field1,
  Field2              
FROM CTE_Table;

SELECT 
  Field1,
  Field2              
FROM #TempTable
  WHERE RowNumberCount BETWEEN ((@Page_Nm- 1)*@Page_Size )+ 1 AND (@Page_Nm*@Page_Size); 
</code></pre>

<p>We have used the following concept:</p>

<ol>
<li>Searched the records through the CTE.</li>
<li>Insert the CTE values to the temp table with Row Number. </li>
<li>Applied the pagging with the help of Row Number column.</li>
</ol>

<p>Since Row number will be applied on the sorted column passed from the UI. But when we run the this query it takes 6 to 8 seconds for 1 lacks records. We found that it takes time to created the Row number in sorted column. Is there any other way to improve the performance of the given query.</p>