|
|
Posted by rbg on 01/25/07 13:46
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...
Navigation:
[Reply to this message]
|