locked
Paging in large record set in sql server 2008 RRS feed

  • Question

  • Hi,

    I am using sql server 2008 and I want to implement paging. Below is how I am currently using paging concept. Table name is test_table and ID column is primary key.


    ;WITH SQLPaging
    AS
    (
        select TOP(@rowsPerPage * @pageNum)
        ResultNum = ROW_NUMBER() OVER (ORDER BY id),*
        from test_table                                          
    
    )
    SELECT *
    FROM SQLPaging
    WHERE ResultNum > ((@pageNum - 1) * @rowsPerPage)
    




    The number of records per page is only 10. If there are more records, the first page is loading fast and If I go to page number 500, it is taking some time. Is there any way that I can make it faster if I go to page number 500 or above that?

    Thanks,
    Rakesh.

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    Thursday, February 14, 2013 12:22 PM

Answers

  • Many many years ago, I attended a PASS session where the presenter discussed something like 18 ways to page a result set. How you should do it, really depends on the circumstances.

    *  Do you expect the user to look at random page in the result set?
    *  Do you execpt the user to ever go past, say, row 1000?
    *  How much can you afford to cache in the client?
    *  Can you permit that the results watches between data access?

    In many cases, the best approach is to simply to read 1000 rows, and then page in the client. If the user wants to see more rows, he is out of luck. The advantage is that you have no network roundtrip to get the data. You should only engage in paging in the database, if this is not an acceptable solution.

    In a web app, it may not be desireable to maintain any sort of state, but if you don't, there is not really much you can do. If you want to displays rows 591 to 600, you must sift through the first 590 rows. And if there is no index to follow, this means a full sort. The only time, it can be fast is there is a table where the search results are materialised and rows are number so that you can read with a simple WHERE clause. But now you need to maintain state again, but you could use a cookie (whereupon I will hate that web site, because I run with cookies disabled). And you still have a problem of when cleaning up the table.

    As you see the problem is a lot more difficult that it may seem initially.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Thursday, February 14, 2013 3:27 PM
    • Marked as answer by Iam_Rakesh Friday, February 15, 2013 7:36 AM
    Thursday, February 14, 2013 12:52 PM

