Module is running _votingapi_select recursively when building vote up/down widget.

When loading node with lots of comments, displaying 300 comments per page, I get vote up/down calling _votingapi_select 1806 times.

This happens in vud_widget_proxy() in vud.theme.inc.
votingapi_select_single_result_value being() called 4 times + one votingapi_select_single_vote_value(). Module selects: (1) user_vote, (2) sum, (3) count, (4) positives and (5) negatives (in my widget I actually use only sum).
Plus one more votingapi_select_single_result_value() call more in vud_votes_proxy() (again for sum).
In total that will be 6 queries.

votingapi_select_single_result_value makes a SQL query each time it is called so that's will be 6 times x 300 comments = 1800 queries + 6 queries from node widget calls = 1806 SQL queries!
Consider, that when displaying 50 comments per page Vote Up/Down makes 306 extra queries. This is really dramatic.

This must be optimized or some caching mechanism needs to be added to be used with memcache.

Comments

marvil07’s picture

Title: Performance: more than 1800 SQL queries per node load with 300 comments per page » Calculate sum and count only for widgets using them
Version: 6.x-2.8 » 6.x-3.x-dev
Category: bug » feature
Issue tags: +Performance

I see the problem.

Please use 6.x-3.x branch last release. There positive and negatives are calculated by the alter template variables widget callback on the needed widgets(See #968630: Let widgets modify template variables, only on 6.x-3.x). I am not really planning to support 6.x-2.x much longer, so the change should be on 6.x-3.x

The same principle can be applied for sum and count, so I am completely in favour of this change(i.e. plain widget do not show any of those).

Patches welcome!

Ainur’s picture

Ok I'll try it.

Ainur’s picture

Hi marvil07,
so I’m trying 6.x-3.x version, but when I’m trying to alter template variables, I get variables overridden but it looks that I’m also getting 2 queries. First the original one then another one via override.

marvil07’s picture

@Ainur, sorry for the late response. I think I need to be a little more specific, what I meant was to remove sum and count from the general widget processing, and then add it as the positive/negative variables for each widget needing it.

BTW You always want to add a patch, even if it's not finished, so others can help too ;-)

marvil07’s picture

Issue summary: View changes
Status: Active » Closed (outdated)

Closing D6 issues, D6 releases are not supported now.
Please reopen as D7 if needed.