Query Execution Plans & Recompilation

  • 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?

  • 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/

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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?

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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))

  • 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?

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks! That clarified a lot.

  • 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...

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply