Advertisements
Home > SQL > Pagination Stored Procedure in SQL

Pagination Stored Procedure in SQL


When listing large amount of data from database, we have to face performance issue in ASP.net pages. There is a default paging mechanism available for some listing controls like Gridview, Listview etc. But all of them have to get total amount of data for manipulation, and from that data they are listing small amount of data using paging mechanism.

 That means, if there are 1 crore rows of data in the table, first of all we need to query out this all records and set to listing control. So it will take very long time to query out this amount of data from the table. 

Better method of pagination in SQL 

So the better mechanism for pagination is, we need to query only small amount of data that we need to display to the user at a time. In order to fetch this function, we have to create a store procedure in SQL with start and end row as parameter. Then If we pass 21 and 30 to the following procedure it will return only 21 to 30 (10 records) and total number of records. So it will be very fast mechanism for pagination.

Pagination Stored Procedure in SQL

 

CREATE PROCEDURE [dbo].[scheduler_view_syncjobs_paging]
@fromRow INT,
@toRow INT
AS
BEGIN

WITH SyncJobs AS
(
      SELECT coalesce(instance.Schedule_Time,
scope.ScheduleTime,getdate()) as ScheduleTime
    ,scope.ScopeName
    ,instance.Status
      ,instance.Schedule_Time as starttedTime
      ,logs.logDetails
      ,ROW_NUMBER() OVER (ORDER BY scope.ScopeID DESC) AS 'RowNumber'
      FROM scheduler_scope scope
      LEFT JOIN scheduler_SyncInstance instance on 
instance.ScopeId=scope.Scopeid
      LEFT JOIN scheduler_logs logs on 
logs.InstanceID=instance.InstanceId
)
SELECT Count(*) as TotalCount
FROM SyncJobs;

WITH SyncJobs AS
(
      SELECT coalesce(instance.Schedule_Time,
scope.ScheduleTime,getdate()) as ScheduleTime
    ,scope.ScopeName
    ,instance.Status
      ,instance.Schedule_Time as starttedTime
      ,logs.logDetails
      ,ROW_NUMBER() OVER (ORDER BY scope.ScopeID DESC) AS 'RowNumber'
      FROM scheduler_scope scope
      LEFT JOIN scheduler_SyncInstance instance on 
instance.ScopeId=scope.Scopeid
      LEFT JOIN scheduler_logs logs on 
logs.InstanceID=instance.InstanceId
)
SELECT *
FROM SyncJobs
WHERE RowNumber BETWEEN @fromRow AND @toRow;

END

GO
Advertisements
Categories: SQL Tags:
  1. No comments yet.
  1. May 27, 2011 at 8:25 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: