I Know You
You have too many indexes on too many tables already, and the thought of adding more fills you with a dread that has a first, middle, last, and even a confirmation name.
This is another place where temp tables can save your bacon, because as soon as the query is done they basically disappear.
Off to buy a pack of smokes.
In yesterday’s post, we looked at how a temp table can help you materialize an expression that would otherwise be awkward to join on.
If we take that same query, we can see how using the temp table simplifies indexing.
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; CREATE CLUSTERED INDEX c ON #Posts(JoinKey); SELECT * FROM #Posts AS p WHERE EXISTS ( SELECT 1/0 FROM dbo.Users AS u WHERE p.JoinKey = u.Id );
Rather than have to worry about how to handle a bunch of columns across the where and join and select, we can just stick a clustered index on the one column we care about doing anything relational with to get the final result.
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 on 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.