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.