Paging of Large Results Using Server Cursors

I'm writing an ASP.NET application that uses a SQL Server 2000 database. The
application searches in large tables with 500, 000+ Records and then display
s
the search results, the search results could be easily 20,000 or 30,000
results. Ofcourse i need to use paging to show like 10 or 20 results per
page. Unfortunetly ADO.NET doesn't support the paging functions that were
found in ADO (like PageSize or AbsolutePosition) so i have to implement the
paging myself.
I've read many articles that talk about how paging could be implemented.
Most of them suggest doing the paging through SQL Server using Server
Cursors. I know that cursors are resource intensive and should be avoided
whenever possible but it seems that this is the only solution that fits. I
just want you to notice that the cursor will just loop through 20 or 30
entries no more (Page Size) So is this a problem?
I will be using code that looks similar to this:
---
DECLARE @.PK /* PK Type */
DECLARE @.tblPK TABLE (
PK /* PK Type */ NOT NULL PRIMARY KEY
)
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT @.PK FROM Table ORDER BY SortColumn
OPEN PagingCursor
FETCH RELATIVE @.StartRow FROM PagingCursor INTO @.PK
WHILE @.PageSize > 0 AND @.@.FETCH_STATUS = 0
BEGIN
INSERT @.tblPK(PK) VALUES(@.PK)
FETCH NEXT FROM PagingCursor INTO @.PK
SET @.PageSize = @.PageSize - 1
END
CLOSE PagingCursor
DEALLOCATE PagingCursor
SELECT ... FROM Table JOIN @.tblPK temp ON Table.PK = temp.PK
ORDER BY SortColumn
----
I got this from the article http://codeproject.com/aspnet/PagingLarge.asp
Another method was suggested also that uses RowCount but it doesn't work for
some technical reasons discussed in the article above.
So what do you think should i move on or what?
Regards,
Mohamed SalahLet's assume you are using a stored procedure call to page through a
Customer table and sorting by LastName. All you need to do is maintain in
session state the last offset value of LastName and CustomerID. This should
be fast resource efficeint. For example:
select top 20
LastName,
FirstName,
PhoneNumber
from
Customer
where
LastName > @.PrevLastName and
CustomerID > @.PrevCustomerID
order by
LastName,
CustomerID
"Mohamed Salah" <MohamedSalah@.discussions.microsoft.com> wrote in message
news:1D026C27-4BA2-43F8-B1CF-053D10B127D5@.microsoft.com...
> I'm writing an ASP.NET application that uses a SQL Server 2000 database.
> The
> application searches in large tables with 500, 000+ Records and then
> displays
> the search results, the search results could be easily 20,000 or 30,000
> results. Ofcourse i need to use paging to show like 10 or 20 results per
> page. Unfortunetly ADO.NET doesn't support the paging functions that were
> found in ADO (like PageSize or AbsolutePosition) so i have to implement
> the
> paging myself.
> I've read many articles that talk about how paging could be implemented.
> Most of them suggest doing the paging through SQL Server using Server
> Cursors. I know that cursors are resource intensive and should be avoided
> whenever possible but it seems that this is the only solution that fits. I
> just want you to notice that the cursor will just loop through 20 or 30
> entries no more (Page Size) So is this a problem?
> I will be using code that looks similar to this:
> ---
> DECLARE @.PK /* PK Type */
> DECLARE @.tblPK TABLE (
> PK /* PK Type */ NOT NULL PRIMARY KEY
> )
> DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
> SELECT @.PK FROM Table ORDER BY SortColumn
> OPEN PagingCursor
> FETCH RELATIVE @.StartRow FROM PagingCursor INTO @.PK
> WHILE @.PageSize > 0 AND @.@.FETCH_STATUS = 0
> BEGIN
> INSERT @.tblPK(PK) VALUES(@.PK)
> FETCH NEXT FROM PagingCursor INTO @.PK
> SET @.PageSize = @.PageSize - 1
> END
> CLOSE PagingCursor
> DEALLOCATE PagingCursor
> SELECT ... FROM Table JOIN @.tblPK temp ON Table.PK = temp.PK
> ORDER BY SortColumn
> ----
> I got this from the article http://codeproject.com/aspnet/PagingLarge.asp
> Another method was suggested also that uses RowCount but it doesn't work
> for
> some technical reasons discussed in the article above.
> So what do you think should i move on or what?
> Regards,
> Mohamed Salah
>