Optimizing Complex SQL Server Queries By Using Batch Mode
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
If you were running this query on SQL 2019+, would it have used batch mode by default? Or would you still need to use the empty temp table trick?
That depends on a few things:
1. If you’re on Enterprise Edition
2. If you’re using compatibility level 150
3. If the heuristics dictate that the plan would benefit from exploring batch mode operators
I often find queries that meet the first two requirements, and should have met the third requirement that still need tricks played on them.
Thanks!
Did you try to add a GROUP BY user_id and MAX(last_access_date) to the three all unioned sub queries?
In my experience it saves usually some extra time, when you reduce the amount of rows to process as soon as possible and I see no reason, why the concating / hash aggregate needs to process 94 mio rows instead of a few thousand (distinct users))
No, the point of the video was to show performance improvements without code changes.