Rails - Nov 2007 - find_by_sql preformance problems when ordering data

Hi,

I am bumping into a small problem I was hoping someone could send me in
the right direction on.

I''m counting votes from a votes table to try to find the top ten
videos.
The following works really well, until I try to order it.  It would seem
that the order by is adding a 26 second delay, which surprised me since
it was all done in sql.  I revised the process to sort and publish on
the top 10 items in rails, which has gotten the delay down to about 5-8
seconds.

Video.find_by_sql("select videos.*, (select count(*) from votes where
video_id = videos.id and value = 1) - (select count(*) from votes where
video_id = videos.id and value = -1) AS total_votes from videos ORDER BY
total_votes DESC LIMIT 10")


Is there some better way to order these results that wouldn''t be more
resource intensive.  I was looking around to see if maybe my total_votes
alias could be clearly defined as an integer, which I though might help
speed up the order by.  I haven''t been able to find anything thus far.

Any help would be appreciated!
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Hi,

Apart from suggesting that you check that you have indexes on the
obvious places (id, value), I would recommend changing your app to do
either

(a) using a select sum(value) on the id field

(b) don''t store the votes directly: change the logic of your
application to update running totals.

You could make it safe by not using a cache and writing the updates
directly to the database - in which case you would only get the
performance
boost when retrieving the score

or

You could go the whole hog and have a distributed memcache and just
periodically update the total


Option (a) is likely to be the correct choice unless you have very
large volumes.

HTH

Chris

On Nov 30, 9:00 am, Mario Flores
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:
> Hi,
>
> I am bumping into a small problem I was hoping someone could send me in
> the right direction on.
>
> I''m counting votes from a votes table to try to find the top ten
videos.
> The following works really well, until I try to order it.  It would seem
> that the order by is adding a 26 second delay, which surprised me since
> it was all done in sql.  I revised the process to sort and publish on
> the top 10 items in rails, which has gotten the delay down to about 5-8
> seconds.
>
> Video.find_by_sql("select videos.*, (select count(*) from votes where
> video_id = videos.id and value = 1) - (select count(*) from votes where
> video_id = videos.id and value = -1) AS total_votes from videos ORDER BY
> total_votes DESC LIMIT 10")
>
> Is there some better way to order these results that wouldn''t be
more
> resource intensive.  I was looking around to see if maybe my total_votes
> alias could be clearly defined as an integer, which I though might help
> speed up the order by.  I haven''t been able to find anything thus
far.
>
> Any help would be appreciated!
> --
> Posted viahttp://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

>
> (a) using a select sum(value) on the id field
>
That was badly worded what I mean is that you can sum all of the
values for a particular "videos_id"  rather than separately summing
the plus votes and the minus votes.

You should also try to avoid that (potentially) big join by getting
the top ten votes and then selecting the corresponding records from
the videos table.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---