January 18, 2005 at 12:15 am
In one of the books I read recently the following topic created a lot of confusions in my mind
Please see...
Are You Trying to Optimize a Stored Procedure?
CREATE PROCEDURE usp_example @search_name char(50) AS
SELECT au_lname
FROM authors
WHERE au_lname like @search_name + `%'
Index: CREATE INDEX au_lname_idx ON authors(au_lname)
Consider what happens when a user executes the query with `B' as a parameter:
EXEC usp_example `B'
If the table contains numerous records that have a last name beginning with the letter B, the optimizer is likely to perform a table scan.
Now consider what happens when a user executes the query with `BREAULT' as a parameter:
EXEC usp_example `BREAULT'
If the table contains only a few records that have a last name equal to BREAULT, the optimizer is likely to use an index.
As you can see, the optimizer chooses different query plans based on the value of the parameter passed into the stored procedure. The problem arises when the first query plan is stored in memory. By default, different users receive the same query plan based on the first execution of the stored procedure. If the first user of the stored procedure passed `B' as a parameter, the query plan would be very inefficient for other users who specify `BREAULT' as a parameter.
To avoid this problem, use the WITH RECOMPILE option when you execute the procedure. This forces the procedure to regenerate its query plan, as in the following:
EXEC ... WITH RECOMPILE
The revised query plan is available only for the current execution of the procedure. Subsequent executions of the procedure without the recompile option revert back to the old query plan.
My Questions:
1. Most of the time Stored Procedures have parameters and WHERE clauses use the parameters. So there wont be any performance gains in using a stored procedure, since Query optimizer will not be able to reuse the Execition Plan. Is that correct?
2.When User 1 executes a Stored Procedure then query plan is there in the Procedure Cache. If User 2 executes the same Stored Procedure, will the Query Optimizer use the Execution Plan which is already there in the Procedure Cache? Procedure Cache memory allocation is Userwise or Procedurewise?
Please help...
January 18, 2005 at 5:07 am
First of all the query optimiser will use an index if it is deemed to be highly selective. If a query is going to return (just as an example) 70% of the records then it will probably ignore the index.
The particular example you give is using a string condition where the string can be of various lengths. If you use a numeric condition then the query plan is optimised for a fixed length value and therefore reuse is likely to be efficient.
If you have a complicated query with lots of OR and LIKE statements then reusing the query plan is probably not going to be efficient.
Don't forget that stored procedures offer advantages other than performance, namely security and if you are using ADO Command objects, some protection against SQL Injection Attacks.
January 18, 2005 at 5:12 am
Thanks David.
January 31, 2005 at 6:12 am
The first sentence in that article should have said:
A query plan that is generated to be optimal for a given parameter value, may not be optimal for a different parameter value. Unfortunately the optimiser may continue to use the same query plan for all parameter values, until table data changes cause a recompile.
(Is that any clearer?)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply