Query as StoredProc taking 60x as long as query executed directly

When I run a sequence of queries directly (in SQL Query Analyzer against Sql Server 2k), it runs in about 40s. When I take that EXACT same sequence of queries and slap them into a StoredProc, it takes 40min. The statement at the end of the sequence is responsible for the long execution, and is an insert into a temp table. I tried googling for other users' experiences with this problem, and found something similar here (http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21750204.html). However, the suggestions have not helped with this query.

The execution plans are different when running directly vs. as a StoredProc. When direct, the execution plan shows a hash join and parallelism, the StoredProc uses a nested loop join. I have added a HASH join hint to the stored proc, and the performance improves quite a bit, but is still 10x worse than running the sequence directly. I have tried many things, including setting maxdop, clearing the proc cache, executing with recompile, using a table variable instead of a temp table (actually gave worse performance since the temp table benefits from a clustered index for subsequent operations), breaking the insert into multiple exclusive steps -- none of these have improved the performance of the storedproc.

Here is the specific statement that is taking "forever" when executed within a storedproc. Note that the VWMarketPriceHistory item is a view on a set of two partitioned tables. Even if I separate the insert into two inserts on the paritioned tables individually, the performance is the same as using this view.

Any ideas?

create table #Price (
      MarketDt smalldatetime not null,
      PkgProgId int not null,
      Coupon float not null,
      Price float not null,
      primary key clustered (MarketDt, PkgProgId, Coupon)
)



insert #Price
      select
            s.MarketDt,
            s.PkgProgId,
            s.Coupon,
            Price = max(s.MarketPrice)
      from Position p
      join VWMarketPriceHistory s
            on p.PkgProgId = s.PkgProgId
            and p.Coupon = s.Coupon
      where datediff(m, s.MarketDt, s.SettlementDt) between 0 and 1
        and s.MarketDt <= '3/17/06'
      group by
            s.MarketDt,
            s.PkgProgId,
            s.Coupon
Start Free Trial