Brent Ozar and I both published temp table posts yesterday for T-SQL Tuesday #201. We both built a lab. We both argued from logical reads. We both ended up with the temp table winning.
And we found two completely different reasons why.
That is more interesting than either post on its own, so let me put them side by side.
What Brent Found
His post is When CTEs Don't Work, Try Temp Tables, and his rule is stated up front: "Default to CTEs. When SQL Server gets that process wrong, switch to temp tables."
His demo runs against the big Stack Overflow database on SQL Server 2025. Find the most popular value in Users.Location, then return the top 250 people in that location by Reputation. He builds indexes on both columns first, so the engine has what it needs.
The CTE version reads 478,982 pages -- more pages than exist in the entire clustered index of the table it is querying.
The cause is an estimate. SQL Server works out that it is looking for exactly one location, and gets that part right. What it cannot work out is which one, or that the one it is looking for is the most heavily populated value in the column rather than a typical one. So it estimates 14 rows, finds vastly more, and pays for the difference in key lookups. Brent counted 113,399 of them.
Swap the CTE for a temp table and the reads drop by two thirds.
His explanation of why is the part I want to sit with. When the second statement compiled, SQL Server had no statistics on the temp table, so it built them. Those statistics told it the one row in there with Location = India. It then looked India up in the statistics on Users.Location, recognized it as a heavily populated value, and compiled a plan suited to exactly that.
Brent's temp table won on statistics. They told the Optimizer which value it was really dealing with, and it chose a different plan shape on the strength of that.
What I Found
My demo was a dashboard-shaped query -- top 100 customers by trailing-twelve-month spend, shown next to the population average and maximum, filtered to those above the average. The aggregate is referenced four separate times.
The CTE version read 26,328 pages against tblOrders. The temp table version read 6,582. That ratio is 4.0000, and the plan says why: four separate Index Scan operators against the base table in one plan, against one in the other. A CTE is a named subquery, and the Optimizer expands its definition inline at every reference.
Then I checked the estimates, expecting them to explain the win.
| My demo | Estimated | Actual | Miss |
|---|---|---|---|
| CTE version (nested loops join) |
24,997.4 | 24,994 | 0.01% |
| Temp table version (scan of #CustomerTotals) |
15,000 | 24,994 | 40% low |
Backwards. The CTE version had a nearly perfect estimate. The temp table version came in 40 percent low -- 15,000 being exactly 30 percent of the 50,000 rows in the temp table, which is the fallback SQL Server reaches for on an inequality when it cannot know the value at compile time. In this case it couldn't, because I was filtering on a local variable.
That handicap was mine, not the temp table's -- write the filter as a subquery against the temp table instead of a variable and the guess goes away. I left it in because it did not matter.
My temp table won by 8x on total logical reads while carrying the worse estimate. It did not win by knowing more. It won by doing less.
Two Different Failure Modes
Which puts the two demos side by side like this.
| Brent's case | My case | |
|---|---|---|
| What the CTE cost | A bad estimate, paid for in key lookups |
The same aggregate evaluated four times |
| Estimate quality with the temp table |
Better | Worse |
| Why the temp table won | New statistics, new plan, fitted to the real value |
One pass instead of four, and smaller plans |
| Changes with your data distribution? |
Yes | Not in this demo |
I went into my lab expecting to find his answer. Reading his post showed me what I had found instead.
One of These You Can See Coming
Brent's opening argument for CTEs is that they let SQL Server pick a processing order suited to your current data distribution, on your current version. By his own account, the engine assumes a typically-sized location and gets the largest one in the table, so the gap between estimate and reality is a property of the data rather than of the query. Which is why his rule fits it so well -- run it, read the plan, find the first place estimates and actuals diverge badly, then act.
Mine is not data-dependent. Four references to a CTE means the Optimizer expands it four times regardless of what is in the table. Reload the data, change the distribution, rebuild the statistics -- still four, unless the Optimizer decides to spool, which it did not do here. There is nothing to discover, because the cost is a property of how the query is written rather than of what it is querying.
Which means the two cases want different habits.
Brent's case, you measure. It shows up once the plan runs and the actuals come back, and his advice to read right to left looking for the first big divergence is exactly how you catch it.
Mine, you count. Open the query, count the references to the CTE. If the answer is more than one, you already know the work is being repeated, before you execute anything. No plan required.
His is the harder of the two to catch, and the plan-reading habit he teaches is how you catch it. Mine announces itself in the query text -- if you're lucky.
Where We Land
Not far apart. Brent's position is default to CTEs and switch when SQL Server gets it wrong. Mine was that a temp table is a foe when it is a reflex and a friend when it is a decision. I'd almost say those are the same instinct wearing different clothes.
He is careful to say his is one case and not a rule. He points out that you are effectively getting an OPTION (RECOMPILE) out of the deal, and that the fresh plan he benefited from does not always happen, thanks to temp table caching.
Two cases, two mechanisms, both measured, published the same day, neither of us having seen the other's post first.
Jeff asked whether temp tables were friend or foe. Two of us answered yes, for entirely different reasons. That may be the most honest answer the question has.
More to Read
Brent Ozar: When CTEs Don't Work, Try Temp Tables -- #TSQL2sday
Jeff Taylor: T-SQL Tuesday #201 Invitation -- Temp Tables, Friend or Foe?
sqlfingers: Temp Tables -- Foe When It's a Reflex, Friend When It's a Decision
Brent Ozar: Paul White Explains Temp Table Caching 3 Ways
No comments:
Post a Comment