SQL Server IF Branches And Query Performance Part 4: Do Stored Procedures Work?

Sub Sub

You know and I know and everyone knows that stored procedures are wonderful things that let you tune queries in magickal ways that stupid ORMs and ad hoc queries don’t really allow for.

Sorry about your incessant need to use lesser ways to manifest queries. They just don’t stack up.

But since we’re going to go high brow together, we need to learn how to make sure we don’t do anything to tarnish the image of our beloved stored procedures.

Like… Probably not doing most of the stuff that we talked about so far this week. We’ve seen some really bad ideas in action, which are largely only possible with stored procedures.

Giveth. Taketh.

It’s up to you.

Likened

The good news is that stored procedures help us with the problem we’ve been hitting all week, where when different IF branches are expanded, we get quality query plans for each one.

Here’s today’s setup, starting with the two procs that we’re going to be calling from our main proc in lieu of plain selects.

CREATE OR ALTER PROCEDURE
    dbo.counter_if_posts
(
    @PostTypeId int = NULL,
    @CreationDate datetime = NULL
)
AS
SET NOCOUNT, XACT_ABORT ON;
BEGIN

        SELECT
            c = COUNT_BIG(*)
        FROM dbo.Posts AS p
        JOIN dbo.Votes AS v
            ON p.Id = v.PostId
        WHERE p.PostTypeId = @PostTypeId
        AND   p.CreationDate >= @CreationDate;

END;
GO 

CREATE OR ALTER PROCEDURE
    dbo.counter_if_votes
(
    @VoteTypeId int = NULL,
    @CreationDate datetime = NULL
)
AS
SET NOCOUNT, XACT_ABORT ON;
BEGIN

        SELECT
            c = COUNT_BIG(*)
        FROM dbo.Posts AS p
        JOIN dbo.Votes AS v
            ON p.Id = v.PostId
        WHERE v.VoteTypeId = @VoteTypeId
        AND   v.CreationDate >= @CreationDate;

END;
GO

And here’s the main calling proc now:

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
    
    EXEC dbo.counter_if_posts
        @PostTypeId = @PostTypeId,
        @CreationDate = @CreationDate;
    
    END;

    IF @VoteTypeId IS NOT NULL
    BEGIN
 
    EXEC dbo.counter_if_votes
        @VoteTypeId = @VoteTypeId,
        @CreationDate = @CreationDate;
    
    END;

END;
GO

In this example, I’m letting the two procs “share” the creation date parameter for simplicity. There is almost no good reason to have a separate one.

The Plans

The good news here is that we can run queries like this back to back and get well-optimized plans for each:

DBCC FREEPROCCACHE
GO 

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

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

This is much happier than any of the results we’ve seen so far. The queries run quickly, have no spills, and cardinality estimation is on point.

Miced

The problem is when we do something like this, which is an actual parameter sensitivity issue. We can use this as an extreme example:

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

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

There are not many Vote Types of 4. There are quite a few manys of Type 2.

Going from 3 milliseconds to one minute and twenty seconds is what many would consider a failure.

I would agree.

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.