October 24, 2002 at 9:50 am
I have on SP. If I run SP thru QA it takes 1 min 50 sec. But If I copy all statements from SP to QA and run it it takes only 5 Sec. Both using different execution plan. any Idea why?.
Thanks
Manesh
October 24, 2002 at 10:27 am
Is it possible that the table contents has changed since you last compiled the SP? If so (or in any case), try recompiling your SP.
sp_recompile 'your_proc_name'
October 24, 2002 at 10:33 am
Thanks. I already tried recompiling SP before but same result. there is on data or table changes. I restored that database to another server.
October 24, 2002 at 11:08 am
When calling your stored procedure, are you providing arguments that are used as variables in the query statements inside of your stored procedure? When extracting the contents of your stored procedure and executing them in QA, are you putting constants into your query statements, or are you still creating variables, assigning values to those variables, and then using those variables in your query statements?
Matthew Burr
October 24, 2002 at 11:18 am
When I am running the SP, I am passing arguments to sp. Thru QA I am assining that values to variables and leave the same T-sql on QA as is on SP.
October 24, 2002 at 11:32 am
Hmmm. All I can speculate is this:
I might speculate that when you compile the stored procedure it essentially has no information on the specific values that are being used in your queries; consequently, any query plan that it generates will be relatively generic. On the other hand, when you execute the queries in QA, they have some values to work with and can therefore generate plans that are more specific and will execute better.
I know that in 7.0, query plans could vary dramatically depending on whether or not you were using constants in your WHERE clauses or variables, simply because the optimizer lacked sufficient information to adequately optimize the query when variables were used in the WHERE clause; the optimizer would occasionally generate sub-optimal plans. I haven't researched to determine whether this problem persists in 2000, but I wouldn't be surprised if a variation of it exists, which would be - in this case - that stored procedures can generate sub-optimal plans since they have insufficient information about the specific values that will be placed in variables.
That, again, is pure speculation. One possible test to see if this might be the case is to go alter your stored procedure and place constant values in your queries, or assigning values to your variables in the stored procedure, only as a test, to see whether or not the procedure compiles with a better plan - one which more closely mirrors what you are seeing in QA. This - to me - would validate my speculation.
Matthew Burr
October 24, 2002 at 11:38 am
I did try with constant values assigned to variables inside the SP and run in 5 sec. Looks like I ave Same issue you reffering. Any solution?
October 24, 2002 at 12:12 pm
Typically, I've resolved this problem in the past using one of two solutions. Both solutions have their positives and negatives, and you'll find people that will agree with them or disagree with them, but in any case, they usually work.
One solution is to use query hints. What tends to cause this problem - in terms of the plan that the query optimizer is generating - is that the optimizer chooses either the wrong index or the wrong method for selecting data from your tables. For example, it might default to a solution of performing a table scan, or it might select data based on a clustered index and then filter that data using your variables, instead of doing an index seek based on a non-clustered index that is built on the columns you are searching against. One way to get around this is by using query hints and index hints to force the optimizer to use a particular index. The upside is that it can improve performance and solve your problem. The downside is that it "hobbles" the query optimizer, limiting its ability to search for effective query plans. However, if the optimizer is already using ineffective query plans, then limiting this ability is exactly what you are trying to do; so, in this case, its not that big of a negative.
The other option is to use dynamic sql in your stored procedure. Rather than directly execute your query, you would use the input variables to help build a string that contains your query, and then you would execute that string using the EXEC command. In general, I frown upon dynamic sql like this; its main problem is that every time you execute it, the query has to go through the optimization process, and that takes time. So, it would add a little overhead to each execution of your stored procedure, because your procedure would not be entirely cached. However, on the upside, it should resolve your problem of the optimizer choosing an ineffective plan. You have to weigh the gains against the losses: if the overhead required to parse and optimize the dynamic sql with every execution is substantially less than the overhead that results from the poor execution of the stored procedure that results from a poor plan, then it might be worth it to use the dynamic sql. In this case, the dynamic sql might take only 5 seconds total to parse, optimize and execute vs. nearly 2 minutes if you stick with your current procedure. Personally, that's a performance gain that I feel would justify using dynamic sql.
In any case, these are the two ways I've resolved this in the past. You might consider these options as solutions to the problem. Others on this site might also have other suggestions.
HTH,
Matthew Burr
Edited by - mdburr on 10/24/2002 12:14:42 PM
Edited by - mdburr on 10/24/2002 12:16:08 PM
October 24, 2002 at 12:21 pm
Thanks Matthew Burr. I tried the Dynamic SQL approch and it took 17 sce. to run the SP.
Any one has any other idea?.
Manesh
October 25, 2002 at 10:32 am
Did you look at the option of using query/index hints to force the plan to use a better index for the query?
Matthew Burr
October 25, 2002 at 11:21 am
If you use sp_executesql with named parameters the query optimizer is more likely to re-use the query plan created the first time the stored procedure was run.
For Example:
sp_executesql N'select * from sysobjects where name = @name', N'@name sysname', @name = 'syscolumns'
The original query plan is only re-used when the only thing that changes in your dynamic SQL is the parameter.
Sincerely,
Mark Cudmore, MCP
Sincerely,
Mark Cudmore, MCDBA
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply