How to make a query faster, using temp tables
Updated: Sep 7, 2022
Okay so temp tables don't actually make queries faster... however, temp tables can be used to let SQL Server maker better decisions when designing its query plans, which in turn can make your queries go faster!
When you first execute a new query, SQL Server will run the query in several phases.
Phase 1, it will look at the query you're trying to run and attempt to use statistics to build a query plan. It won't actually look into the data of the tables you're trying to access, but instead try to guess how many rows it will find at each operation and make decisions, based on those guesses.
Phase 2, it will run each of these operations and actually retrieve the data we want. Sometimes, we made great guesses in the first phase and we found roughly the same amount of rows as we thought we would. That means we made all the correct choices in the first phase and our second phase is likely running at an optimal speed.
Sometimes, however, those guesses aren't as great... we might return several hundred times the amount of rows that the first phase thought we would. In these scenarios, we might have wanted to make other decisions than the ones we are stuck with. ...but we can't, because we are stuck with it...
If we find exactly where in the query plan we start to drift apart between our estimated rows and our actual rows (for instance 54 780 estimated vs 625 actual on the top right Index Seek in the picture above), we can change our query to cut out the sections up until the point that estimated vs actual started to drift apart, and throw the result into a temp table (in this situation, that would be the very first table we touch). Then use the temp table to continue the rest of the query.
What this does is to perform Phase 1 and Phase 2 on a part of the query up until the poorly estimated part (only on the table "users", and then run Phase 1 and Phase 2 again on the rest of the query, calculating a new plan with better understanding of what data we are actually dealing with. This works because temp tables also hold statistics on what it contains, allowing Phase 1 to make guesses based on the results so far.