6
\$\begingroup\$

So this morning, I decided to create a SEDE query that answers the age-old question "How close am I to being Unsung?" because I recently polished up my first SEDE query and I realized that I had enjoyed learning how to code in SQL.

I have tested that the code works as intended1, however there I would like to know if there is a way I could improve my code's performance in terms of time.

What this does is output the # of accepted answers from the UserId that have 0 score, are accepted, and are not self-answers or community wikis. run online

SELECT
    COUNT(a.Id) as [Accepted Answers],
    (10 - SUM(CASE WHEN a.Score = 0 THEN 1 ELSE 0 END)) AS [Answers till Unsung]
FROM
    Posts q
INNER JOIN
    Posts a on a.Id = q.AcceptedAnswerId
WHERE
    a.CommunityOwnedDate IS NULL
AND a.OwnerUserId = ##UserId##
AND q.OwnerUserId != ##UserId##
AND a.PostTypeId = 2

My question is: is there anything that I could do to make sure that the query doesn't take as much time to run the first time around? It really doesn't make sense that the code would run slowly the first time when I enter a user ID but not the second time when the info is already cached.


The query returning negative numbers when putting in the IDs of users who have more than 10 accepted answers with a score of 0 is perfectly intentional.


Notes


1Although it was pretty hard to get done as there weren't a lot of sites where the Unsung Hero badge had even been attained. And even then, I had to make sure that there were users who had the badge whose answers had not been upvoted since then.

\$\endgroup\$

1 Answer 1

6
\$\begingroup\$

I tried it on myself, elapsed time of 136 msec for userid 145459: https://data.stackexchange.com/codereview/query/1822723/how-close-am-i-to-being-unsung?opt.withExecutionPlan=true#executionPlan

The plan is extremely easy to read. Nearly all execution nodes show approximately 0% cost, with an isolated leaf node showing

Clustered Index Scan [PostsWithDeleted].[UIX_PostsWithDe…

Cost: 97%

The estimated to actual row ratio is not too bad: 1716 / 264 == 6.5.
So the planner manages to get it within an order-of-magnitude, about par for the course, sufficient for deciding which access path to choose. Estimated subtree cost at that node is 69, which I guess is in units of millisecond, reasonably close to the observed 136.

Substantially all of the expense was for dealing with the a.Id = q.AcceptedAnswerId equi-join, and it seems entirely appropriate to me. I see no inefficiencies in that.

a way I could improve my code's [elapsed] time

tl;dr: No, not that I see.

It would be interesting to publish some "hard" instances of this query. That is, against a bigger site such as SO, and with a more prolific user.

Another way to make this a harder query would be to choose the top ten (or a hundred) users, and produce one result row per user.

cached behavior

Re-running the identical query unsurprisingly shrinks elapsed time from 136 msec down to 2 msec. So access to the I/O subsystem accounts for most of the expense -- CPU cycles to analyze cached rows is trivial cost.

magic number

AND a.PostTypeId = 2

Minimally this warrants a -- comment giving the symbolic name of 2. Ideally we would JOIN against a third table, to recover 2 from a symbolic name, at the cost of maybe an extra millisecond.

meaningful identifiers

The result columns enjoy admirable clarity in their self-explanatory names.

\$\endgroup\$
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.