Pagination Strategy
Pagination is one of the very common need to design a web page (Using C#, PHP, JQuery etc). While writing the code for pagination, some developers think, it is a bad idea to call database multiple times to load data for each web pages instead of that
load all the data at the same time and store in application cache. This strategy is called application level pagination. There are many types of grid which support inbuilt application level pagination. This strategy work good for if we are dealing with less volume of data. For example if data to be loaded from a table named tblUser, below script has to execute to implement application level pagination:
load all the data at the same time and store in application cache. This strategy is called application level pagination. There are many types of grid which support inbuilt application level pagination. This strategy work good for if we are dealing with less volume of data. For example if data to be loaded from a table named tblUser, below script has to execute to implement application level pagination:
SELECT * FROM tblUser
SELECT @@ROWCOUNT
Total logical read: 319
Note: Let's assume tblUser has 100000 records.
Let us assume, if that result set of the query has 100k records or 1 million records or even more than this. Is it a good idea to load all those data at the same time during the first load of a web page? In general an end user is never interested in more than two to three pages. What is need to load all records of a table?
In this strategy a web page with pagination loads fast with if data volume is very less, as soon as data volume will increase the same web page will take time from seconds to minutes to hours to load it. It may come to a situation that web page is not loading at all. We should avoid application level pagination if you predicate data volume may increase with time.
Best strategy is, load only those data which is necessary. During the first load of a web page, fetch only those data from database which is needed to show only on that web page. When an end user click on any other page then fetch only those data from database which is needed to show in that web page. This strategy is called database level pagination. This approach is some extent independent from volume of data in the database. After implementing this, a web page which was initially taking long time to load can load in within fraction of a second.
Sample script to implement database level pagination:
CREATE PROCEDURE GetUsers(
@ntPageNumber AS INT,
@ntPageSize AS INT,
@ntTotalRecord AS INT OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
SELECT @ntTotalRecord = COUNT(*) FROM tblUser
/* Alternative & efficient way to get total records count
SELECT @ntTotalRecord = SUM(st.row_count)
FROM sys.dm_db_partition_stats st
WHERE object_name([object_id]) = 'tblUser'
AND (index_id < 2)
*/
;WITH cteLoadUser
AS (
SELECT *
,ROW_NUMBER() OVER (
ORDER BY ntUserID
) AS ntRowID
FROM tblUser
)
SELECT *
FROM cteLoadUser
WHERE ntRowID BETWEEN (@ntPageNumber - 1) * @ntPageSize + 1
AND @ntPageNumber * @ntPageSize
/* From SQL Server 2012
;WITH cteLoadUser
AS (
SELECT *
,ROW_NUMBER() OVER (
ORDER BY ntUserID
) AS ntRowID
FROM tblUser
)
SELECT *
FROM cteLoadUser
ORDER BY ntUserID
OFFSET @ntPageSize * (@ntPageNumber - 1) ROWS
FETCH NEXT @ntPageSize ROWS ONLY; */
END
Total logical read: 3 + 5 = 8
Comparison of execution plan(Application level vs Database level pagination):
![]() |
Pagination best practices SQL SERVER |
Here we can notice in
case of large volume of data, database level pagination is much better than
application level pagination.
No comments:
Post a Comment