All replies

  • 	DECLARE  
    		  @pgSize INT 
    		, @pgNbr	INT 
    
    	SELECT
    		 @pgSize = 10
    		,@pgNbr = 500
    
    SELECT        
    	*	
    		FROM
    		 (
    			
    			 select 
    			ROW_NUMBER() OVER(
    				ORDER BY ID) AS Row_ID        
    				,*
    			from test_table            
        
    			) MyTable
    				WHERE 
    
    					Row_ID >= (@pgSize * @pgNbr) - (@pgSize -1) AND
    					Row_ID <= @pgSize * @pgNbr
    			ORDER BY ID
    
    try This

    Thursday, February 14, 2013 12:38 PM
  • Why do you need TOP clause?

    DECLARE @page_size INT;
    DECLARe @page_nbr INT;

    SET @page_size = 50;
    SET @page_nbr = 2;

    SELECT a, b, c, d
    FROM (SELECT a, b, c, d,
                        ROW_NUMBER() OVER(
                        ORDER BY a DESC, b DESC, 
                                      c DESC, d DESC)
          FROM SomeTable) AS T(a, b, c, d, rn)
    WHERE rn > (@page_nbr - 1) * @page_size 
       AND rn <= @page_nbr * @page_size;


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thursday, February 14, 2013 12:38 PM
    Answerer
  • Many many years ago, I attended a PASS session where the presenter discussed something like 18 ways to page a result set. How you should do it, really depends on the circumstances.

    *  Do you expect the user to look at random page in the result set?
    *  Do you execpt the user to ever go past, say, row 1000?
    *  How much can you afford to cache in the client?
    *  Can you permit that the results watches between data access?

    In many cases, the best approach is to simply to read 1000 rows, and then page in the client. If the user wants to see more rows, he is out of luck. The advantage is that you have no network roundtrip to get the data. You should only engage in paging in the database, if this is not an acceptable solution.

    In a web app, it may not be desireable to maintain any sort of state, but if you don't, there is not really much you can do. If you want to displays rows 591 to 600, you must sift through the first 590 rows. And if there is no index to follow, this means a full sort. The only time, it can be fast is there is a table where the search results are materialised and rows are number so that you can read with a simple WHERE clause. But now you need to maintain state again, but you could use a cookie (whereupon I will hate that web site, because I run with cookies disabled). And you still have a problem of when cleaning up the table.

    As you see the problem is a lot more difficult that it may seem initially.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Thursday, February 14, 2013 3:27 PM
    • Marked as answer by Iam_Rakesh Friday, February 15, 2013 7:36 AM
    Thursday, February 14, 2013 12:52 PM
  • *  Do you expect the user to look at random page in the result set? - YES

    *  Do you execpt the user to ever go past, say, row 1000? - YES
    *  How much can you afford to cache in the client? - Not much
    *  Can you permit that the results watches between data access? - NO

    URI and Junaid, I tried both your queries and both are taking time. I see you are having all the records in a sub query and then from that you are filtering the required no of rows.. is there any other way of directly choosing  10 records of page number 500 ?



    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    Thursday, February 14, 2013 1:44 PM
  • *  Do you expect the user to look at random page in the result set? - YES
    *  Do you execpt the user to ever go past, say, row 1000? - YES
    *  How much can you afford to cache in the client? - Not much
    *  Can you permit that the results watches between data access? - NO

    I am sorry, my last question was complete nonsense due to a severe disconnect between brains and fingers. What I meant to ask was: can you permit that the result change as the user page through the result set? That is, the user says: "Show me the widgets that match this criteria" and the initial search finds 564 widgets and you show the first 10 widgets according to the chosen sort order. While the user is watching the first page, a new widget comes in, and this widget ranks third according to the user. The user now presses next, and if you have no state but requery, he will be shown widgets 12 to 21, rather than widgets 11 to 21. Is this acceptable or not? (If you answer NO, you have a big problem, given you other answers above.)

    Given the answers above, you will not may much choice but to requery, and it will not be fast when the user wants to see data far away in the result set. Of course, I am here making the assumption that the user can specify a couple of search conditions and also choose sort orders. If the search conditions and sort order are fixed, there is the option to maintain a table with contiguous numbering, from which you eaily can get any interval.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, February 14, 2013 2:19 PM
  • To support for random access into pages deep into the table you need to optimize the global ordering of all the rows.  In the typical case of a single clustered unique index performing the ordering, you have to table scan to identify the target rows, and for retrieval of deep pages you need to scan a significant portion of the table.

    One way to do this is to have a unique, non-clustered index containing only your ordering keys.  Number those and generate a list of ids based on row number, then do a nested loops join to get your table rows. 

    eg

    use tempdb 
    
    create table TableToPage
    (
      id int identity primary key,
      a int, 
      b int,
      c char(200) not null default ''
    )
    
    --insert 1,000,000 rows
    insert into TableToPage(a,b)
    select top (1000*1000) o.object_id, c.column_id
    from sys.objects o, sys.columns c, sys.columns c2
    
    
    set statistics io on
    
    declare @begin int = 500000;
    declare @end int = @begin + 20;
    with q as
    (
     select *, row_number() over (order by id) rn
     from TableToPage
    )
    select * 
    from q 
    where rn >= @begin and rn < @end 
    
    set statistics io off
    
    --(20 row(s) affected)
    --Table 'TableToPage'. Scan count 1, logical reads 13949, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    
    create unique nonclustered index ux_tableToPage_id on TableToPage(id)
    go
    
    set statistics io on
    
    declare @begin int = 500000;
    declare @end int = @begin + 20;
    
    with q as
    (
     select id, row_number() over (order by id) rn
     from TableToPage 
    )
    select * 
    from TableToPage
    where id in (select id from q where rn >= @begin and rn < @end )
    
    set statistics io off
    
    
    (--20 row(s) affected)
    --Table 'TableToPage'. Scan count 1, logical reads 687, physical reads 0, read-ahead reads 21, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    
    


    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by Naomi N Thursday, February 14, 2013 3:28 PM
    Thursday, February 14, 2013 3:26 PM