.NET Daily

SQL

Fast SQL Server Stored Procedure with Filtering, Sorting and Paging

Posted on .

Fast SQL Server Stored Procedure with Filtering, Sorting and Paging

Introduction

Even though there are dozens of articles out there which shows you how to do a Microsoft SQL Server stored procedure with filtering, sorting and paging, the execution time greatly differs between each approach.

So I started to do some research in order to find the best approach out there. And I believe I found it on Aaron Bertrand article about OFFSET/FETCH Pagination.

With the help of these OFFSET and FETCH keywords inside a CTE I managed to build a SQL Stored procedure that was at least twice as fast in return times as the other average ones found on the internet.

You can check it in action out below exemplified on a simple Languages table:

Source Code

ALTER PROCEDURE [dbo].[ListLanguages]
(
	@SearchValue NVARCHAR(50) = NULL,
	@PageNo INT = 1,
	@PageSize INT = 10,
	@SortColumn NVARCHAR(20) = 'Name',
	@SortOrder NVARCHAR(20) = 'ASC'
)
 AS BEGIN
 SET NOCOUNT ON;

 SET @SearchValue = LTRIM(RTRIM(@SearchValue))

 ; WITH CTE_Results AS 
(
    SELECT LanguageId, Name, Code from Language 
	WHERE (@SearchValue IS NULL OR Name LIKE '%' + @SearchValue + '%') 
	 	    ORDER BY
   	 CASE WHEN (@SortColumn = 'Name' AND @SortOrder='ASC')
                    THEN Name
        END ASC,
        CASE WHEN (@SortColumn = 'Name' AND @SortOrder='DESC')
                   THEN Name
		END DESC,
	 CASE WHEN (@SortColumn = 'Code' AND @SortOrder='ASC')
                    THEN Code
        END ASC,
        CASE WHEN (@SortColumn = 'Code' AND @SortOrder='DESC')
                   THEN Code
		END DESC 
      OFFSET @PageSize * (@PageNo - 1) ROWS
      FETCH NEXT @PageSize ROWS ONLY
	),
CTE_TotalRows AS 
(
 select count(LanguageID) as MaxRows from Language WHERE (@SearchValue IS NULL OR Name LIKE '%' + @SearchValue + '%')
)
   Select MaxRows, t.LanguageId, t.Name, t.Code from dbo.Language as t, CTE_TotalRows 
   WHERE EXISTS (SELECT 1 FROM CTE_Results WHERE CTE_Results.LanguageID = t.LanguageID)
   OPTION (RECOMPILE)
   END
GO

Try this approach on your side, especially if you have a different implementation approach. I am super curious to see if it made any difference in terms of performance. On my side for a SQL clustered table with 100+ k rows it got me 2.3x improvement in execution times.

You can download it from below:

Darius

Darius

Darius Dumitrescu is a creative Senior CMS Consultant with in depth .NET knowledge, focused on Web Development and Architecture Design.

There are no comments.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

View Comments (0) ...
Navigation