Showing public poll results = damn slow?

[Andreas]

It seems like displaying results of public posts on my board is damn slow.

Looking at the queries I found that for each option there is one query, which takes kinde long to execute.

Example:


SELECT user.userid,user.username
FROM pollvote AS pollvote, user AS user
WHERE user.userid = pollvote.userid AND
pollid = '1832' AND
voteoption = '1'
ORDER BY username ASC

Time before: 0.42810201644897
Memory Before: 698KB
Time after: 0.87274694442749
Time taken: 0.44464492797852
Memory After: 698KB


Now let's say there are 10 options. Together with the time for the other queries and PHP processing this accumulates to 5+ seconds to generate the page!

Any idea as to why these queries are that slow and how this could be optimized (vB3)?

[Xenon]

Hmm, it isn't that slow on my testboard here, but i threw together a little optimization.

can you test it for me and post the results, i'll then release it in the optimization forum :)

(attachment can now be found: https://vborg.vbsupport.ru/showthread.php?t=66694)

[Andreas]

Although my code (which I wrote in the meanwhile) is slightly differnt it is basically the same as yours (without the USING part) :)

And this gave a HUGE performance boost: Page generation time went down approx. 5 seconds for the poll I tested.

[Xenon]

:)

perfect, then i can release it :)

[Trigunflame]

Bad mistake on part of the Vbulletin Dev's. Report it to the bugtracker if you already havn't.

[Xenon]

well, everyone can make a mistake :)

even so, a query in a loop is a real big mistake, but hey, noones perfect. i'm sure it will be fixed in vb302 ^^

[Brad]

Good bug find!