Posted by AlterEgo on 01/25/07 19:26

rbg,

SqlServerCentral.com has an article on paging. You will need to sign up to
get access.

http://www.sqlteam.com/item.asp?ItemID=26455

-- Bill

"rbg" <rbg.net@gmail.com> wrote in message
news:1169732779.351525.299580@s48g2000cws.googlegroups.com...
>I did use query plans to find out more. ( Please see the thread BELOW)
>
> I have a question on this, if someone can help me with that it will be
> great.
> In my SQL query that selects data from table, I have a where clause
> which states :
>
> where PermitID like @WorkType
> order by WorkStart DESC
>
> @WorkType is a input parameter to the Stored proc and its value is
> '01%'
>
> When I use the above where clause, all the Sorts in the ESTIMATED Query
> Execution plan show me a COST of 28%.
>
> However if I change the query manually to say:
>
> where PermitID like '01%'
> order by WorkStart DESC
>
> The COST of the Sort (in ESTIMATED Query Execution plan) reduces to 2%
> and at the beginning of the PLAN, there is a Bookmark Lookup which
> includes the above where clause.
> Whereas with the FIRST example , the BookMark Lookup in the beginning
> doesn't show that where condition.
>
> Can anyone help me better understand this anomaly?
>
> TIA
> =====================================
> 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
> Server
>
> http://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...
>

[Back to original message]