Paging records on SQL server using derived tables : more question

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...rbg (rbg.net@.gmail.com) writes:
> 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?
That WHERE clause was not in your original post. Nor does the column
name seem entirely familiar. I gather that what you posted yesterday
was a sample from an MSDN article, but it can be tricky to give accurate
answers, ir you don't post your actual code.
But some optimizer bascis: when SQL Server builds the query plan
for a stored procedure, it builds the plan for the procedure as a
whole. This means that it does not know what values that variables
will have at time for execution. The same applies to parameters, but
in this case it does at least know the input value, and uses this
value as guidance. (This is known as parameter sbiffing.)
But SQL Server does not build a query plan every time a procedure
is executed. Instead the plan is put in cache, and the cached plan
will be reused - even if the procedure is called with input values
for which the cached plan is no good.
In your case, assume the the procedure was first called with
WorkType '%01'. For this input value any index on PermitID is not
very useful, so most likely you will get a table scan instead.
On the other hand, when you hardcode a value. SQL Server have full
information, and the odds for a good plan are much better.
To test this theory, you can say:
EXEC your_sp @.WorkTyoe WITH RECOMPILE
if you get a berrer plan, the problem was that you had a plan created
for a differnt value in the cache.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||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...
>