A faboulously awful query

fabulous-query.sql

IF OBJECT_ID('tempdb..#recent_votes') IS NOT NULL
    TRUNCATE TABLE #recent_votes;
ELSE 
    CREATE TABLE #recent_votes (
        PostId INT PRIMARY KEY,
        UserId INT,
        VoteWeight INT
    );

IF OBJECT_ID('tempdb..#posts') IS NOT NULL
    TRUNCATE TABLE #posts;
ELSE
    CREATE TABLE #posts (
        Id INT,
        Score INT
    );

DECLARE @today DATE = '2013-09-06';

INSERT INTO #recent_votes
SELECT  v.PostId,
        v.UserId,
        (SUM(CASE WHEN v.VoteTypeId = 2 THEN 1 
                 WHEN v.VoteTypeId = 3 THEN -1
                 ELSE 0 END ) 
            / COUNT(*)) AS VoteWeight
FROM    dbo.Votes AS v
WHERE   v.CreationDate > CAST(DATEADD(dd, -90, @today) AS DATE)
        AND v.VoteTypeId IN (2,3)
GROUP BY v.PostId, v.UserId ;

INSERT INTO #posts
SELECT  p.Id ,
        p.Score 
FROM    dbo.Posts AS p
        LEFT JOIN dbo.Posts AS answers ON answers.ParentId = p.Id
                                          AND answers.Score > 0 
WHERE   p.CreationDate > CAST(DATEADD(dd, -90, @today) AS DATE)
        AND p.AcceptedAnswerId = 0
        AND answers.Id IS NOT NULL ;

SELECT  TOP 2000
        p.Id ,
        p.Score ,
        p.Score 
        + SUM(v.VoteWeight)
        + SUM(COALESCE(uv.Reputation, 0)) AS SortWeight
FROM    dbo.#posts AS p
        JOIN #recent_votes AS v ON p.Id = v.PostId
        LEFT JOIN dbo.Users AS uv ON v.UserId = uv.Id
GROUP BY p.Id, p.Score
ORDER BY SortWeight DESC
;
GO

stats-io-and-time.txt

SQL Server parse and compile time: 
   CPU time = 31 ms, elapsed time = 35 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table 'Votes'. Scan count 1, logical reads 13577, physical reads 0, read-ahead reads 13538, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 4234 ms,  elapsed time = 3110 ms.

(1806688 row(s) affected)
Table 'Posts'. Scan count 4, logical reads 19302, physical reads 0, read-ahead reads 6, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1875 ms,  elapsed time = 1433 ms.

(108600 row(s) affected)
SQL Server parse and compile time: 
   CPU time = 1063 ms, elapsed time = 1131 ms.

(2000 row(s) affected)
Table '#posts______________________________________________________________________________________________________________000000000041'. Scan count 3, logical reads 229, physical reads 0, read-ahead reads 3, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#recent_votes_______________________________________________________________________________________________________000000000040'. Scan count 3, logical reads 4693, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Users'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 436 ms,  elapsed time = 326 ms.

text-plan.txt

SQL Server parse and compile time: 
   CPU time = 15 ms, elapsed time = 26 ms.
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @today DATE = '2013-09-06';
INSERT INTO #recent_votes
SELECT  v.PostId,
        v.UserId,
        (SUM(CASE WHEN v.VoteTypeId = 2 THEN 1 
                 WHEN v.VoteTypeId = 3 THEN -1
                 ELSE 0 END ) 
            / COUNT(*)) AS VoteWeight
FROM    dbo.Votes AS v

