Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
colin.graham@stylo.co.uk
Starting Member
8 Posts |
 Posted - 2006-05-25 : 04:06:24
|
| 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.varintWhen 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.Can anyone help me to solve this problem?RegardsColin |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-05-25 : 04:13:10
|
| index on taxtable and prodint?--------------------keeping it simple... |
 |
|
|
colin.graham@stylo.co.uk
Starting Member
8 Posts |
Posted - 2006-05-25 : 04:17:21
|
| Yes - indexes were the first thing that I looked at and tried combinations of. |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-05-25 : 04:19:30
|
| Are they the same data type?-------Moo. :) |
|
|
|
colin.graham@stylo.co.uk
Starting Member
8 Posts |
Posted - 2006-05-25 : 04:35:11
|
| Yes, the same data type and lengths |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-05-25 : 04:40:49
|
| I can only ponder that a) The second time through it's working on a much larger data set for the second week orb) There's a missing join condition that is causing it to work on a cartesian product data set.-------Moo. :) |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-05-25 : 05:33:42
|
| try dbcc freeprocache or sp_recompile and see if it produces a new execution plan--------------------keeping it simple... |
|
|
|
|
|
|
Execeution Plan affecting Performance