0

This question already has an answer here:

I found a stored procedure in our sql server 2016 db

Create PROCEDURE XXX
   @complicatedQuery bit
AS
BEGIN
   SELECT some fields FROM
     Table A join Table B on something
     JOIN Giant Table C on @complicatedQuery=1 and SomeOtherCondition
END

Will we get bit better performance if I do this:

Create PROCEDURE XXX
       @complicatedQuery bit
    AS
    BEGIN
       IF @complicatedQuery =0 then
       SELECT some fields FROM
         Table A join Table B on something
       ELSE
       SELECT some fields FROM
         Table A join Table B on something
         JOIN Giant Table C on complicatedQuery=1 and SomeOtherCondition

    END

I think in both cases,sql search will create a plan that goes through both branches. But will second one be better or in reality there is no performance difference (as the first sp has @complicatedQuery in the table join condition anyway)?

I read another post which says that it is best to create sub stored procedures to cover different scenarios.

Is there a way to write some tests to prove it?

Many Thanks

marked as duplicate by Josh Darnell, Erik Darling sql-server Jul 10 at 18:28

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • When you use an IF/ELSE Sql server will create plans for both branches regardless of your parameter. You can see this when you look at the query plan. The only way around this is to create each branch as a separate store proc and have a dispatcher store proc call the appropriate one. – Michael B Jul 10 at 17:00
  • Shouldn't the JOIN Giant Table C on @complicatedQuery=1 and SomeOtherCondition be a LEFT JOIN ? Otherwise you wont have a resultset if @complicatedQuery=0 – Randi Vertongen Jul 10 at 17:07
  • The “another post” is right. One procedure for one task. Have a “complicated query” procedure and a “plain query” procedure and gave your app call the right one instead of passing execution parameters. If you can’t change the app, have your parent procedure simply do an IF... EXEC ... ELSE EXEC. Simpler queries, less parameter and plan cache headaches, clear. – SQLRaptor Jul 10 at 18:24
3

Testing

I changed the inner join with TABLE C to a LEFT JOIN so our data does not get filtered out by the INNER JOIN condition (JOIN DBO.TABLEC C ON @complicatedQuery=1 when @complicatedQuery = 0 ) as an INNER JOIN ON 1=0 AND SomeOtherCondition returns zero rows.

Unless that is, what you want, but then calling the original or conditional procedure with @complicatedQuery = 0 will return different results. (Among other remarks)

Test data at the bottom

Procedures

Original procedure example with left join

Create PROCEDURE dbo.ProcOriginal
   @complicatedQuery bit
AS
BEGIN
   SELECT A.VAL FROM
     DBO.TABLEA A 
     JOIN  DBO.TABLEB B on A.B_ID =B.ID
     LEFT JOIN  DBO.TABLEC C ON @complicatedQuery=1 and C.A_ID = A.ID
END

Conditional procedure example, also with left join to keep it logically the same

Create PROCEDURE dbo.ProcConditional
       @complicatedQuery bit
    AS
    BEGIN
       IF @complicatedQuery =0 
        BEGIN
        SELECT A.VAL FROM
        DBO.TABLEA A 
        JOIN  DBO.TABLEB B on A.B_ID =B.ID;
        END
        IF @complicatedQuery =1 
        BEGIN
        SELECT A.VAL FROM
            DBO.TABLEA A 
            JOIN  DBO.TABLEB B on A.B_ID =B.ID
            LEFT JOIN  DBO.TABLEC C ON C.A_ID = A.ID;
    END
END

Running them with parameter = 0

SET STATISTICS IO, TIME ON;
EXEC dbo.ProcConditional 0 
EXEC dbo.ProcOriginal 0 

The estimated execution plan's differ the most, the actual query plan's less.

enter image description here

We can see why the estimates are different, based on a part of the estimated execution plan from ProcOriginal :

enter image description here

While in reality, 0 rows are returned.

enter image description here

We can improve this by adding this index:

CREATE INDEX IX_A_ID
ON DBO.TABLEC(A_ID);

Causing no table access

enter image description here

But, the estimated rows are now at 2.5K, even though the variable eliminates the table access.

This can still be a potential problem due to increasingly higher memory grants if the join between A and B returns more results.

enter image description here

The ProcConditional actual execution plan seems ok.

enter image description here

A NC Index on B_ID on TableA could improve both queries.


Running them with parameter = 1

EXEC dbo.ProcConditional 1;
EXEC dbo.ProcOriginal 1;

The only difference here is the extra filter operator with a startup expression predicate as seen earlier @complicatedQuery=1 or 1=1 .

enter image description here

Filter enter image description here Added due to

ON @complicatedQuery=1 and C.A_ID = A.ID

All this due to SQL Server not knowing what @complicatedQuery will be at runtime.

You could remove this filter operator by adding an OPTION(RECOMPILE) to the procedure, making a new plan to be created on each execution.


Which is better

I do think that the conditional method would be the better choice between the two.


Another method

Using Dynamic SQL to build the strings depending on @complicatedQuery and then executing them directly.

CREATE PROCEDURE dbo.ProcDynamic
   @complicatedQuery bit = 0
AS 
BEGIN
DECLARE @CMD nvarchar(1000)

    SET @CMD =
    'SELECT A.VAL FROM
    DBO.TABLEA A 
    JOIN  DBO.TABLEB B on A.B_ID =B.ID'

    IF @complicatedQuery =1 
    BEGIN
    SET @CMD += ' LEFT JOIN  DBO.TABLEC C ON C.A_ID = A.ID;'
    END
    ELSE 
    BEGIN
        SET @CMD+= ';'
    END
EXEC SP_EXECUTESQL @CMD
END

When working with dynamic sql, you do have several threats and considerations, but without working with parameters added to the dynamic string you should be ok. For further reference on dynamic sql, check out this walkthrough by Erland Sommarskog.


Test data

Small A table, bigger B table (1M) and 6x bigger C table

CREATE TABLE DBO.TABLEA(ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,B_ID INT, VAL VARCHAR(255));

INSERT INTO DBO.TABLEA(B_ID,VAL)
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) , 'val'
FROM MASTER.dbo.spt_values;
--2540

CREATE TABLE DBO.TABLEB(ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL, VAL VARCHAR(255));

INSERT INTO DBO.TABLEB(VAL)
SELECT top(1000000) 'valB'
FROM MASTER.dbo.spt_values spt1
CROSS APPLY MASTER.dbo.spt_values spt2;
--1000000

CREATE TABLE dbo.TABLEC(ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL, A_ID  INT, VAL VARCHAR(255))

INSERT INTO DBO.TABLEC(A_ID,VAL)
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), 'val'
FROM MASTER.dbo.spt_values spt1
CROSS APPLY MASTER.dbo.spt_values spt2;
--6451600

Not the answer you're looking for? Browse other questions tagged or ask your own question.