(2 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Table Insert(OBJECT:([tempdb].[dbo].[#recent_votes]), SET:([tempdb].[dbo].[#recent_votes].[PostId] = [StackOverflow].[dbo].[Votes].[PostId] as [v].[PostId],[tempdb].[dbo].[#recent_votes].[UserId] = [StackOverflow].[dbo].[Votes].[UserId] as [v].[UserId
       |--Compute Scalar(DEFINE:([Expr1006]=[Expr1004]/[Expr1005]))
            |--Parallelism(Gather Streams)
                 |--Compute Scalar(DEFINE:([Expr1005]=CONVERT_IMPLICIT(int,[Expr1016],0)))
                      |--Hash Match(Aggregate, HASH:([v].[PostId], [v].[UserId]), RESIDUAL:([StackOverflow].[dbo].[Votes].[PostId] as [v].[PostId] = [StackOverflow].[dbo].[Votes].[PostId] as [v].[PostId] AND [StackOverflow].[dbo].[Votes].[UserId] as [v].[U
                           |--Compute Scalar(DEFINE:([Expr1008]=CASE WHEN [StackOverflow].[dbo].[Votes].[VoteTypeId] as [v].[VoteTypeId]=(2) THEN (1) ELSE CASE WHEN [StackOverflow].[dbo].[Votes].[VoteTypeId] as [v].[VoteTypeId]=(3) THEN (-1) ELSE (0) END E
                                |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS:([v].[PostId], [v].[UserId]))
                                     |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1014], [Expr1015], [Expr1013]))
                                          |--Compute Scalar(DEFINE:(([Expr1014],[Expr1015],[Expr1013])=GetRangeWithMismatchedTypes(dateadd(day,(-90),[@today]),NULL,(6))))
                                          |    |--Constant Scan
                                          |--Index Seek(OBJECT:([StackOverflow].[dbo].[Votes].[IX_VotesByCreationDate_filtered] AS [v]), SEEK:([v].[CreationDate] > [Expr1014] AND [v].[CreationDate] < [Expr1015]) ORDERED FORWARD)

(11 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO #posts
SELECT  p.Id ,
        p.Score 
FROM    dbo.Posts AS p
        LEFT JOIN dbo.Posts AS answers ON answers.ParentId = p.Id
                                          AND answers.Score > 0 
WHERE   p.CreationDate > CAST(DATEADD(dd, -90

(1 row(s) affected)

StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Table Insert(OBJECT:([tempdb].[dbo].[#posts]), SET:([tempdb].[dbo].[#posts].[Id] = [StackOverflow].[dbo].[Posts].[Id] as [p].[Id],[tempdb].[dbo].[#posts].[Score] = [StackOverflow].[dbo].[Posts].[Score] as [p].[Score]))
       |--Parallelism(Gather Streams)
            |--Hash Match(Inner Join, HASH:([p].[Id])=([answers].[ParentId]), RESIDUAL:([StackOverflow].[dbo].[Posts].[ParentId] as [answers].[ParentId]=[StackOverflow].[dbo].[Posts].[Id] as [p].[Id]))
                 |--Bitmap(HASH:([p].[Id]), DEFINE:([Bitmap1009]))
                 |    |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS:([p].[Id]))
                 |         |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1007], [Expr1008], [Expr1006]))
                 |              |--Compute Scalar(DEFINE:(([Expr1007],[Expr1008],[Expr1006])=GetRangeWithMismatchedTypes(dateadd(day,(-90),[@today]),NULL,(6))))
                 |              |    |--Constant Scan
                 |              |--Index Seek(OBJECT:([StackOverflow].[dbo].[Posts].[IX_UnansweredQuestions] AS [p]), SEEK:([p].[CreationDate] > [Expr1007] AND [p].[CreationDate] < [Expr1008]) ORDERED FORWARD)
                 |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([answers].[ParentId]))
                      |--Index Scan(OBJECT:([StackOverflow].[dbo].[Posts].[IX_ScoredCommentsByParent] AS [answers]),  WHERE:(PROBE([Bitmap1009],[StackOverflow].[dbo].[Posts].[ParentId] as [answers].[ParentId],N'[IN ROW]')))

(11 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT  TOP 2000
        p.Id ,
        p.Score ,
        p.Score 
        + SUM(v.VoteWeight)
        + SUM(COALESCE(uv.Reputation, 0)) AS SortWeight
FROM    dbo.#posts AS p
        JOIN #recent_votes AS v ON p.Id = v.PostId
        LEFT JOIN dbo.

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Top(TOP EXPRESSION:((2000)))
       |--Parallelism(Gather Streams, ORDER BY:([Expr1007] DESC))
            |--Sort(TOP 2000, ORDER BY:([Expr1007] DESC))
                 |--Compute Scalar(DEFINE:([Expr1007]=[tempdb].[dbo].[#posts].[Score] as [p].[Score]+[Expr1005]+[Expr1006]))
                      |--Compute Scalar(DEFINE:([Expr1005]=CASE WHEN [Expr1021]=(0) THEN NULL ELSE [Expr1022] END, [Expr1006]=CASE WHEN [Expr1023]=(0) THEN NULL ELSE [Expr1024] END))
                           |--Hash Match(Aggregate, HASH:([p].[Id], [p].[Score]), RESIDUAL:([tempdb].[dbo].[#posts].[Id] as [p].[Id] = [tempdb].[dbo].[#posts].[Id] as [p].[Id] AND [tempdb].[dbo].[#posts].[Score] as [p].[Score] = [tempdb].[dbo].[#posts].[Sc
                                |--Compute Scalar(DEFINE:([Expr1008]=CASE WHEN [StackOverflow].[dbo].[Users].[Reputation] as [uv].[Reputation] IS NOT NULL THEN [StackOverflow].[dbo].[Users].[Reputation] as [uv].[Reputation] ELSE (0) END))
                                     |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([p].[Id], [p].[Score]))
                                          |--Nested Loops(Left Outer Join, OUTER REFERENCES:([v].[UserId], [Expr1020]) OPTIMIZED WITH UNORDERED PREFETCH)
                                               |--Hash Match(Inner Join, HASH:([p].[Id])=([v].[PostId]), RESIDUAL:([tempdb].[dbo].[#recent_votes].[PostId] as [v].[PostId]=[tempdb].[dbo].[#posts].[Id] as [p].[Id]))
                                               |    |--Bitmap(HASH:([p].[Id]), DEFINE:([Bitmap1019]))
                                               |    |    |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([p].[Id]))
                                               |    |         |--Table Scan(OBJECT:([tempdb].[dbo].[#posts] AS [p]))
                                               |    |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([v].[PostId]))
                                               |         |--Table Scan(OBJECT:([tempdb].[dbo].[#recent_votes] AS [v]), WHERE:(PROBE([Bitmap1019],[tempdb].[dbo].[#recent_votes].[PostId] as [v].[PostId],N'[IN ROW]')))
                                               |--Clustered Index Seek(OBJECT:([StackOverflow].[dbo].[Users].[PK_Users] AS [uv]), SEEK:([uv].[Id]=[tempdb].[dbo].[#recent_votes].[UserId] as [v].[UserId]) ORDERED FORWARD)

(16 row(s) affected)