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 =1000
)
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...rbg (rbg.net@.gmail.com) writes:
Quote:
Originally Posted by
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???
I can't really say what happens, but if you study the query plans you may
do some discoveries.
But it goes to show that for paging there is not any single solution.
Aaron Bertrand has a fairly good overview on this site:
http://www.aspfaq.com/show.asp?id=2120.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
