i want the logic of..........

Question

i want the logic of..........

praveenkishan

SSC Journeyman

Points: 90
More actions
October 23, 2006 at 6:29 am

#115235

hi,
in sqlserver,
how i have to query to display first 20 records in a first page,
next 20 records in the next page,next 20 in the next........... and so on.
from a database which has 4,00,000 records fastly.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

That's really not something for SQL Server to do. And it wouldn't happen fast - your query would have to go to the database, pull 20 records, go to the database, pull 20 records, go to the database.....until it got all 400,000 (or 4,000,000 whichever you meant) records. Very inefficient.

You should get all the records and pass them to the front end...then let the front end application page through the results.

-SQLBill

Clive Strong SSChampion Points: 11222 More actions · Answer 2

I agree with SQLBill.

Really not efficient. If your application requested 20 records with each call to the database, you'd end up with 200,000 calls to the database (400,000 in total between client and server).

I guess the key question though, is do you really need to retrieve all 4m rows of data? Even if you did need vast amounts of data, I would retrieve it in bigger chunks than 20 rows and allow the front end application to handle the 20 rows at a time and then make another call to the database when it needs more rows.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Hey guys I think he just needs a paging procedure. Anyone got sample coding around?

Lowell SSC Guru Points: 323518 More actions · Answer 4

this might help:

http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!