SQL Server IF Branches And Query Performance Part 3: Local Variables Still Suck

Blip In The Wall

---

Parameters get a bad name. It seems everything that gets sniffed does, whether it’s a stored procedure, a piece of anatomy, or an illicit substance.

Once you stop worshipping memes, you’ll find that things aren’t always black and white. End results are important.

What never seems to get a bad name, despite numerical supremacy in producing terrible results, are local variables.

In this particular scenario, I see developers use them to try to beat “parameter sniffing” to no avail.

A chorus of “it seemed to work at the time”, “I think it made things a little better”, “it worked on my machine”, and all that will ensue.

But we know the truth.

Hush, Puppy

---

This general strategy is one that I see often enough to warrant a blog post to talk about how bad it is.

It’s a little different from yesterday, but equally misguided in its effect.

CREATE OR ALTER PROCEDURE
    dbo.counter_if
(
    @PostTypeId int = NULL,
    @VoteTypeId int = NULL,
    @CreationDate datetime = NULL
)
AS
SET NOCOUNT, XACT_ABORT ON;
BEGIN
    
    IF @PostTypeId IS NOT NULL
    BEGIN

        DECLARE
            @PostTypeIdCopy int = @PostTypeId,
            @CreationDatePostCopy datetime = @CreationDate;
    
        SELECT
            c = COUNT_BIG(*)
        FROM dbo.Posts AS p
        JOIN dbo.Votes AS v
            ON p.Id = v.PostId
        WHERE p.PostTypeId = @PostTypeIdCopy
        AND   p.CreationDate >= @CreationDatePostCopy;
    
    END;

    IF @VoteTypeId IS NOT NULL
    BEGIN

        DECLARE
            @VoteTypeIdCopy int = @VoteTypeId,
            @CreationDateVoteCopy datetime = @CreationDate;
    
        SELECT
            c = COUNT_BIG(*)
        FROM dbo.Posts AS p
        JOIN dbo.Votes AS v
            ON p.Id = v.PostId
        WHERE v.VoteTypeId = @VoteTypeIdCopy
        AND   v.CreationDate >= @CreationDateVoteCopy;
    
    END;

END;

In this (bad, threatening individual) display of transact SQL, we declare local copies of each parameter as a variable and then use those local copies as arguments in the queries.

There are other variations on this theme that I see from time to time, but I think that you get the point.

Cardinality Sins

---

What I want to show you here is related to cardinality estimation issues with the above strategy.

DBCC FREEPROCCACHE
GO 
EXEC dbo.counter_if
    @PostTypeId = 8, 
    @VoteTypeId = NULL,
    @CreationDate = '20080101';
GO 
DBCC FREEPROCCACHE
GO 
EXEC dbo.counter_if
    @PostTypeId = 2, 
    @VoteTypeId = NULL,
    @CreationDate = '20080101';
GO 

DBCC FREEPROCCACHE
GO 
EXEC dbo.counter_if
    @PostTypeId = NULL, 
    @VoteTypeId = 4,
    @CreationDate = '20080101';
GO 
DBCC FREEPROCCACHE
GO 
EXEC dbo.counter_if
    @PostTypeId = NULL, 
    @VoteTypeId = 2,
    @CreationDate = '20080101';
GO

The point here is to execute each query with a cleared plan cache and show that cardinality estimation for both parameter values is the same value, and also completely wrong.

Posts

---

Here are the plans that hit the Posts table:

Votes

---

Here are query plans for the Votes table:

Planama!

---

Regardless of how many rows may actually flow out of the table, SQL Server makes the same “density vector” guess, which sucks in these cases.

This isn’t parameter sniffing, or plan caching, because we’re wiping out the plan cache between executions. This is what happens when local variables get used, even when you copy parameter values over to them.

I’ve seen this work under rare circumstances where SQL Server’s estimates were bad regardless of the supplied parameters, but… I want to emphasize that it’s incredibly rare for this to be a good long-term solution.

You can sort of ignore the cardinality estimates on the joined tables a bit, because the Bitmaps are going to throw those off a bit.

So, we’ve seen the problem, and we’ve seen things that don’t work to fix the problem.

Tomorrow we’re going to see if stored procedures will give us better results.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.