Hi,
I am doing an eager loading in ruby on rails using below statement
------------------------
Article.find(:all, :include => [:asset, :vote],
:conditions=>"assets.parent_id is null",
:order=>"stat_final_ranking desc", :limit=>20)
---------------------------------------
the above statement resulted in this expensive query. After indexing, it
take about 3 seconds to execute. (before indexing it is about 25
seconds)
SELECT DISTINCT articles.id
FROM articles LEFT OUTER JOIN assets ON assets.attachable_id articles.id AND
assets.attachable_type = ''Article''
LEFT OUTER JOIN votes ON votes.voteable_id = articles.id AND
votes.voteable_type = ''Article''AND user_id = 2
WHERE (assets.parent_id is null)
ORDER BY stat_final_ranking desc
LIMIT 20
-----------------------------------
so, what I plan to do it is to cache the expensive query without
including vote table
Article.find(:all, :include => [:asset],
:conditions=>"assets.parent_id is null",
:order=>"stat_final_ranking desc", :limit=>20)
then later combine it with vote table which is different for each user.
Is the method to cache the expensive query the right approach?
Also, I have another question how to combine @articles and @vote after
getting it from database.
@articles= Article.find(:all, :include => [:asset],
:conditions=>"assets.parent_id is null",
:order=>"stat_final_ranking desc", :limit=>20)
with
@votes = Vote.find(:all, :conditions=> "user_id" ....
Thanks in advance,
--
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
-~----------~----~----~----~------~----~------~--~---
Beta Beta wrote:> Hi, > > I am doing an eager loading in ruby on rails using below statement > ------------------------ > Article.find(:all, :include => [:asset, :vote], > :conditions=>"assets.parent_id is null", > :order=>"stat_final_ranking desc", :limit=>20) > --------------------------------------- > the above statement resulted in this expensive query. After indexing, it > take about 3 seconds to execute. (before indexing it is about 25 > seconds)One way forward with this non-caching option would be to only load the votes of the current user. This can be done with a trick that dynamically sets the :conditions option on the vote association.> so, what I plan to do it is to cache the expensive query without > including vote table > Article.find(:all, :include => [:asset], > :conditions=>"assets.parent_id is null", > :order=>"stat_final_ranking desc", :limit=>20) > > then later combine it with vote table which is different for each user. > > Is the method to cache the expensive query the right approach? > > Also, I have another question how to combine @articles and @vote after > getting it from database. > @articles= Article.find(:all, :include => [:asset], > :conditions=>"assets.parent_id is null", > :order=>"stat_final_ranking desc", :limit=>20) > with > @votes = Vote.find(:all, :conditions=> "user_id" ....You want the votes to be hashed by article id, which the above would not give you unless you hashed it after retrieval. The alternative is to do an Article query like your original one, but leaving out the asset association, and, for speed, only selecting the id of the article model. Now you can include the user_id condition because don''t care if the articles the user hasn''t voted for are dropped. Id-only selection would however require use of plugins that allow use of the find :select option in combination with eager loading of the votes. -- We develop, watch us RoR, in numbers too big to ignore. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Thanks Mark for the feedback, you did bring good point in "id-only selection". Here is what I am trying to achieve: Article1 -- Vote1(by current user) Asset1, Asset2, Asset3 Article2 -- Vote2 (by current user) Asset3, Asset4 I did have eager loading query to only load votes of the current user. So that''s why I want to separate it out. Article and assets most likely will stay the same and it is good candidate to cache it. However, votes by user will keep changing. If I use this method, I still need to figure out how to combine the cache (Article and Assets) with the votes for current user.> The alternative is to do an Article query like your original one, > but leaving out the asset association, and, for speed, only selecting > the id of the article model. Now you can include the user_id > condition because don''t care if the articles the user hasn''t voted > for are dropped.for your alternative, If I leave out the Assets, I will not be able to get the asset arrays. I think it is pretty late night right now. May be tomorrow, I will get more ideas. :) Thanks again for the feedback -- 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 -~----------~----~----~----~------~----~------~--~---
Mark Reginald James wrote:> The alternative is to do an Article query like your original one, > but leaving out the asset association, and, for speed, only selecting > the id of the article model. Now you can include the user_id > condition because don''t care if the articles the user hasn''t voted > for are dropped. > > Id-only selection would however require use of plugins that allow use of > the find :select option in combination with eager loading of the votes.Now your alternative make sense. It speed up the performance a lot. Thanks again, Beta -- 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 -~----------~----~----~----~------~----~------~--~---