How to do pagination in an optimised way

Hello Everyone,

In our application the most crucial part is data and it’s very important how we interact with database in order to fetch the data. Sometimes we might go straight in order to develop the module faster than usual but it’s very crucial to understand it’s impact on performance. Most common mistake developers do while handling pagination is, They apply ROW_NUMBER(SQL Server) or rank variable (MySql) directly on the result set where as it should be handled very carefully.

I’ve tried here to explain the traditional approach and the optimised approach in order to make an application perform better.

Traditional approach

[code language=”sql”]
CREATE PROCEDURE
Demo_UsingDirectRank(IN ip_PageNo INT,IN ip_RowsPerPage INT)
BEGIN
SET @PageStartRecordIndex=((ip_PageNo – 1) * ip_RowsPerPage) + 1;
SELECT * FROM
(
SELECT @rownum := @rownum + 1 AS Rank,
EncounterInfoWithRowNumber.* FROM
(
SELECT encounter.*
FROM
encounter
INNER JOIN
task ON task.sysEncId=encounter.sysEncId
)EncounterInfoWithRowNumber
)EncounterInfoWithRowNumberResultSet
WHERE
Rank >= @PageStartRecordIndex
AND
Rank < (@PageStartRecordIndex + ip_RowsPerPage);
END
[/code]

Steps for traditional approach

  1. Apply all the joins and where conditions
  2. Select the result set with all the columns (even for those rows which are not required)
  3. Apply the filtration

Disadvantages of traditional approach

  1. Selecting all the columns requires the result set to be taken in memory. For example 1000 Rows(with 100 Columns) are available in database and after applying filter we get 100 Rows. But we are still selecting 100*100 rows+columns which is actually 10000 cells
  2. Here consider that we need only records from 51-60 so we actually need 100*10 cells
  3. Here we are selecting 9000 cells for NO REASON

[code language=”sql”]

CREATE PROCEDURE
Demo_UsingPartialSelectionOfRows(IN ip_PageNo INT,IN ip_RowsPerPage INT)
BEGIN
SET @PageStartRecordIndex=((ip_PageNo – 1) * ip_RowsPerPage) + 1;
SET @rownum:=0;

CREATE TEMPORARY TABLE
TEMP_EncounterIds(EncId INT,Rank INT);

INSERT INTO TEMP_EncounterIds(Rank,EncId)
SELECT * FROM
(
SELECT @rownum := @rownum + 1 AS Rank,
EncounterInfoWithRowNumber.sysEncId FROM
(
SELECT encounter.sysEncId
FROM
encounter
INNER JOIN
task
ON
task.sysEncId=encounter.sysEncId
)EncounterInfoWithRowNumber
)EncounterInfoWithRowNumberResultSet
WHERE
Rank >= @PageStartRecordIndex
AND
Rank < (@PageStartRecordIndex + ip_RowsPerPage);

SELECT encounter.*
FROM encounter
INNER JOIN
TEMP_EncounterIds
ON
TEMP_EncounterIds.EncId=encounter.sysEncId;

DROP TEMPORARY TABLE TEMP_EncounterIds;
END
[/code]

Leave a Reply

Your email address will not be published. Required fields are marked *