Nov 16

Sorting, Filtering, Searching and Pagination using SQL Stored Procedure

Application performance is one of the important aspect in any kind of software application. This non functional requirement (NFR)  developers forget while implementing (this in generally I observed while doing code reviews) table or grid design . Developers generally try to fetch all the records from database and displays using third party grids or open source grids.

This is the area where end users or clients complains about the application performance is not up to the mark, page is taking long time to load, grid not loading quickly etc. whenever the table contains large number of records let’s say more than half million ?? This problem increases if you  want to display the hierarchical data.

Is there any solution to fix this problem? if yes, then how ?

Yes, Definitely!!

Generally, in any table we perform Searching or Filtering, Pagination, Page Size, Sorting or Ordering operations means we are displaying fixed size of records on page let’s 10, 20 etc. but we are fetching all the unnecessary records from database. This increases query execution time and converting fetched records to class objects.

The solution to solve this problem, to write SQL query in such a way that it will do all the required operations  (search, pagination, sorting) to fetch data. In following sql stored procedure, I will pass search term, column to sort the record, sorting order (ascending or descending), page number clicked, and number of records parameters to fetch data.

I will use temporary table concept (common table expression) to fetch the records.

PROCEDURE [dbo].[usp_Employee_GetAll] 
	@SearchTerm VARCHAR(50),
	@SortColumn VARCHAR(50),
	@SortOrder VARCHAR(50),
	@PageNumber INT,
	@PageSize INT
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @StartRow INT
	DECLARE @EndRow INT
	
	-- calculate the starting and ending of records
	SET @SortColumn = LOWER(ISNULL(@SortColumn, ''))
	SET @SortOrder = LOWER(ISNULL(@SortOrder, ''))
	SET @StartRow = (@PageNumber - 1) * @PageSize		 
    SET @EndRow = (@PageNumber * @PageSize)+1 			
	
	;WITH CTEResult AS (
		SELECT ROW_NUMBER() OVER (ORDER BY
			CASE WHEN (@SortColumn = 'firstname' AND @SortOrder='asc') THEN FirstName END ASC,
			CASE WHEN (@SortColumn = 'firstname' AND @SortOrder='desc') THEN FirstName END DESC,

			CASE WHEN (@SortColumn = 'lastname' AND @SortOrder='asc') THEN LastName END ASC,
			CASE WHEN (@SortColumn = 'lastname' AND @SortOrder='desc') THEN LastName END DESC,

			CASE WHEN (@SortColumn = 'title' AND @SortOrder='asc') THEN Title END ASC,
			CASE WHEN (@SortColumn = 'title' AND @SortOrder='desc') THEN Title END DESC,

			CASE WHEN (@SortColumn = 'department' AND @SortOrder='asc') THEN Department END ASC,
			CASE WHEN (@SortColumn = 'department' AND @SortOrder='desc') THEN Department END DESC,

			CASE WHEN (@SortColumn = 'employeeid' AND @SortOrder='asc') THEN Id END ASC,
			CASE WHEN (@SortColumn = 'employeeid' AND @SortOrder='desc') THEN Id END DESC
		 ) AS RowNumber
		, COUNT(*) OVER () AS TotalCount
		,[Id]
		,[FirstName]
		,[LastName]
		,[Gender]
		,[Dob]
		,[Title]
		,[Department]
		,[Salary]
		,[Description]
		,[IsActive]
		,[ModifiedDate]
		FROM [dbo].[Employee] 
		WHERE IsActive = 1
		 AND (
				 (ISNULL(@SearchTerm, '') = '' OR Title LIKE '%' + @SearchTerm + '%')
				OR (ISNULL(@SearchTerm, '') = '' OR FirstName LIKE '%' + @SearchTerm + '%')
				OR (ISNULL(@SearchTerm, '') = '' OR LastName LIKE '%' + @SearchTerm + '%')
				OR (ISNULL(@SearchTerm, '') = '' OR Department LIKE '%' + @SearchTerm + '%')
			)
	)

	SELECT RowNumber, TotalCount, [Id], [FirstName], [LastName], [Gender], [Dob], [Title], [Department], [Salary], [Description], [IsActive], [ModifiedDate]
	FROM CTEResult
	WHERE RowNumber > @StartRow AND RowNumber < @EndRow
	ORDER BY RowNumber

END

Now let's test it

CASE 1: Fetch All Records
This will fetches only 10 records and displays first page

Query

EXEC [usp_Employee_GetAll]  '','','',1,10

Output

Sorting pagination and ordering in sql

Sorting pagination and ordering in Sql Stored Procedure

CASE 2: Fetch Records for third page

Query

EXEC [usp_Employee_GetAll]  '','','',3,10

Output

Sorting pagination and ordering in sql

Sorting pagination and ordering in sql - third page

CASE 3: Ascending ordered First Name

Query

EXEC [usp_Employee_GetAll]  '','FirstName','asc',1,10

Output

Sorting pagination and ordering in sql - Ascending Order Firstname

Sorting pagination and ordering in sql - Ascending Order First Name

CASE 4: Ascending ordered First Name and Search term by department

Query

EXEC [usp_Employee_GetAll]  '','FirstName','asc',1,10

Output

Sorting pagination and ordering in sql - Filter term

Sorting pagination and ordering in sql - Filter term

Likewise you can play around this query.

I hope you enjoyed this post. Don't forget to like, subscribe, comment and share this article with your friends.

Leave a Reply