Fingerless
I get why these things happen. You’re the <new person> somewhere, and someone asks for you to add something to a report, or something
You look at the original query, and it’s like 1000 lines long.
There’s dozens of joins, and a half-mile where clause full of ands and ors.
There’s no way you’re messing with that. You just tack your left join on and walk away.
Fine.
Everything’s Eventual
Don’t get me wrong. Though some combination of skill, luck, hardware, or size of data, this might work for a while.
SQL Server might even help you out with a parallel plan. They’re sort of the Great Equalizer™ for performance.
Optimizer thinks this is gonna be a doozy? Have some more CPU!
Be my guest. They’re free, right?
Eventually, though, this will get slower and slower.
This is usually about the time someone gives me a call.
Chewy and Chompy
See, when a query is big and complicated to you, there’s a pretty good chance you’re gonna get a big and complicated query plan, because it’s big and complicated to the optimizer, too.
This isn’t to say the optimizer is dumb or bad or ugly; it’s just that there’s only so long it’s willing to spend coming up with a plan.
Remember, cheap plan fast. Not perfect, not great, maybe good enough.
Cheap and fast.
Even worse, the bigger a query plan is, the less likely it is to be helpful to analyze.
Costs get so spread out, it’s hard to focus on what might make a difference.
Hatchet Act
When I have to tune a query like this, there’s some stuff I’ll try out first to get a feel for what’s going on, but ultimately your best friend is breaking things up.
The optimizer is just like you and me. The more chances and choices we have, the more likely we are to screw one up.
Really big queries usually have some logical stopping points, that you might wanna try materializing by sticking them in a #temp table.
- CTEs
- Derived tables
- Subqueries
- UNION/UNION ALL
- Initial Inner Joins
The last point there might be a little unclear. I mean that usually your query starts off with some inner joins, then people start tacking left joins on.
If you grab the most restrictive stuff first, that’s sometimes a good starting place.
But really, all of those things are valid. It’s easier to tune a bunch of small queries than one big query.
The Hounds Of Hinterville
This is also where I’m a big fan of hints — not because I want them to stay, but because I wanna see how the plan changes.
Join and aggregate hints, recompile, trying to force a parallel plan, FAST 1, etc. are all valid experiments to see if there’s something the optimizer isn’t figuring out on its own.
Figuring out why is harder, but hey, the only way to get good at that is to keep tuning.
Hints are great to learn from, and sometimes the only way to get the plan you want.
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.
Adding one more value in the IN and every new person says -“ I just added one value, all others were already there”.
An IN can be converted to a small look up table if there are too many values.