SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Execeution Plan affecting Performance
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

colin.graham@stylo.co.uk
Starting Member

United Kingdom
8 Posts

Posted - 05/25/2006 :  04:06:24  Show Profile  Reply with Quote
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.

Can anyone help me to solve this problem?

Regards
Colin

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 05/25/2006 :  04:13:10  Show Profile  Send jen a Yahoo! Message  Reply with Quote
index on taxtable and prodint?

--------------------
keeping it simple...
Go to Top of Page

colin.graham@stylo.co.uk
Starting Member

United Kingdom
8 Posts

Posted - 05/25/2006 :  04:17:21  Show Profile  Reply with Quote
Yes - indexes were the first thing that I looked at and tried combinations of.

Go to Top of Page

mr_mist
Grunnio

United Kingdom
1870 Posts

Posted - 05/25/2006 :  04:19:30  Show Profile  Visit mr_mist's Homepage  Reply with Quote
Are they the same data type?

-------
Moo. :)
Go to Top of Page

colin.graham@stylo.co.uk
Starting Member

United Kingdom
8 Posts

Posted - 05/25/2006 :  04:35:11  Show Profile  Reply with Quote
Yes, the same data type and lengths
Go to Top of Page

mr_mist
Grunnio

United Kingdom
1870 Posts

Posted - 05/25/2006 :  04:40:49  Show Profile  Visit mr_mist's Homepage  Reply with Quote
I can only ponder that

a) The second time through it's working on a much larger data set for the second week or
b) There's a missing join condition that is causing it to work on a cartesian product data set.

-------
Moo. :)
Go to Top of Page

jen
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 05/25/2006 :  05:33:42  Show Profile  Send jen a Yahoo! Message  Reply with Quote
try dbcc freeprocache or sp_recompile and see if it produces a new execution plan

--------------------
keeping it simple...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000