BIG problem while implementing pagination in back-end 4

[orlyotero]

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, 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 a 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.

 

[orlyotero]

vongrunt, I didn't understand that one,

rc_ListName could be found many times in the table, the PK of tblRecorder is rc_AdNo, the other fields could be found more that once, phones could be repeated, Sale amounts too, Advertisement names too, the only field that is going to be unique is rc_AdNo

I'm thinking in retrieving for instance 25225 + 25 rows, and then sort the set inverting the order, I mean, if the 25225 + 25 were sorted ASC, then re-sort the set DESC or vice-versa to get the top 25 of the resulting set, at least they are going to be kind of "plain" sentences and not nested select sentences, understand what I mean?

 

[vongrunt]

Have you tried it anyway?

If rc_AdNo is unique in result set then (rc_ListName, rc_AdNo) together are also unique - and both sort data the way you want.

SELECT TOP N... @variable query gives you values for Nth row according to ORDER BY. Try SELECT @rc_AdNo, @rc_ListName after 1st query to check that. The rest is kinda obvious (TOP and WHERE clause, same ORDER BY).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Catadmin]

Vongrunt is giving you a point of reference where you can minimize the # of records you have to pull without going into cursors or rowsets, etc. That's what the variables do. They are set to a value somewhere down in the records (row 1000 in this case), so instead of nesting, you can use those variables to say "Select X many records before this variable" or "Select X many records after this variable".

Try his code and see if it helps your speed any further. I think it's pretty nifty. I'm going to have to remember that one, vongrunt. @=) Thanks for the idea.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[orlyotero]

but I don't understand what:
-- find unique values for 1000th row according to ORDER BY
select top 1000 @rc_AdNo = rc_AdNo, @rc_ListName = rc_ListName
from tblRecorder
order by rc_ListName, rc_AdNo

will produce, I have used that kind of assigment when I'm sure of retrieving just one row, in this case that sentence will retrieve a set, what the values of @rc_AdNo and @rc_ListName are going to be?

how does it works?

 

[vongrunt]

In that case @variable will get last value from SELECT according to ORDER BY. Here is simple test:

use Northwind

select top 6 * from Customers order by City, Address

declare @CompanyName nvarchar(40)
select top 6 @CompanyName=CompanyName from Customers order by City, Address

select @CompanyName

Run this in QA. Note the last row in 1st result set.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[orlyotero]

wow, fractions of sec.

very very very nice

amazing

thanks a lot

now I have to adapt it to my needs which not always the criteria filter and PK are going to be varchar and int

by the way, this is a newbie question,
does sqlserver support a kind of variant or void data type, I mean a data type which any value could be assigned to?

 

[orlyotero]

Northwind is not installed in the db server, I have installed msde local in my pc and it is not there too

 

[vongrunt]

There is sql_variant data type introduced in SQL2k, though it's usage is rarely recommended.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[orlyotero]

I came up with:

Create table #T(
this_pk int identity(1,1),
other_pk sql_variant, /*not all PKs are of the same type*/
)
go
INSERT INTO #T (other_pk)
SELECT TOP 25250 CAST(rc_AdNo AS SQL_VARIANT) FROM tblRecorder
ORDER BY rc_ListName, rc_AdNo ASC
SELECT */*or specific fields*/ FROM tblRecorder R inner join #T t on r.rc_AdNo=t.other_pk WHERE t.this_pk>25225 order by this_pk
DROP TABLE #T

and although it got executes in a fraction of second, when you lock at the trace tab, vongrunt's solutions has faster values

Once again thank you all, specially to vongrunt.

Orlando Otero