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
No comments:
Post a Comment