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. 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 database performance problems quickly. You can also get a quick, low cost health check with no phone time 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.