Cache plan different using sp_prepare and sp_executesql.

I have a 3rd party application that uses sp_prepare and sp_execute for data retreival. The following statement takes around 40 seconds to run:

declare @.P1 int

exec sp_prepare @.P1 output, N'@.P1 bigint,@.P2 bigint,@.P3 bigint,@.P4 bigint,@.P5 bigint,@.P6 bigint,@.P7 bigint,@.P8 bigint', N'SELECT SHAPE ,S_.eminx,S_.eminy,S_.emaxx,S_.emaxy ,SHAPE.fid F_fid,SHAPE.numofpts F_numofpts,SHAPE.entity F_entity,SHAPE.points F_points FROM (SELECT DISTINCT sp_fid,eminx,eminy,emaxx,emaxy FROM SDE.SDE.s162 SP_ WHERE SP_.gx >= @.P1 AND SP_.gx <= @.P2 AND SP_.gy >= @.P3 AND SP_.gy <= @.P4 AND SP_.eminx <= @.P5 AND SP_.eminy <= @.P6 AND SP_.emaxx >= @.P7 AND SP_.emaxy >= @.P8 ) S_ ,SDE.SDE.ENTORDERLINESEGMENT, SDE.SDE.f162 SHAPE WHERE S_.sp_fid = SHAPE.fid AND SDE.SDE.ENTORDERLINESEGMENT.SHAPE = S_.sp_fid AND (( ORDERID in (16320, 16825) ))', 1 select @.P1

exec sp_execute 1, 166, 169, 219, 224, 90269119, 119480870, 88777840, 117193071

The query plan created by sp_prepare is used during the sp_execute but it's very slow compared to running this query ad hoc or with sp_executesql. If I clear the proc cache before running the sp_execute this runs in less than 1 second. The tables used are pretty large (8 - 15 million rows) but are indexed correctly and I've updated the statistics and rebuilt the indexes but neither improves the performance.

Does anyone know why using the sp_prepare statement causes a poor query plan?

Thanks.

Doug Matney

Hi

I have come across an interesting article regarding sp_prepare. Hope , it'll be useful for you too

http://www.slxdeveloper.com/page.aspx?action=viewarticle&articleid=51

NB.

Business Scorecard Manager

hello -
We are looking for a 3rd party tool for dashboarding, gauges and Key
Performance Indicators (KPIs).
We are already using SSRS 2005 for reporting purposes.
Is MS's Business Scorecard Manager
(http://office.microsoft.com/en-us/FX012225041033.aspx) anyway related
to SSRS 2005?
Is Business Scorecard Manager another BI module in SQL Server 2005?
any inputs are appreciated.
thanks
- jasthiWe just had a demo for Business Scorecard Manager. It is a separate
application that runs on Sharepoint (Services or Portal). It can pull data
from any datasource directly or through cubes from multiple datasources. It
can incorporate reports from Reporting Services (2000 or 2005).
I'm totally new at Scorecard Manager, but this is my understanding so far.
So anyone can correct me or add more...
"siva.jasthi@.gmail.com" wrote:
> hello -
> We are looking for a 3rd party tool for dashboarding, gauges and Key
> Performance Indicators (KPIs).
> We are already using SSRS 2005 for reporting purposes.
> Is MS's Business Scorecard Manager
> (http://office.microsoft.com/en-us/FX012225041033.aspx) anyway related
> to SSRS 2005?
> Is Business Scorecard Manager another BI module in SQL Server 2005?
> any inputs are appreciated.
> thanks
> - jasthi
>