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.
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.
This is a Good Enough™ guess.
Once you assign that function to a value, everything gets awkward.
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.
We get the exact same guess as before — 821,584 rows. Bummer.
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
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.
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!
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”
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!
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.
Woah, neat! I didn’t know that about minimal logging. Crazy. Have you written about that anywhere?
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.
Ha ha, that’s all you. I didn’t know about it until now!
Comments are closed.