The main improvements I would suggest are:
- Minimize the number of accesses made to the large, minimally indexed, SEDE tables.
- Break the query into multiple steps.
- Reduce or eliminate code repetition.
Temporary tables are allowed on SEDE. Use these to fetch the minimal data needed from the large base tables to perform the calculations. Referring to these smaller data sets will be much more efficient than interrogating the base tables multiple times.
Properly used, temporary tables enable more accurate cardinality estimation, automatic statistics on intermediate results, and provide indexing opportunities - all of which can improve final plan quality and performance.
Breaking the query up also makes the logic easier to comprehend and debug (initially, and when maintaining it in future). Errors and redundancies are much easier to spot with smaller queries.
I have not attempted to improve the reputation calculation logic itself, except in a few minor ways, but the following illustrates a possible re-implementation of the provided code using temporary tables:
Initialization
DECLARE
@UserId integer = 22656, -- Jon Skeet, why not
@UntilDate datetime = CURRENT_TIMESTAMP;
DROP TABLE IF EXISTS
#Posts, #Data, #Edits;
Posts data
Just the columns we are going to need from the Posts table:
CREATE TABLE #Posts
(
Id integer NOT NULL,
AcceptedAnswerId integer NULL,
CommunityOwnedDate datetime NULL,
PostTypeId tinyint NOT NULL
);
Load the minimal number of rows needed:
INSERT #Posts
(
Id,
AcceptedAnswerId,
CommunityOwnedDate,
PostTypeId
)
SELECT
P.Id,
P.AcceptedAnswerId,
P.CommunityOwnedDate,
P.PostTypeId
FROM dbo.Posts AS P
WHERE
P.OwnerUserId = @UserId
AND P.CreationDate <= @UntilDate
AND
(
P.CommunityOwnedDate IS NULL
OR P.CommunityOwnedDate <= @UntilDate
);
This applies the common predicates in the original query as early as possible, including setting a boundary of the values of CommunityOwnedDate that might impact the reputation calculation up to the desired date. Other filtering could be added here, for example to restrict the PostTypeId values to just those of interest.
The execution plan (for Jon Skeet) is:
This plan is relatively efficient, being primarily based on a seek to the OwnerUserId specified. The Merge Interval subtree is concerned with finding the range of CommunityOwnedDate values used as a secondary seek predicate.
The Key Lookup is sadly unavoidable, given the SEDE view dbo.Posts wrapping the underlying table dbo.PostsWithDeleted (filtering on DeletionDate IS NULL without a supporting index). Nevertheless, it does mean we can filter CreationDate in the same lookup without adding significant cost.
Adding votes data
The next step adds the columns we need from the voting data associated with the qualifying posts. This is performed as a second step rather than joining all in one step because the #Posts table provides useful accurate cardinality and statistical information. A join query is quite likely to mis-estimate, resulting in an inappropriate plan selection or hash spill, for example.
CREATE TABLE #Data
(
AcceptedAnswerId integer NULL,
CommunityOwnedDate datetime NULL,
PostTypeId tinyint NOT NULL,
BountyAmount integer NULL,
VoteTypeId tinyint NOT NULL,
CreationDate datetime NOT NULL
)
WITH (DATA_COMPRESSION = ROW);
Row compression is not necessarily super-useful here, but it does illustrate the extra flexibility of using discrete result sets.
INSERT #Data WITH (TABLOCK)
(
AcceptedAnswerId,
CommunityOwnedDate,
PostTypeId,
BountyAmount,
VoteTypeId,
CreationDate
)
SELECT
P.AcceptedAnswerId,
P.CommunityOwnedDate,
P.PostTypeId,
V.BountyAmount,
V.VoteTypeId,
V.CreationDate
FROM #Posts AS P
JOIN dbo.Votes AS V
ON V.PostId = P.Id
WHERE
V.CreationDate <= @UntilDate
AND
(
P.CommunityOwnedDate IS NULL
OR P.CommunityOwnedDate > V.CreationDate
);
The query above again applies predicates as early as possible, and gives us a combined view of the posts and votes that we can interrogate in multiple ways later on at low cost (certainly much cheaper than accessing the base tables again).
The execution plan features accurate cardinality estimates, appropriate use of parallelism, and a hashtable build-side bitmap filter used to perform semi-join reduction on the Votes table scan. This removes rows that cannot possibly join even before they are surfaced from the storage engine to the query processor (as indicated by the INROW attribute).
Suggested Edit Data
We only need the date (no time component) and total reputation earned for suggested edits:
CREATE TABLE #Edits
(
ApprovalDate datetime PRIMARY KEY,
Reputation integer NOT NULL
);
INSERT #Edits
(
ApprovalDate,
Reputation
)
SELECT
G.ApprovalDate,
EditRep = 2 * COUNT(*) -- 2 rep per approved suggested edit
FROM dbo.SuggestedEdits AS SE
CROSS APPLY
(
VALUES (CONVERT(datetime, CONVERT(date, SE.ApprovalDate)))
) AS G (ApprovalDate)
WHERE
SE.OwnerUserId = @UserId
AND SE.ApprovalDate <= @UntilDate
AND SE.RejectionDate IS NULL
GROUP BY
G.ApprovalDate;
Using CROSS APPLY with VALUES allows us to avoid repeating the datetime to date conversion in the SELECT list and GROUP BY clause. The converted value is put back in a datetime type for consistency with the other SEDE-derived tables.
It is generally best practice to pay careful attention to data types, avoiding implicit conversions. This has myriad benefits, not least for cardinality estimation and join performance & optimizations.
A possible improvement to the code here would be to use the date type everywhere only the date portion is needed.
The parallel scan of the SuggestedEdits table is the best we can do here, without better indexing in SEDE.
Reputation Per Day
This table largely replaces the original r2d subquery, computing reputation totals per day:
CREATE TABLE #DayTotals
(
CreationDate datetime PRIMARY KEY,
ReputationFromVotes integer NOT NULL,
ReputationFromBounties integer NOT NULL,
ReputationFromAccepts integer NOT NULL,
ReputationFromSuggestedEdits integer NOT NULL
);
INSERT #DayTotals WITH (TABLOCK)
(
CreationDate,
ReputationFromVotes,
ReputationFromBounties,
ReputationFromAccepts,
ReputationFromSuggestedEdits
)
SELECT
D.CreationDate,
ReputationFromVotes =
ISNULL
(
SUM
(
CASE
WHEN D.PostTypeId = 1 AND D.VoteTypeId = 2 THEN 5
WHEN D.PostTypeId = 2 AND D.VoteTypeId = 2 THEN 10
WHEN D.VoteTypeId = 3 THEN -2
ELSE 0
END
), 0
),
ReputationFromBounties =
ISNULL(SUM(CASE WHEN D.VoteTypeId = 9 THEN D.BountyAmount ELSE 0 END), 0),
ReputationFromAccepts =
ISNULL(SUM(CASE WHEN D.VoteTypeId = 1 THEN 15 ELSE 0 END), 0),
ReputationFromSuggestedEdits = ISNULL(SUM(E.Reputation), 0)
FROM #Data AS D
LEFT JOIN #Edits AS E
ON E.ApprovalDate = D.CreationDate
GROUP BY
D.CreationDate;
The execution plan shows efficient parallel processing using only the relatively small temporary tables:
Other reputation totals
These are single values, stored in variables for convenience:
DECLARE
@RepBountiesGiven integer =
(
SELECT
ISNULL(SUM(-D.BountyAmount), 0)
FROM #Data AS D
WHERE
D.VoteTypeId = 8 -- BountyStart
),
@RepFromAcceptingAnswers integer =
(
SELECT
2 * COUNT(*) -- 2 rep per answer accepted
FROM #Posts AS P
WHERE
P.PostTypeId = 1
AND P.AcceptedAnswerId IS NOT NULL
),
@ReputationFromRepCap integer =
(
SELECT
SUM
(
CASE
WHEN ReputationFromVotes + ReputationFromSuggestedEdits <= 200
THEN ReputationFromVotes + ReputationFromSuggestedEdits
ELSE 200
END
)
FROM #DayTotals
);
Final query
Now we have all the elements we need to produce the final output:
SELECT
TotalReputation =
@ReputationFromRepCap +
SUM(DT.ReputationFromBounties) +
@RepBountiesGiven +
@RepFromAcceptingAnswers +
SUM(DT.ReputationFromAccepts),
ReputationFromRepCap = @ReputationFromRepCap,
ReputationFromBounties = SUM(DT.ReputationFromBounties),
ReputationGivenAsBounties = @RepBountiesGiven,
ReputationFromAcceptingAnswers = @RepFromAcceptingAnswers,
ReputationFromAcceptedAnswers = SUM(DT.ReputationFromAccepts)
FROM #DayTotals AS DT;
Try the complete script at: SEDE Demo
