Why Expressions Are Better Than Local Variables For Performance In SQL Server Queries

Often Enough


I see people writing stored procedures that use local variables either because they read that all functions in a where clause are dangerous, or because they need to perform a calculation on one of the parameters that get passed in.

In nearly all cases, you’re better off… not doing that.

Here are a couple examples of why.

Hard Yes


Passing in a function like GETDATE, and even passing it to another function like DATEADD, the optimizer can interpret them to their current values and make a pretty decent guess based on them.

SQL Server Query Plan
goo

This is a Good Enough™ guess.

Hard Pass


Once you assign that function to a value, everything gets awkward.

SQL Server Query Plan
bang bang

That’s a Pretty Bad™ guess, but not the end of the world. What’s bad is that the guess never changes even if you change the span of time.

Look what happens if we just add one day instead of one year.

SQL Server Query Plan
one day at a time

We get the exact same guess as before — 821,584 rows. Bummer.

Storied Tradition


It’s nearly the same thing in stored procedures. What I see more often is people there is people passing in one parameter for a start date, and then using a local variable to figure out an end date.

CREATE OR ALTER PROCEDURE dbo.dangit_bobby
(
    @start_date datetime
)
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;

DECLARE
    @later datetime = DATEADD(DAY, 1, GETDATE());

    SELECT
        records = 
            COUNT_BIG(*)
    FROM dbo.express_yourself AS ey
    WHERE ey.some_date >= @start_date
    AND   ey.some_date <  @later;

END;
GO

EXEC dbo.dangit_bobby 
    @start_date = '2021-05-19';
GO
SQL Server Query Plan
FAR OFF DUDE

Advanced Calculus


Let’s change how we use the parameter, and put it into some date math in the where clause instead.

CREATE OR ALTER PROCEDURE dbo.dangit_bobby
(
    @start_date datetime
)
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;

    SELECT
        records = 
            COUNT_BIG(*)
    FROM dbo.express_yourself AS ey
    WHERE ey.some_date >= @start_date
    AND   ey.some_date <  DATEADD(DAY, 1, @start_date);

END;
GO 
EXEC dbo.dangit_bobby 
    @start_date = '2021-05-19';
GO

We get a Much Closer™ estimate. What a lovely day.

SQL Server Query Plan
go for both

Hardest Part


It’s often tempting to take shortcuts like this, especially if you need to reuse the same calculation multiple times.

Now, look, if you stare closely enough at these plans, you’ll notice that they all finish quickly enough. That’s because I have a small table with a wonderful index on the column I care about.

The point here is to show you how bad estimates can turn up even in ideal circumstances when you do things The Wrong Way™.

As long as you’re not wrapping columns in functions like this, you’re probably okay.

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.



5 thoughts on “Why Expressions Are Better Than Local Variables For Performance In SQL Server Queries

  1. Oooh yeah, this is a pet peeve of mine, and one I ask about in interviews if the interviewees bring up rewriting the examples I show them with local variables. It does get a bit tricky if you need to use GETDATE in several queries and get the same result (which is not a problem in the same query, but turns out not many know that either). BTW it might be worth mentioning the effect on RECOMPILE on these. Thanks for the post!

  2. IT TURNS OUT™ 😀 THAT™ THE USE OF SUCH VARIABLES™ will also put the kibosh on minimal logging UNLESS™, AS ALEX MENTIONED™ 😀 above, you use OPTION (RECOMPILE) EVEN IF™ 😀 EVERY THING ELSE IS PERFECT™ 😀 for the minimal logging.

      1. No… I haven’t about it. A good part of why not is for the same reason why you wrote this good article about using expressions instead of creating a local variable for things (which is certainly the right way to do it in front-end and other managed code)… at first, it seemed obvious and that everyone probably knows it only to find out (sometimes a whole lot) later that they don’t.

        If you want to write an article here on your blog, I’d be happy to provide a coded example. Or, I can write one over on SQLServerCentral. Seems like a good piece of info so I don’t care how it gets out… only that it gets out.

Comments are closed.