T-SQL Tuesday #200 - I Bet It's Bad if I See...

T-Sql-Tuesday
Page content

T-SQL Tuesday is a monthly blog party hosted by a different community member each month. This month, Brent Ozar (blog) asks us to fill in the blank:

When I’m looking at a query, I bet it’s bad if I see ____.

T-SQL Tuesday Logo

Code Smells

write a few paragraphs about why you don’t like seeing _____ in production code, the problem that it’s caused you in the past, and what you recommend instead.

What Brent’s asking us about is code smells. Remember, Brent’s premise is “I bet it’s bad…”, not “It’s always bad…” - sometimes, you lose the bet. What code do we see that makes us recoil in horror when we see (smell) it? Not all code that has a smell is bad but like cheeses, we have to use caution when we start smelling something, and take a closer look before we decide to…ok, yeah, this analogy is breaking down. Sorry. Back to business.

I’ve spent a lot of time over the past 8 or years trying to “right the ship.” Systems that have been built and evolved over 10-15 years and the cracks are starting to show. Yes, there’s always the hot spot code that desperately needs attention, the stored procedure that runs in 6 hours but could be 20 minutes with the right adjustments. But I’m looking at a macro level today, more “operational” than “surgical.” When I see ____ in a chunk of code, it’s a signal to me that there are overarching problems in how the whole system I’m working on was built and it’s going to take me a good, long while to undo that to deliver constant performance as data grows or improve maintainability.

What is ____?

It’s a temp table (or scarier still, a table variable) that gets created and populated with fixed values right at the top of the procedure/query. You’ve seen it, right?

1
2
3
4
5

create table #Locales (LocaleId varchar(10), LocaleName nvarchar(100));
insert into #Locales (LocaleId, LocaleName) values ('en-US','United States')
    ,('en-GB','Great Britain')
    ,('fr-FR', 'France')
    ,('en-CA','Canada');

It’s the lookup table that acts as the “magic en/decoder ring” each time that code runs. And then you find it in the next stored procedure that gets on your radar. And the next one. And another one. And another one.

Maybe there’s more than one lookup table in some of these procedures.

But It Works, Right?

What’s the problem? This pattern signals a few things to me.

  • It means that any change to that lookup becomes a code deployment. Branch, edit, pull request, merge, pipeline, approvals, how long will this take? This lookup table is no longer configuration/reference data that has been stored somewhere. It’s live code running in the database.
  • Once it’s sprinkled across a half-dozen (or more!) stored procedures, you must maintain it across all that code, else you’ll get interesting results.
    • Spreading multiple copies around means more time editing, validating, and approving too! So changes will take longer.
  • How much churn of the same data will you have in tempdb on a daily basis? Why are we burning these resources?
  • This is tactical development, not strategic. The team hasn’t paused to think about the fact that they’re duplicating code. They haven’t looked at the big picture of “what is the purpose of this lookup data? Do I need it elsewhere?” The latter has already been answered - if they’re copying & pasting it from the last stored procedure they wrote, it clearly is needed it in multiple places.

All of this adds up to a pile of technical debt that we’re going to have to come to terms with eventually. Hopefully before it freezes forward progress.

It Goes Deeper

Once you see that temp table copypasta’d across a half dozen stored procedures, you realize that there are even more large chunks of code that have been copypasta’d as well. If that logic changes, we’re into changing even more copies of the same code (and/or temp table data).

Understanding that SQL Server doesn’t always do “modular/reusable code” as well as we might like, it behooves us to at least try to refactor those common elements out to a reusable object. Which brings me to…

Making It Better

These lookup tables need to be, well, tables. Real tables in the application database. Or maybe a utility database that multiple applications can share. If you’re feeling adventurous, have a core server that hosts these lookups and replicates the tables out to the masses. You want a copy for yourself inside your stored procedure? OK, we can do that - load it into a temp table from that trusted and managed reference copy.

Honorable Mentions

Tuesday is almost over so I can’t write in depth about these, but a few more patterns that get my hackles up.

Dropping Temp Tables

I don’t mean to say that it’s unilaterally bad to drop table if exists #Locales. But if I see this at the very beginning of a stored procedure, I’m getting twitchy about what other code may be left behind from the development process. In other words, the code was written up in SSMS to execute “cleanly” every time…and then we wrapped it in create procedure [whatever] as BEGIN...END, checked it into git, and called it a day.

Leading Semicolons

Leading semicolons on every Common Table Expression (CTE). Sure, it’s valid, nothing breaks - but I wonder if the developer understands why they put it there. Or did they do it because “that’s just what you do” or “the tutorial said I had to.” If they were using semicolons to terminate all their statements it wouldn’t even be necessary. Regardless, it leaves me wondering what other bits of SQL Server behavior were misunderstood when this code was written?

Creative Parameters

I still have nightmares about this one. Imagine that you need to pass an unknown number of values into a stored procedure as parameters. How many ways can you think of to do this that aren’t table-valued user defined types? You’re into delimited or formatted strings now. And how many different ways can you parse various types of strings? It’s all good though - SQL Server is just excellent at parsing large amounts of text quickly and cleanly, especially with pre-2016 code that’s still kicking around.

Coda

It’s been far too long since I’ve posted for T-SQL Tuesday and I’m glad I was able to get back into it for this one. Thanks to Brent for hosting! I’m looking forward to reading all the posts and learning a few things.