3
\$\begingroup\$

There is a 1-to-many relationship between the posts table and the votes table, and they join on posts.id = votes.postid. I would like to know how many posts have 1, 2, 3, 5, etc. votes.

Thanks to Waffles' most controversial posts query, it was trivial to write this:

declare @VoteStats table (PostId int, up int) 

set nocount on

insert @VoteStats
select
    PostId, 
    up = sum(case when VoteTypeId = 2 then 1 else 0 end)
from Votes
where VoteTypeId in (2,3)
group by PostId

set nocount off

select up, count(*)
from @VoteStats
group by up;

However, it runs in a full 22 seconds on the Stack Overflow data dump.

Am I overlooking an obvious way to speed this query up, perhaps avoiding the creation of the VoteStats table?

\$\endgroup\$
  • 1
    \$\begingroup\$ Is there some reason you can't restrict your where clause to where VoteTypeId = 2 and then just count(*) the rows? That's essentially what you're currently asking. Or do you also have a down column that goes the other way? \$\endgroup\$ – Clockwork-Muse Sep 7 '11 at 17:47
  • \$\begingroup\$ @X-Zero That only shoved off one second, but makes a lot of sense. Plus, it removed a subtle problem with showing a count of posts with "0 votes" (they actually aren't all posts with no votes). I should've totally seen that \$\endgroup\$ – badp Sep 7 '11 at 17:51
  • \$\begingroup\$ Then that's probably as fast as it's going to get... \$\endgroup\$ – Clockwork-Muse Sep 7 '11 at 17:57
2
\$\begingroup\$

Not creating the VoteStats table makes a big difference. Gets it down to ~5.5 seconds.

set nocount on

select up, count(*)
from (
    select
        PostId,
        up = sum(case when VoteTypeId = 2 then 1 else 0 end)
    from Votes
    where VoteTypeId in (2,3)
    group by PostId
) a
group by up
order by up

http://data.stackexchange.com/stackoverflow/q/112108/

From the execution plan of the table variable version, it looks like the table scan and the sort on it is pretty expensive.

\$\endgroup\$
  • \$\begingroup\$ ...and combining this with the other answer cuts the time down to 2 seconds. Pesky syntax! :D \$\endgroup\$ – badp Sep 8 '11 at 22:45
2
\$\begingroup\$

I think this should be faster, because instead of summing up 1 for every vote that has a VoteTypeId=2, I counted votes with VoteTypeId=2:

with VoteCountsByPost as
(
    select 
        p.Id as PostId,
        count(v.Id) as up
    from Posts p
    left join Votes v on v.PostId = p.Id and v.VoteTypeId = 2
    group by p.Id
)
select up, count(*) 
from VoteCountsByPost
group by up
order by up

Also, please note that the results are a little different: you query does not take into account posts that have no votes.

For instance, if a post has a vote with VoteTypeId != 2, then that post will appear with 0 ups. If it has no votes whatsoever, then that post will be completely excluded from the result set.

I don't know if this is the intended behavior.

\$\endgroup\$
  • \$\begingroup\$ Yeah, this is what X-Zero suggested. See the comments on the question for my follow up. :) \$\endgroup\$ – badp Sep 8 '11 at 22:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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