Database optimization (ways to decrease the number of sql selects)

Rails
#1

Is there any way to optimize (decrease) SQL selects without using pure
SQL (find_by_sql command)?

Example: I have persons - professions (many to many relation), with
only id and name columns to both tables.

  1. When a use this:

  • controller
    @persons = Person.find :all

  • view
    <% @persons.each do |person| %>
    <%= person.name %> |
    <%= person.proffesions.count %>

    <% end %>

Mongrel shows that there are N+1 selects to the database. One for
“select * from people”, and N selects for the count professions to
specific person’s id.

  1. But, when a use this:

  • controller
    @persons = Person.find_by_sql “select , (select count() from
    people_professions where people_professions.person_id = people.id ) as
    pro_number from people”

  • view
    <% @persons.each do |person| %>
    <%= person.name %> |
    <%= person.pro_number %>

    <% end %>

Mongrel shows that there is only 1 select to the database, and it goes
much faster.

How can I do this by using ActiveRecord syntax and not direct sql
selects?

#2

On 1/9/08, blackflash [email protected] wrote:

@persons = Person.find :all

snip

How can I do this by using ActiveRecord syntax and not direct sql
selects?

Look for “eager loading” under
ActiveRecord::Associations::ClassMethods in the API docs.

Isak

#3

On 9 Jan 2008, at 10:17, Isak H. wrote:

  1. When a use this:
    Mongrel shows that there are N+1 selects to the database. One for

And in this particular case consider also a counter cache. (since
eager loading will actually fetch all of the professions and
instantiate them, rather than just fetching the number of professions)

You could also do Person.find :all, :conditions => …, :select =>
‘people., (select count() from people_professions where
people_professions.person_id = people.id ) as pro_number’
although that is of course much the same as doing the find_by_sql
Lastly if the best way is using find_by_sql, don’t be afraid to use
it. There are times when it’s the right tool for the job.

Fred

#4

Model.find :all, :include => :child

#5

Quoting blackflash [email protected]:

@persons = Person.find :all

  • view
    <% @persons.each do |person| %>
    <%= person.name %> |
    <%= person.proffesions.count %>

    <% end %>

count() by definition generates an SQL “SELECT COUNT(*) FROM …”
operation.
What you probably want is “person.proffessions.size()” which is the
number of
elements in the array, assuming it is eagerly loaded. Or if
proffessions is
not loaded, try a counter_cache.

HTH,
Jeffrey

#6

Jeffrey L. Taylor wrote:

  • controller
    count() by definition generates an SQL “SELECT COUNT(*) FROM …” operation.
    What you probably want is “person.proffessions.size()” which is the number of
    elements in the array, assuming it is eagerly loaded. Or if proffessions is
    not loaded, try a counter_cache.

HTH,
Jeffrey

Also you could write this out as:

@persons = Person.find(:all, :include => :proffessions)

which will do a join, which will inheritly use more memory, although it
will only perform one trip to the database.

@persons.proffessions.count will then return to result of the rows
counted.