Posted by rbg on 01/26/07 13:59

PLEASE NOTE the variable was not WokStart but WORKTYPE.

So the query that takes very long looks like this:

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 @WorkType ) 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
===================================================================
On Jan 26, 8:49 am, "rbg" <rbg....@gmail.com> wrote:
> 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_Permitteeon 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_Permitteeon 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

[Back to original message]