March 24, 2009 at 12:15 pm
Good Afternoon Everyone,
We have an interesting situation, and I know I've read an article somewhere about this, but I cannot for the life of me remember where. There is a stored procedure that we run daily (sometimes multiple times) which figures out for certain companies, have we received price information from them for their products. Result set might look something like this:
Company Yesterday's Count Missing Count
---------------------------------------------
Company 1 500 0
Company 2 24 24
Company 3 2000 0
Company 4 322 5
We have about a dozen or so Companies that we receive information for, and the interesting thing here is that if more that 3 companies have missing prices, the query will run for about 15 minutes.
Basically, my question is, there are 2 different ways to calculate how the query is going to execute, first, what are the different calculation methods called, and second, is there a way to "force" the query to be executed one way or the other?
-Mark
March 25, 2009 at 1:00 am
If your Stored Procedure is executing SQL Statements based values passed then it will generate two different execution plans. The query that is running slow your execution must not be efficient because of indexes or other operations in stored procedure.
There is a way to force a query plan; although it is not recommended practice. If you can post the execution plan of your stored procedure for both parts the good part and the poor performance one someone here can help you out :).
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 25, 2009 at 6:42 am
It does sound like you might be seeing the results of different paths through the execution plan working better for one data set than another. Check the execution plan for the slow and the fast execution. They should be the same. In a test environment (not production), try running DBCC FREEPROCCACHE and then rerun the slow query. See if the execution plan changes. Free the cache again and see if the faster query changes the plan. If they are changing plan a lot, you should check the statistics on the index. Make sure they're up to date. If they are, you might be hitting a situation where you need to have the procedure recompile with each execution. Try adding a query hint, WITH RECOMPILE (again, in the test environment) and see if that resolves the issue. Possibly you can explore using the OPTIMIZE FOR hint, but I don't think it will help. I'd do all this before I started looking at forcing plans. That's a last resort. Also, you might need to defrag the index, just for performance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 25, 2009 at 6:52 am
Can you post the code for the procedure please?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply