T/F: Using If Statements in a Procedure Produces Multiple Plans

T/F: Using IF statements in a procedure produces multiple plans

We're all correct :-)

• A "query plan" has at most 2 entries in cache: one serial and one parallel

• Each user has their own "Execution context" that runs the plan

• Plans differ if objects are not qualified

So, what you may think is a plan is not because tables are not qualified with schema (which is the same in SQL Server 2000 , 2005 and 2008)

From MSDN/BOL "Execution Plan Caching and Reuse"

Edit:

"Obtaining Statement-Level Query Plans" from MS blog

Is it good practice to use one single stored procedure that accepts a variable number of parameters

here is a very comprehensive article on this topic:

Dynamic Search Conditions in T-SQL by Erland Sommarskog

it covers all the issues and methods of trying to write queries with multiple optional search conditions

here is the table of contents:

   Introduction
      The Case Study: Searching Orders
      The Northgale Database
   Dynamic SQL
      Introduction
      Using sp_executesql
      Using the CLR
      Using EXEC()
      When Caching Is Not Really What You Want
   Static SQL
      Introduction
      x = @x OR @x IS NULL
      Using IF statements
      Umachandar's Bag of Tricks
      Using Temp Tables
      x = @x AND @x IS NOT NULL
      Handling Complex Conditions
   Hybrid Solutions – Using both Static and Dynamic SQL
      Using Views
      Using Inline Table Functions
   Conclusion
   Feedback and Acknowledgements
   Revision History

Why query takes more time to execute when I use if else statement in stored procedure?

There's two main reasons - like and execution plan.

like '%something' is the slowest possible thing you can filter by - it means going row by row, reading the whole data in the column and doing a comparison. It can't use any index seeks, only scans.

Second, you only get one execution plan, based on the first way the procedure is called. This pretty much guarantees that for any other input data, the performance will suffer. In your second example, while the plan is again sub-optimal and dependent on the initial inputs, it doesn't completely ignore all the possible filters - it just optimizes based on statistics.

Dynamic filters aren't something nobody tried to solve before. Learn from the best: http://www.sommarskog.se/dyn-search.html

SQL Stored procedure if (@variable = 0) doesn't work

You could rewrite it as one single select of 4-part UNION ALL.

I also notice that your conditions are not comprehensive, not sure if that is intentional.

In the 2nd branch of @PoolID = 0 , the tests are specifically @Bevoegdheid < 3 and @Bevoegdheid >= 3 (instead of ELSE ) which will do nothing if @Bevoegdheid IS NULL

The optimizer will only run the branch that fits the criteria marked with <<<

ALTER PROCEDURE [dbo].[Pool_select]
@PartnerCode nvarchar(8),
@GebrID int,
@PoolID int,
@Bevoegdheid int
-- WITH RECOMPILE -- << may need this

AS

    SELECT
    p.*,
    pr.poolrecht
    FROM racpPool p, racpPoolrecht pr
    WHERE
    p.poolid = pr.poolid and
    p.PartnerCode = @PartnerCode and
    pr.poolrecht > 0 and
    p.actief = 1 and
    pr.GebrID = @GebrID
    AND @PoolID = 0 and @Bevoegdheid < 3   --- <<<
UNION ALL
    SELECT *, NULL
    FROM racpPool p
    WHERE standaardpool = 1
    and partnercode = @PartnerCode
    AND @PoolID = 0 and IsNull(@Bevoegdheid,4) >= 3 --- <<<
UNION ALL
    SELECT
    p.*,
    pr.poolrecht
    FROM racpPool p, racpPoolrecht pr
    WHERE 
    p.poolid = pr.poolid and
    p.PartnerCode = @PartnerCode and
    pr.poolrecht > 0 and
    p.actief = 1 and
    pr.GebrID = @GebrID and 
    p.PoolID = @PoolID
    AND Isnull(@PoolID,-1) <> 0 AND @Bevoegdheid < 3   --- <<<
UNION ALL
    SELECT *, NULL
    FROM racpPool p
    WHERE PoolID = @PoolID
    AND Isnull(@PoolID,-1) <> 0 AND @Bevoegdheid >= 3  --- <<<

