Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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  
 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.06 seconds. Powered By: Snitz Forums 2000