Is IF ELSE better in this stored procedure than a Parameter? [duplicate]

Question

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

Answer 1

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.

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

While in reality, 0 rows are returned.

We can improve this by adding this index:

CREATE INDEX IX_A_ID
ON DBO.TABLEC(A_ID);

Causing no table access

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.

The ProcConditional actual execution plan seems ok.

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 .

Filter 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