Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2002

    Unhappy Unanswered: How to do paging when there are over 80 000 rows in table?

    I have one table in my db which contains some 80 000 rows of data. I want to list the rows in a web page so that one page has 200 rows. I also want to put the first,previous,next and last buttons into the web page so that the users can navigate between the pages/rows nicely.

    How can i do this with SQL-Server? I dont want to read all the rows because it takes too long (20 seconds) and uses too much memory.

    Do i have to do it like this?
    SELECT TOP 200 * FROM TABLE WHERE ID>0 (First page)
    SELECT TOP 200 * FROM TABLE WHERE ID>200 (Second page)
    SELECT TOP 200 * FROM TABLE WHERE ID>400 (Third page)
    .... AND SO ON ....

    ID = Primary key field (identity insert 1, +1)
    (Of course the ID values are different than 0,200,400,.... if there are deleted rows in a table.)

  2. #2
    Join Date
    Feb 2002
    Only 20 seconds for 80,000 rows of data in a web page - not bad. If you can use a query like select top 200 * from table where id > ?, then use a stored procedure with n as a parameter and use that to multiply by 200. As you progress through the web site, for next just add 1 and for previous subtract 1 from the value you submit to the stored procedure. You could use 1 asp page to handle this functionality. Normally, you are not this lucky and have to use remote scripting to handle large recordsets to return x rows to a web page.

    Good luck.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts