Posted by rbg on 01/29/07 16:36

Erland,

You were very much right regarding using sp_executesql stored proc for
executing dynamic queries.

Once I started using this:
Declare @Params nvarchar(200)
SET @Params = '@WorkTypeSQL nvarchar(3), @PermitteeNumberSQL
nvarchar(5), @BoroSQL nvarchar(1)
exec sp_executesql @Params, @WorkType, @PermitteeNumber, @Boro

The performance improved and solved my original problem.

I have one more question though.
When I used Temp tables instead of derived tables, my performance
improved significantly. The query which takes 30 seconds using the
derived tables, takes only 11 seconds when using Temp tables.

So is it better to choose the Temp table solution over the derived
tables solution?

TIA

On Jan 29, 9:02 am, "rbg" <rbg....@gmail.com> wrote:
> 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_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 @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]