Posted by rbg on 01/29/07 14:02

Please find attached the complete query:

Declare @WorkType varchar(3), @PermitteeNumber varchar(5), @Boro
varchar(1)
Declare @IssueDateFrom datetime, @IssueDateTo datetime
Declare @SortExpression varchar(50), @SortOrder varchar(5)
Declare @PageNum int, @PageSize int

select @PageNum = 2, @PageSize = 1000
Declare @rowsToRetrieve int
Declare @SortOrderMirror varchar(4)

if @SortOrder = 'ASC'
SET @SortOrderMirror = 'DESC'
else
SET @SortOrderMirror = 'ASC'

SET @rowsToRetrieve = @PageNum * @PageSize

Declare @TotalRows int

SET @TotalRows = 1600

SET @PageSize = @TotalRows%@PageSize

select @WorkType = '01%', @PermitteeNumber = '00180', @Boro = 'M'
select @IssueDateFrom = '01/24/2001', @IssueDateTo = '01/24/2007'
select @SortExpression = 'WorkStart', @SortOrder = 'DESC'

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
=======================================================================
=
if I replace the above where clause from PermitType_ID like
@WorkType to PermitType_ID like '01%'
The results are returned within 30 secs, else it takes forever.

Any Help will be grately appreciated.

TIA..
=======================================================================
=
On Jan 26, 5:50 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> rbg (rbg....@gmail.com) writes:
> > 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 @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.Yes, but how does those variables makes into the dynamic SQL? You said
> that you were using EXEC(), and EXEC() does not permit you to pass
> parameters. Does your complete SQL string look something like:
>
> DECLARE @Workstart, ...
>
> SELECT @Workstart = '01%'
> ...
> SEKECT * ....
>
> Then you have preclsely the problem that I discussed in my previous
> post. The optimizer has no clue of value @workstart has, and will make
> a blind assumption.
>
> But you should not use EXEC(). Use sp_executesql instead. sp_executesql
> permits you to pass parameters, and in this case the optimizer will
> be able to use be parameter values for guidance.
>
> Seehttp://www.sommarskog.se/dynamic_sql.html#sp_executesqlfor
> more details on sp_excecutsql.
>
> If you want further help, please post your entire SQL batch, that is
> the one that builds the dynamic SQL. It's a bit frustrating having to
> guess what you are doing - and you get better answers that way.
>
> --
> 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]