Backwards N+1 problem


#1

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 : )


#2

On 28 Oct 2008, at 00:33, Richard S. 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


#3

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.


#4

On 29 Oct 2008, at 03:17, Richard S.
<removed_email_address@domain.invalid

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.