StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I've found solution how to avoid performance penalty using ROW_NUMBER() function over large result sets. A goal I didn't write in my question was to avoid declaring query as nvarchar variable and executing it, because it can cause open door for SQL injection.</p> <p>So, solution is to query data as much as possible in required sort order, then query result set and switch ordering and get data only for current page. Finally I can take result ordered in opposite order and order them again.</p> <p>I defined new variable @innerCount to query most inner result set and order it as query client specify in @sortExpression and @sortDirection variables</p> <pre><code>SET @innerCount = @startRowIndex + @maximumRows Select OppositeQuery.Id ,s.StockNumber ,s.Name ,s.Description FROM (SELECT TOP (@maximumRows) InnerItems.Id FROM (SELECT TOP (@innerCount) sti.Id FROM stockItems sti ORDER BY CASE WHEN @SortDirection=1 THEN CASE WHEN CHARINDEX('Name',@SortExpression)=1 THEN sti.Name WHEN CHARINDEX('StockNumber',@SortExpression)=1 THEN sti.StockNumber ELSE sti.StockNumber END END DESC CASE WHEN ISNULL(@SortDirection,0)=0 THEN CASE WHEN CHARINDEX('Name',@SortExpression)=1 THEN sti.Name WHEN CHARINDEX('StockNumber',@SortExpression)=1 THEN sti.StockNumber ELSE sti.StockNumber END END ASC ) as InnerQuery INNER JOIN StockItems si on InnerQuery.Id=si.Id ORDER BY CASE WHEN @SortDirection=1 then CASE WHEN CHARINDEX('Name',@SortExpression)=1 THEN si.Name WHEN CHARINDEX('StockNumber',@SortExpression)=1 THEN si.StockNumber ELSE si.StockNumber END END ASC CASE WHEN ISNULL(@SortDirection,0)=0 then CASE WHEN CHARINDEX('Name',@SortExpression)=1 THEN si.Name WHEN CHARINDEX('StockNumber',@SortExpression)=1 THEN si.StockNumber ELSE si.StockNumber END END ASC ) AS OppositeQuery INNER JOIN StockItems s on OppositeQuery.Id=s.Id ORDER BY CASE WHEN @SortDirection=1 THEN CASE WHEN CHARINDEX('Name',@SortExpression)=1 THEN s.Name WHEN CHARINDEX('StockNumber',@SortExpression)=1 THEN s.StockNumber ELSE s.StockNumber END END DESC CASE WHEN ISNULL(@SortDirection,0)=0 THEN CASE WHEN CHARINDEX('Name',@SortExpression)=1 THEN s.Name WHEN CHARINDEX('StockNumber',@SortExpression)=1 THEN s.StockNumber ELSE s.StockNumber END END ASC </code></pre> <p>Disadvantage of this approach is that I have to sort data three times, but in case of multiple inner joins to StockItems table subqueries are much faster than using ROW_NUMBER() function.</p> <p>Thank to all contributors for help.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload