Forum: Ruby on Rails Backwards N+1 problem

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
71ee1809f53df0a2b4dac0530f630933?d=identicon&s=25 Richard Schneeman (snowmaninthesun)
on 2008-10-28 01:33

Hey, i’m running into an N+1 problem, but i don't exactly know how to
:include in this situation. (controller code is at the bottom).

 I'm trying to find all of the highest ranked definitions, and then
render their associated phrases.

A Phrase has many definitions. A Definition has many children

I already have all the info i need stored in @definitions, so this
should only require one SQL call, but unfortunately this line:

 @Phrases << definition.phrase(:include => [:definitions => :children])

Doesn’t actually work.

Below is my controller code. Rails 2.1.0, Ruby 1.8.6…Thanks ahead of
time

   def index
      @definitions = Definition.find(:all, :include => [:children,
:phrase], :limit => 10, :order => 'rank DESC')
       @Phrases = []
       for definition in @definitions
         @Phrases << definition.phrase(:include => [:definitions =>
:children])
       end
       @Phrases = @Phrases.uniq
       if @Phrases != nil
         @new_phrases = @Phrases.paginate(:page => params[:page], :order
=> 'word ASC', :per_page => 5)
       end ## if @phrase !=nil
    end ##def index


The full capture can be found under my Five Runs Account
https://tuneup.fiveruns.com/runs/699

If you have any suggestions i would love for my main page to not take
5000 milliseconds to load : )
81b61875e41eaa58887543635d556fca?d=identicon&s=25 Frederick Cheung (Guest)
on 2008-10-28 10:40
(Received via mailing list)
On 28 Oct 2008, at 00:33, Richard Schneeman wrote:

>
> I already have all the info i need stored in @definitions, so this
> should only require one SQL call, but unfortunately this line:
>
> @Phrases << definition.phrase(:include => [:definitions => :children])
>

An easy way - Phrase.find :all, :include => [...], :conditions =>
["definitions.phrase_id in (?)", @definitions]

or something like
@definitions = Definition.find :all, :include => [:phrase =>
{:definitions => :children}], :order => 'rank desc', :limit => 10
@phrases = @definitions.collect {|definition| definition.phrase}

might do the trick.

Fred
71ee1809f53df0a2b4dac0530f630933?d=identicon&s=25 Richard Schneeman (snowmaninthesun)
on 2008-10-29 04:17
That knocked off about 1000ms, thanks for the help! Is it possible to
re-arrange the order of your database all together??

When I take out the :order => 'rank desc' my sql call time gets cut in
half, is there any way to tell RoR to automatically sort my definitions
(in the DB) by  rank descending??

I already have this code in my phrase.rb

"has_many :definitions, :order => 'rank DESC'"

but is it possible to have an entire model be automatically ordered by a
specific column inside of the database?? In this case by rank.
81b61875e41eaa58887543635d556fca?d=identicon&s=25 Frederick Cheung (Guest)
on 2008-10-29 10:11
(Received via mailing list)
On 29 Oct 2008, at 03:17, Richard Schneeman
<rails-mailing-list@andreas-s.net
 > wrote:

>
> "has_many :definitions, :order => 'rank DESC'"
>
> but is it possible to have an entire model be automatically ordered
> by a
> specific column inside of the database?? In this case by rank.

Nope. Or rather at the end of the day the database still needs to sort
it. An index on the column you are ordering by usually helps.

Fred.
This topic is locked and can not be replied to.