Execution Plans for Databases

• To be exact for SQL Server:

You have at most two plans in cache (one parallel, one non-parallel). Then the plan is used with an Execution Context per user. More info in my answer here

• JOIN order is irrelevant in almost all cases

SQL is declarative. This means you tell the engine what you want and the optimiser works out the best plan (within reason, it might take 2 weeks to work out the best one). This is why you can rewrite queries many different ways to get the same answer.

Like any rules about RDBMS, there are exceptions. For complex queries, the optimiser will not go through every permutation so the JOIN order can matter: it depends when the optimiser decides it's had enough...

Use one CTE many times

A CTE is basically a disposable view. It only persists for a single statement, and then automatically disappears.

Your options include:

• Redefine the CTE a second time. This is as simple as copy-paste from WITH... through the end of the definition to before your SET.

• Put your results into a #temp table or a @table variable

• Materialize the results into a real table and reference that

• Alter slightly to just SELECT COUNT from your CTE:

.

SELECT @total = COUNT(*)
FROM Players p 
INNER JOIN Teams t 
    ON p.IdTeam=t.Id 
INNER JOIN Leagues l 
    ON l.Id=t.IdLeague
WHERE l.Id=@idleague

Dynamic SQL and stored procedure optimization

If you have multiple nested IF blocks then SQL Server will be able to store execution plans.
I'm assuming that the IFs are straightforward, eg. IF @Parameter1 IS NOT NULL

SchmitzIT's answer is correct that SQL Server can also store execution paths for Dynamic SQL. However this is only true if the sql is properly built and executed.

By properly built, I mean explicitly declaring the parameters and passing them to sp_executesql. For example

declare @Param1 nvarchar(255) = 'foo'
        ,@Param2 nvarchar(255) = 'bar'
        ,@sqlcommand nvarchar(max)
        ,@paramList nvarchar(max)

set @paramList = '@Param1 nvarchar(255), @Param2 nvarchar(255)'
set @sqlcommand = N'Select Something from Table where Field1 = @Param1 AND Field2 = @Param2'

exec sp_executesql @statement = @sqlcommand
                  ,@params = @paramList
                  ,@Param1 = @Param1
                  ,@Param2 = @Param2

As you can see the sqlcommand text does not hardcode the paramer values to use. They are passed separately in the exec sp_executesql

If you write bad old dynamic sqL

set @sqlcommand = N'Select Something from Table where Field1 = ' + @Param1  + ' AND Field2 = ' + @Param2

exec sp_executesql @sqlcommand

then SQL Server won't be able to store execution plans

Oracle EXECUTE IMMEDIATE changes explain plan of query

It turns out that this is a known bug in Oracle 9i. Below is the text from a bug report.

Execute Immediate Gives Bad Query Plan [ID 398605.1]

Modified 09-NOV-2006     Type PROBLEM     Status MODERATED

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.

Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.6
This problem can occur on any platform.

Symptoms
When a procedure is run through execute immediate the plan produced is a different than when procedure is run directly.

Cause
The cause of this problem has been identified and verified in an unpublished Bug 2906307.
It is caused by the fact that SQL statements issued from PLSQL at a recursive
depth greater than 1 may get different execution plans to those issued directly from SQL.
There are multiple optimizer features affected by this bug (for example _unnest_subquery,_pred_move_around=true)
HINTS related to the features may also be ignored.

This bug covers the same basic issue as Bug 2871645 Complex view merging does not occur for
recursive SQL > depth 1 but for features other than complex view merging.

Bug 2906307 is closed as a duplicate of Bug 3182582 SQL STATEMENT RUN SLOWER IN DBMS_JOB THAN IN SQL*PLUS.
It is fixed in 10.2

Solution
For insert statements use hint BYPASS_RECURSIVE_CHECK:
INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO table

References
BUG:2871645 - COMPLEX VIEW MERGING DOES NOT OCCUR FOR RECURSIVE SQL > DEPTH 1
BUG:3182582 - SQL STATEMENT RUN SLOWER IN DBMS_JOB THAN IN SQL*PLUS

---

Related Topics