Returning paginated records in MSSQL
Published on: 22nd Dec 2011
Updated on: 19th May 2021
Overview
If the "tb_inv_master_huge" table has a million records, it's not wise to return all records to the browser. That's because there is no way for the tiny screen to show a million records nor the user wanting to see all these records in one page. In this case, you would like to have a functionality for the user to scroll through the records page by page.
Another case is if the user wanted to see the invoices for the current month. You might still end up with more than a thousand records.
Query with paging
In MSSQL, you will be able to paginate the records with the "ROW_NUMBER() OVER (ORDER BY ...)" keyword. The stored procedure will look like this,
create proc pr_get_inv_by_page (
@start_idx int
, @page_size int
, @start_dt datetime
, @end_dt datetime
)
as
begin
select *
from (
select
row_number() over (order by dt) as row_no
, *
from tb_inv_master_huge
where
dt between @start_dt and @end_dt
) as a
where
row_no between @start_idx and @start_idx + @page_size
end
In case the above query is slow (due to many queries were locking tb_inv_master_huge table), you will need a temporary working table that keeps the filtered records. The new stored procedure will look like this,
create proc pr_get_inv_by_page (
@start_idx int
, @page_size int
, @start_dt datetime
, @end_dt datetime
)
as
begin
-- filtered the records by date range
select
inv_id
into #temp
from tb_inv_master_huge
where
dt between @start_dt and @end_dt
order by
dt
-- the following query will be faster because it does not
-- have to lock tb_inv_master_huge table.
select *
from (
select
row_number() over (order by b.dt) as row_no
, b.*
from #temp a
inner join tb_inv_master_huge b on b.inv_id = a.inv_id
) as a
where
row_no between @start_idx and @start_idx + @page_size
-- drop the working table
drop table #temp
end
Caveats of creating a working table
-
The above stored procedure has a potential risk of creating a working table if the user is asking for 10 years of invoices. In this case, the SQL server will be slowing down due to copying 10 years invoices to the working table.
To avoid this situation, you have to limit the number of days and throw an error if the date range is more than "x" days. For how to throw an exception that can be catch by C#, please refers this article: Throw an error in MSSQL and catch by C#
"x" value can be 30 days or 180 days which depends on the average number of records per day. If the daily average has 300 invoices and the user is requesting for 30 days invoices, you may expect the working table contains 9,000 records before joining the tb_inv_master_huge. To find out if this is reasonable, you must do some testing in your server.
-
Lastly, avoid using the "*" (i.e., returning all fields) as it might cause performance issues. It's best to specify the fields that the user wants to see plus a good index. This will yield a better performance.
Related posts
Jump to #MSSQL blog
Author
Lau Hon Wan, software developer.