Introduction
Most of the web developers must have come across the requirement to implement Server
Side Paging of the data to increase the performance of the application. In the absence
of Server Side Paging, the application will fetch all the data from the database
server and load a specific number of records (depending on the current page being
viewed by the user). Assume that a table has 10,000 records and the paging used
by the application is 50. When the user clicks on Page 2, the application
will fetch all the 10,000 records from the database server and then load records
51 to 100 to the UI control on the web page. This shows that, we are fetching a
lot of records which we really do not use. By fetching only the records that we
need (in the above example, records 51 from 100) from the database server, we can
gain better performance at the database server level as well as at the application
level.
There are quite a few articles available on Internet which address this problem from different angles. Some of the interesting articles that I could find are the following:
None of the articles I could find online was considering all the requirements that I was looking for. I wanted that the Server Side Paging code should consider
the following points:
- Select the required number of records based on the current page count and
the page size. If the page size is 25 records and if we are on page 4, then we need to retrieve records from 76 to 100.
- The sort order needs to be handled. The data that we need to retrieve for page 4 will be different when the sort order changes. For example, when the sort
order is First Name a different set of records are to be returned than
City.
- Filters need to be applied in the TSQL code. Most of the times, the data is retrieved against a search operation which takes various filter values. For example, the Employee search might take filters like First Name, City
or Hire Date. It could also be that, the filters are optional. None,
one, many or all of the filters can be specified in the query. If a filter is provided, then the data needs to be filtered for that condition. Otherwise, that filter should be ignored.
At this point, I thought of writing my own version of the Server Side Paging
TSQL code which takes care of all the points mentioned above.
Sample Code
We will use the NorthWind database for the purpose of this example. The
following are the requirements that this example will fulfill.
- A web page needs to be created for displaying a list of Employees
- User can search by First Name, Title and City
- User can enter None, One, Two or All of the filters
- We will use LIKE matching while applying the filters
- The page should display only 10 records at a time. Paging should be implemented for viewing other records.
- When a specific page number is clicked, the data of that page needs to be loaded
- User can sort the results by First Name, Title, City,
or Hire Date
- After sorting the results by a column, when the user clicks on a page number, the paging should happen based on the current sort order.
Here is the stored procedure which satisfies the above requirements. [code]
1 CREATE PROCEDURE GetEmployees(
2 @LastName VARCHAR(20) = NULL,
3 @Title VARCHAR(20) = NULL,
4 @City VARCHAR(20) = NULL,
5 @PageSize INT = 5,
6 @PageNumber INT = 1,
7 @SortOrder VARCHAR(20) = 'LastName'
8 )
9 AS
10
11 SET NOCOUNT ON
12 /*
13 Let us use a CTE to simplify the code. The below CTE makes the code easier
14 to read and understand.
15 */
16 ;WITH emp AS (
17 SELECT
18 /*
19 Based on the sort order passed into the stored procedure, a Record Identifier
20 (sequential number) is generated using the ROW_NUMBER() method. The sequential
21 number is generated in the sorted order.
22 */
23 CASE
24 WHEN @SortOrder = 'Title' THEN ROW_NUMBER()OVER (ORDER BY Title)
25 WHEN @SortOrder = 'HireDate' THEN ROW_NUMBER()OVER (ORDER BY HireDate)
26 WHEN @SortOrder = 'City' THEN ROW_NUMBER()OVER (ORDER BY City)
27 -- In all other cases, assume that @SortOrder = 'LastName'
28 ELSE ROW_NUMBER()OVER (ORDER BY LastName)
29 END AS RecID,
30 LastName,
31 FirstName,
32 Title,
33 HireDate,
34 City,
35 Country,
36 PostalCode
37 FROM employees
38 WHERE
39 /*
40 Apply the filter. If the filter is specified, then apply the filter.
41 If not, ignore the filter.
42 */
43 (@LastName IS NULL OR LastName LIKE '%' + @LastName + '%')
44 AND
45 (@Title IS NULL OR Title LIKE '%' + @Title + '%')
46 AND
47 (@City IS NULL OR City LIKE '%' + @City + '%')
48 )
49
50 /*
51 Select the final query result.
52 */
53 SELECT
54 RecID,
55 LastName,
56 Title,
57 HireDate,
58 City
59 FROM emp
60 /*
61 Apply a RANGE filter on the requested SORT ORDER to retrieve the records of the
62 current page. If the "Page Number" is 3 and "Page Size" is 30 then records 61 to
63 90 are retrieved.
64 */
65 WHERE RecID BETWEEN ((@PageNumber - 1) * @PageSize) + 1 AND @PageNumber * @PageSize
66 /*
67 "RecID" is a value generated by the previous CTE based on the sort order specified
68 by the @SortOrder parameter.
69 */
70 ORDER BY RecID
Let us execute the stored procedure. [code]
1 -- Let us retrieve the first page sorted by "Last Name"
2 EXECUTE GetEmployees @PageSize = 3, @PageNumber = 1, @SortOrder = 'LastName'
3
4 /*
5 OUTPUT:
6
7 RecID LastName Title HireDate City
8 -------------------- -------------------- ------------------------------ ----------------------- ---------------
9 1 Buchanan Sales Manager 1993-10-17 00:00:00.000 London
10 2 Callahan Inside Sales Coordinator 1994-03-05 00:00:00.000 Seattle
11 3 Davolio Sales Representative 1992-05-01 00:00:00.000 Seattle
12 */
13
14 -- Let us retrieve the second page sorted by "Last Name"
15 EXECUTE GetEmployees @PageSize = 3, @PageNumber = 2, @SortOrder = 'LastName'
16
17 /*
18 OUTPUT:
19
20 RecID LastName Title HireDate City
21 -------------------- -------------------- ------------------------------ ----------------------- ---------------
22 4 Dodsworth Sales Representative 1994-11-15 00:00:00.000 London
23 5 Fuller Vice President, Sales 1992-08-14 00:00:00.000 Tacoma
24 6 King Sales Representative 1994-01-02 00:00:00.000 London
25 */
26
27 -- Let us retrieve the third page sorted by "City"
28 EXECUTE GetEmployees @PageSize = 3, @PageNumber = 3, @SortOrder = 'City'
29
30 /*
31 OUTPUT:
32
33 RecID LastName Title HireDate City
34 -------------------- -------------------- ------------------------------ ----------------------- ---------------
35 7 Davolio Sales Representative 1992-05-01 00:00:00.000 Seattle
36 8 Callahan Inside Sales Coordinator 1994-03-05 00:00:00.000 Seattle
37 9 Fuller Vice President, Sales 1992-08-14 00:00:00.000 Tacoma
38 */
Conclusions
There are different ways to implement the above functionality. The above code can be re-written in different ways. For example, the ORDER BY clause can take an expression which uses a CASE statement with ROW_NUMBER().