MiniMe to the rescue - Simple but effective performance tuning trick

Happy Thanksgiving!

We wish everyone a great thanksgiving and if you are stuck babysitting SQL server, we salute you! You suppor the infrastructure of the modern world. And that's a noble thing.

Here's another SQL Query performance optimization tip that is so simple in concept and actually works pretty well, often cutting down query execution time two or three folds. If you are joining a big table many times in a query, consider plucking off the small subset of that big table, put it in a tiny table variable or temp table and join them instead. Concept shown below:

If you're a seasoned pro, it's the sort of thing that goes without saying - sorry, in that case we didn't mean to offend your intelligence. But! Be it incompetence or negligence, we see this many times over. And over. And over. We feel guilty getting all the praises for magically making it go faster for such a simple trick, and feel that this should be on every SQL pro's toolset.

Anyone can do it, wokrs on any version, any edition of SQL and it works well. It works well on wide AND tall tables, and works particularly well if you are narrowing it by the same criteria in WHERE clause over and over again using expensive criteria such as the LIKE clause or a function. The idea is, you pay the price of doing the filtering just once, and leap the benefit of filtered small subset many times over. It has additional benefit of reducing locks on the large table.

One thing to note though, is if you want transactional integrity - you can still use the trick, but be sure to use Temporary Table. Yep. The hashtags for data nerds before there were hashtags. Use them and wrap it with begin tran - commit tran, and you're good to go. If you don't care about transactions, as in reports, then simply say (nolock) and fetch a tiny performance boost when you are making your @MiniMe table.

That's it for today! Talk to you all soon!