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/