I hope someone out there can help me. I have a stored procedure which executes a sub-procedure twice (it retrieves stock data for 2 consecutive weeks). The sub-procedure has the following piece of seemingly innocuous code:
UPDATE t SET taxcode = pt.taxcode FROM prodtaxes pt, #tmp_week_actuals t WHERE t.taxtable = pt.taxtable AND t.prodint = pt.varint
When I run the top level procedure the first time the sub-procedure executes this statement in about 14 minutes and generates about 450,000 physical I/Os; the second time the statement executes it takes about 2.5 hours and generates about 4,500,000 physical I/Os - a ten fold increase. I've tried changing the #tmp table to a permanent one, creating indexes on both tables, re-scheduling the procedure to avoid conflicts with other jobs and run it on a test server with no other jobs running - the outcome is always the same.
It seems to me that the Query Optimiser is generating a completely different execution plan the second time around but I don't know why and don't how how to stop it.