|
|
Posted by rbg on 01/26/07 13:49
You are right, I did not include the exact query since it has a whole
of joins and many where clauses in it.
I did not want to make the post very hard to read, hence I simplified
it.
In the Stored proc I am using a String variable @SQLString
varchar(2000) to hold the entire select statement, and then executing
that SQL using EXEC (@SQLString).
Thus for debugging, I used Query Analyzer, and within the Analyzer I am
using the Select statement.
So in my test I do not use any stored proc.
so one select statement says:
Select * from ( Select Top 600 * from
( Select Top 2000 * from
( Select Top 2000 PermitNumber, HouseNumber, OnStreetName,
FromStreetName, ToStreetName, WorkStartDate as "WorkStart",
tbl_Permittee.Permittee_name as PermitteeName, PermitteeNumber,
PermitType_ID as "Type",
InspectionDistrict,
PermitStatus,
IssueDate
>From PermitMain inner join tbl_Permittee
on PermitMain.PermitteeNumber = tbl_Permittee.Permittee_Number
and (tbl_Permittee.Permittee_name_flag = 'd' or
tbl_Permittee.Permittee_name_flag = 'p')
where
(PermitType_ID like '01%' ) and worktypeid is null
and ((IssueDate between @IssueDateFrom and @IssueDateTo) or
(EmergIssueDate between @IssueDateFrom and @IssueDateTo))
and PermitteeNumber = @PermitteeNumber
and PermitMain.Boroughcode = @Boro
order by WorkStart DESC
) as T2 order by WorkStart ASC
) as T3
) as T4 order by WorkStart DESC
THIS ONE RUNS FAST and RETURNS RESULTS.
The Other Select statement:
Select * from ( Select Top 600 * from
( Select Top 2000 * from
( Select Top 2000 PermitNumber, HouseNumber, OnStreetName,
FromStreetName, ToStreetName, WorkStartDate as "WorkStart",
tbl_Permittee.Permittee_name as PermitteeName, PermitteeNumber,
PermitType_ID as "Type",
InspectionDistrict,
PermitStatus,
IssueDate
>From PermitMain inner join tbl_Permittee
on PermitMain.PermitteeNumber = tbl_Permittee.Permittee_Number
and (tbl_Permittee.Permittee_name_flag = 'd' or
tbl_Permittee.Permittee_name_flag = 'p')
where
(PermitType_ID like @WokStart ) and worktypeid is null
and ((IssueDate between @IssueDateFrom and @IssueDateTo) or
(EmergIssueDate between @IssueDateFrom and @IssueDateTo))
and PermitteeNumber = @PermitteeNumber
and PermitMain.Boroughcode = @Boro
order by WorkStart DESC
) as T2 order by WorkStart ASC
) as T3
) as T4 order by WorkStart DESC
TAKES FOREVER to COMPLETE.
However IF I INCREASE the PAGESIZE from 600 to 800, BOTH QUERIES RETURN
RESULTS EQUALLY FAST.
Thanks for your help
On Jan 25, 5:49 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> rbg (rbg....@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|