October 15, 2008 at 10:30 pm
Do IF-THEN statements inside stored procedures cause the query execution plans for stored procedures to be recompiled?
I have heard two different views on this:
1. Yes. There is only one query plan cached per stored procedure. SQL Server will recompile the query plan for the stored procedure when the code goes down a different path.
2. No. Each part of the IF-THEN clause will have a separate plan stored when it is run the first time. Subsequent runs of the stored procedure will use one of the cached plans.
Does anyone have any input on this?
October 15, 2008 at 11:18 pm
ggraber (10/15/2008)
Do IF-THEN statements inside stored procedures cause the query execution plans for stored procedures to be recompiled?I have heard two different views on this:
1. Yes. There is only one query plan cached per stored procedure. SQL Server will recompile the query plan for the stored procedure when the code goes down a different path.
2. No. Each part of the IF-THEN clause will have a separate plan stored when it is run the first time. Subsequent runs of the stored procedure will use one of the cached plans.
Does anyone have any input on this?
Here is the version that I know? - A query plan is an optimal way to reach the data in the tables (for both – modifying and selecting it). When the server creates a query plan it creates it only for statements that have anything to do with the data from the database and for all of those statements. Other statements like control statements (if, else, etc’) or value setting statement (such as SET @Var=10) have no need for a query plan, so there isn’t a query plan for those statements. So a query plan will be created and it will include all the SQL Statements even if each time that the procedure will run, only some of the statements will be executed because of control statements such as IF. When the procedure runs it will use the needed part of the procedure’s query plan.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 16, 2008 at 1:40 am
No, the if-else will not cause a recompile.
When SQL compiles the procedure the first time, it will compile and optimise all of the queries in the procedure. Even ones in branches of if/else that will not be executed on that run. It does the optimisation based on the parameter values for that first run.
This can, in some cases, lead to poor query plans for some branches of the if/else that will hinder performance for some executions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2008 at 12:01 pm
GilaMonster (10/16/2008)
This can, in some cases, lead to poor query plans for some branches of the if/else that will hinder performance for some executions.
Can you please elaborate on this? When would this lead to poor query plans and poor performance?
October 16, 2008 at 12:12 pm
Sure. This is the classic example
CREATE PROCEDURE AllOrSearch (
@SomeCode VARCHAR(30) = NULL
)
AS
IF @ID is null
SELECT * FROM SomeTable
ELSE
SELECT * FROM SomeTable WHERE SomeColumn = @SomeCode
If on the first execute, the parameter is null, then SQL will optimise both branches of the proc as if they would be run with the parameter of NULL. The second select (which won't actually be run with a parameter value of NULL) gets optimised as well and when the optimiser estimates rows affected, it will estimate 0 (Because no rows will ever match = NULL)
Now, let's say the optimal exec plan for a very few rows is an index seek with a bookmark lookup. That's what gets cached for the second query
Later, someone runs that with a value for @SomeCode that will return 1/4 of the table. Because the plan is cached, it will be reused, even though it's not an optimal plan for 1/4 of the table
Now this is a simplistic example, but the general principle is the same for more complex procs.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2008 at 12:29 pm
GilaMonster (10/16/2008)
Sure. This is the classic example
CREATE PROCEDURE AllOrSearch (@SomeCode VARCHAR(30) = NULL
)
AS
IF @ID is null
SELECT * FROM SomeTable
ELSE
SELECT * FROM SomeTable WHERE SomeColumn = @SomeCode
If on the first execute, the parameter is null, then SQL will optimise both branches of the proc as if they would be run with the parameter of NULL. The second select (which won't actually be run with a parameter value of NULL) gets optimised as well and when the optimiser estimates rows affected, it will estimate 0 (Because no rows will ever match = NULL)
Now, let's say the optimal exec plan for a very few rows is an index seek with a bookmark lookup. That's what gets cached for the second query
Later, someone runs that with a value for @SomeCode that will return 1/4 of the table. Because the plan is cached, it will be reused, even though it's not an optimal plan for 1/4 of the table
Now this is a simplistic example, but the general principle is the same for more complex procs.
OK. So in the case above would it be better do something like this?
CREATE PROCEDURE AllOrSearch
(
@SomeCode VARCHAR(30) = NULL
)
AS
SELECT *
FROM SomeTable
WHERE ((@SomeCode IS NULL) OR (SomeColumn = @SomeCode))
October 16, 2008 at 12:39 pm
I'm still a little confused about how the query plan works.
Here's what I thought:
There is one query plan per stored procedure. When the query is run an execution context is created, and it is cached. The second time SQL Server checks to see if it can use the existing execution context, meaning you are going down the same path of the if statement. If it can not find the execution context, meaning you are going down a different path of the if statement, it will create a new execution context.
According to what you're saying, the query plan itself is not recompiled, but it will not be able to use the existing execution context. Is this correct?
October 16, 2008 at 12:41 pm
Not really. The revised version (especially with more than 1 parameter) is likely to table scan all the time. That query has no single optimal plan
The usual recommendations (and often things are a lot more complex than 1 if statement and 1 parameter) are:
Call sub procedures from the if/else statement. Each proc will have it's own optimal plan
Use RECOMPILE, on a procedure or statement level
use dynamic SQL (not a good idea unless things are really, really complex)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2008 at 12:44 pm
As far as I'm aware, execution contexts are cached per user and per procedure, not for each statement in the procedure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2008 at 1:28 pm
Thanks! That clarified a lot.
February 28, 2011 at 10:28 am
Hai...
if i execute a procedure as my code starts as follows...
PROCEDURE PROC_SY_ADD_INSERT_FUNCTION
GO
IF EXISTS (SELECT * FROM sysobjects
WHERE id = OBJECT_ID('PROC_INS_FUNCTION')
AND OBJECTPROPERTY(id, 'IsProcedure') = 1)
DROP PROCEDURE PROC_INS_FUNCTION
GO
CREATE PROCEDURE PROC_INS_FUNCTION
()
...
...
...
after executing(creating) above procedure.. ... will it clear existing cache?....will it create new execution plan when next time this sp called?... any help on this?
Thanks in advance...
February 28, 2011 at 10:48 am
Please post new questions in a new thread. Thanks
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply