June 2, 2005 at 8:52 am
Hi all,
I'm really facing a bad problem.
I'm using SQLServer as back-end and ASP as front-end.
I need to display information from a table(joined with other 2 or 3 at most) that has more than 54,000 records, so after spending some time reading (including this forum and articles),
I decided to implement pagination in back-end.
There are two major approach to implement pagination:
1) Retrieve more than 54,000 records to front-end and move the set pointer row by row up to the desired position, a very procedural solution on a relational model.
2) And this one that was what common sense told me to choose.
Let SQL Sever do what it is good for. I read a SP in your forum and I adapted it to my needs. The SP just retrieves the desired amount of records, say for instance 25 from page 1(meaning records 1 to 25), or 25 records from page 2(meaning records 26 to 50), and so on. This way front-end just uses 25 rows every time(no matter what page, they are just 25). So once front-end got the records, it's an easy job, but the problem is when sql server try to retrieve the records.
The following query took 1 min and 40 sec to show a result in query analizer(obviously my asp scripts timed out at all times, can't imagine 20 user executing the same script):
SELECT R.*, PB.pb_PubType, PB.pb_PubName, PR.pr_Desc FROM tblRecorder R
LEFT OUTER JOIN tblPublication PB ON R.rc_Directory=PB.pb_RecNo
LEFT OUTER JOIN tblProducts PR ON R.rc_Product=PR.pr_ProdNo
WHERE R.rc_AdNo IN
( SELECT TOP 25 R.rc_AdNo FROM tblRecorder R
LEFT OUTER JOIN tblPublication PB ON R.rc_Directory=PB.pb_RecNo
LEFT OUTER JOIN tblProducts PR ON R.rc_Product=PR.pr_ProdNo
WHERE 1=1 AND R.rc_AdNo NOT IN
( SELECT TOP 25225 R.rc_AdNo FROM tblRecorder R
LEFT OUTER JOIN tblPublication PB ON R.rc_Directory=PB.pb_RecNo
LEFT OUTER JOIN tblProducts PR ON R.rc_Product=PR.pr_ProdNo
WHERE 1=1
ORDER BY rc_ListName ASC )
ORDER BY rc_ListName ASC )
ORDER BY rc_ListName ASC
there is a total of 54687 rows, meaning 2188 pages of 25 rows each.
While retrieving the first pages(page 1 to 200), no problem, a very quick reponse, the same while retrieving the last pages(2000-), the big problem is while retrieving the middle pages(1000, 1010, etc.)
Now, if in the above query, 25 rows are switched to retrieve 50 rows, the query got executed in a fraction of second, obviously it is not a data domain value since the 50 selected rows includes the original 25 rows that took 2 minutes to be retrieved, plus 25 more records that doen't belong to the 25225 rows set that meets the same criteria.
I really dont know what is going on with this, I have read about views, etc, etc, etc, and I dont know why is this happening.
If I decide to choose 50 rows per page, what about when the table doubles the current amount?
Any idea? any comment or suggestion or help or anything will be greatly appreciated.
Regards,
Orlando Otero
P.S. sorry about my english.
June 2, 2005 at 3:42 pm
Hi all, here are a discussion on this thread with finally a SOLUTION:
http://www.tek-tips.com/viewthread.cfm?qid=1070419&page=1
Hope this help to all who face the same situation.
Regards,
Orlando Otero
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply