A lot of the time when I see queries that are written with all sorts of gymnastics in the join or where clause and I ask some questions about it, people usually start complaining about the design of the table.
That’s fine, but when I ask about changing the design, everyone gets quiet. Normalizing tables, especially for Applications Of A Certain Age™ can be a tremendously painful project. This is why it’s worth it to get things right the first time. Simple!
Rather than make someone re-design their schema in front of me, often times a temp table is a good workaround.
Let’s say we have a query that looks like this. Before you laugh, and you have every right to laugh, keep in mind that I see queries like this all the time.
They don’t have to be this weird to qualify. You can try this if you have functions like ISNULL, SUBSTRING, REPLACE, or whatever in joins and where clauses, too.
SELECT p.OwnerUserId, SUM(p.Score) AS TotalScore, COUNT_BIG(*) AS records FROM dbo.Users AS u JOIN dbo.Posts AS p ON u.Id = CASE WHEN p.PostTypeId = 1 THEN p.OwnerUserId WHEN p.PostTypeId = 2 THEN p.LastEditorUserId END WHERE p.PostTypeId IN (1, 2) AND p.Score > 100 GROUP BY p.OwnerUserId;
There’s not a great way to index for this, and sure, we could rewrite it as a UNION ALL, but then we’d have two queries to index for.
Sometimes getting people to add indexes is hard, too.
People are weird. All day weird.
You can replace it with a query like this, which also allows you to index a single column in a temp table to do your correlation.
SELECT p.OwnerUserId, SUM(p.Score) AS TotalScore, COUNT_BIG(*) AS records, CASE WHEN p.PostTypeId = 1 THEN p.OwnerUserId WHEN p.PostTypeId = 2 THEN p.LastEditorUserId END AS JoinKey INTO #Posts FROM dbo.Posts AS p WHERE p.PostTypeId IN (1, 2) AND p.Score > 100 GROUP BY CASE WHEN p.PostTypeId = 1 THEN p.OwnerUserId WHEN p.PostTypeId = 2 THEN p.LastEditorUserId END, p.OwnerUserId; SELECT * FROM #Posts AS p WHERE EXISTS ( SELECT 1/0 FROM dbo.Users AS u WHERE p.JoinKey = u.Id );
Remember that temp tables are like a second chance to get schema right. Don’t waste those precious chances.
Thanks for reading!
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.
One thought on “Making The Most Of SQL Server Temporary Tables Part 2: Materializing Expressions”
Comments are closed.