Paging records on SQL server using derived tables

I am using derived tables to Page data on the SQL Server side.
I used this link as my mentor for doing paging on the SQL
Serverhttp://msdn2.microsoft.com/en-us/library/ms979197.aspx
I wanted to use USER PAGING, thus I used the following code:
CREATE PROCEDURE UserPaging
(
@.currentPage int = 1, @.pageSize int =10
)
AS
DECLARE @.Out int, @.rowsToRetrieve int, @.SQLSTRING nvarchar(1000)
SET @.rowsToRetrieve = (@.pageSize * @.currentPage)
SET NOCOUNT ON
SET @.SQLSTRING = N'select
CustomerID,CompanyName,ContactName,ContactTitle from
( SELECT TOP '+ CAST(@.pageSize as varchar(10)) +
'CustomerId,CompanyName,ContactName,ContactTitle from
( SELECT TOP ' + CAST(@.rowsToRetrieve as varchar(10)) +
'CustomerID,CompanyName,ContactName,ContactTitle FROM
( SELECT TOP ' + CAST(@.rowsToRetrieve as varchar(10)) +
'CustomerID,CompanyName,ContactName,ContactTitle FROM Customers as T1
ORDER BY contactname) AS T2 ORDER BY contactname DESC ) AS T3)
As T4 ORDER BY contactname ASC'
EXEC(@.SQLSTRING)
RETURN
GO
When I use this. Assume that the Total records returned by the SQL
query is 1198.Thus when I am on Page1 the above Stored Proc (SP) will
return the first 1000 records.
This works absolutely fine.
Now I am on Page2, now I need to retrieve only the remaining 198
records.But if I use the above SP, it will return the last 1000
records.So to tweak this I used the following logic to set the
@.pagesize variable:
Declare @.PageCount int
select @.PageCount = @.TotalRows/@.PageSize
if @.currentPage > @.PageCount SET @.PageSize = @.TotalRows%@.PageSize
Since I am on Page2 the above logic will set the PageSize to 198 and
not 1000.But when I use this logic, it takes forever for the SP to
return the 198 records in a resultset.
However if the TotalRows were = 1800, and thus the PageSize=800 or
greater, this SP returns the resultset quickly enough.
Thus to get over this problem I had to use the other logic i.e. using
Application Paging (i.e. first storing the entire result set into a
Temp table, then retrieving only the required records for the PAGE)
Can anyone suggest what is wrong with my user paging logic'?
TIA...Hi
"rbg" wrote:
> I am using derived tables to Page data on the SQL Server side.
> I used this link as my mentor for doing paging on the SQL
> Serverhttp://msdn2.microsoft.com/en-us/library/ms979197.aspx
> I wanted to use USER PAGING, thus I used the following code:
> CREATE PROCEDURE UserPaging
> (
> @.currentPage int = 1, @.pageSize int =10
> )
> AS
> DECLARE @.Out int, @.rowsToRetrieve int, @.SQLSTRING nvarchar(1000)
> SET @.rowsToRetrieve = (@.pageSize * @.currentPage)
> SET NOCOUNT ON
> SET @.SQLSTRING = N'select
> CustomerID,CompanyName,ContactName,ContactTitle from
> ( SELECT TOP '+ CAST(@.pageSize as varchar(10)) +
> 'CustomerId,CompanyName,ContactName,ContactTitle from
> ( SELECT TOP ' + CAST(@.rowsToRetrieve as varchar(10)) +
> 'CustomerID,CompanyName,ContactName,ContactTitle FROM
> ( SELECT TOP ' + CAST(@.rowsToRetrieve as varchar(10)) +
> 'CustomerID,CompanyName,ContactName,ContactTitle FROM Customers as T1
> ORDER BY contactname) AS T2 ORDER BY contactname DESC ) AS T3)
> As T4 ORDER BY contactname ASC'
> EXEC(@.SQLSTRING)
> RETURN
> GO
> When I use this. Assume that the Total records returned by the SQL
> query is 1198.Thus when I am on Page1 the above Stored Proc (SP) will
> return the first 1000 records.
> This works absolutely fine.
> Now I am on Page2, now I need to retrieve only the remaining 198
> records.But if I use the above SP, it will return the last 1000
> records.So to tweak this I used the following logic to set the
> @.pagesize variable:
> Declare @.PageCount int
> select @.PageCount = @.TotalRows/@.PageSize
> if @.currentPage > @.PageCount SET @.PageSize = @.TotalRows%@.PageSize
> Since I am on Page2 the above logic will set the PageSize to 198 and
> not 1000.But when I use this logic, it takes forever for the SP to
> return the 198 records in a resultset.
> However if the TotalRows were = 1800, and thus the PageSize=800 or
> greater, this SP returns the resultset quickly enough.
> Thus to get over this problem I had to use the other logic i.e. using
> Application Paging (i.e. first storing the entire result set into a
> Temp table, then retrieving only the required records for the PAGE)
> Can anyone suggest what is wrong with my user paging logic'?
> TIA...
>
Have you seen
http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html
and
http://databases.aspfaq.com/database/how-do-i-handle-alphabetic-paging.html ?
With SQL 2005 there are new functions that could help.
John