Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Query Execution Plans & Recompilation Expand / Collapse
Author
Message
Posted Wednesday, October 15, 2008 10:30 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 1:09 PM
Points: 478, Visits: 1,417
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?
Post #586713
Posted Wednesday, October 15, 2008 11:18 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 3:12 AM
Points: 2,112, Visits: 5,480
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/
Post #586721
Posted Thursday, October 16, 2008 1:40 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:48 AM
Points: 39,980, Visits: 36,347
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 2008, MVP
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

Post #586769
Posted Thursday, October 16, 2008 12:01 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 1:09 PM
Points: 478, Visits: 1,417
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?
Post #587214
Posted Thursday, October 16, 2008 12:12 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:48 AM
Points: 39,980, Visits: 36,347
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 2008, MVP
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

Post #587220
Posted Thursday, October 16, 2008 12:29 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 1:09 PM
Points: 478, Visits: 1,417
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))

Post #587228
Posted Thursday, October 16, 2008 12:39 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 1:09 PM
Points: 478, Visits: 1,417
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?
Post #587237
Posted Thursday, October 16, 2008 12:41 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:48 AM
Points: 39,980, Visits: 36,347
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 2008, MVP
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

Post #587240
Posted Thursday, October 16, 2008 12:44 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:48 AM
Points: 39,980, Visits: 36,347
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 2008, MVP
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

Post #587248
Posted Thursday, October 16, 2008 1:28 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 1:09 PM
Points: 478, Visits: 1,417
Thanks! That clarified a lot.
Post #587275
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse