Posted by Erland Sommarskog on 01/26/07 22:50

rbg (rbg.net@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.

See http://www.sommarskog.se/dynamic_sql.html#sp_executesql for
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, 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

[Back to original message]