Monday, February 9, 2009

Paging as a concept

Paging is yet another mysterious word used in by software developers. In principle it means that instead of downloading all the data returned by a query at once from database, we download subset (page)(first 100 rows) and when user browse through records, we fetch another page, and continue to do so until we have downloaded all the data. When applying this concept we presume that user will narrow its search when he cannot find what he was looking for in first n pages. If done correctly paging can have a significant impact on application performance.

Here is how we do it on all the databases that support rownum:  


SELECT * FROM  
(   SELECT rownum as row_num,
    column1,
    column2,
    column3,
    FROM 
    datatable
)
WHERE ROWNUM <= 201 and  ROW_NUM >= 300

Downside of paging is that it does not guaranty the coherence of data. If data in database is modified during the paging, duplicates or missing data my appear.

Post a Comment