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

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 %><br/>
<% 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.

2) 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 %><br/>
<% 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?

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 %><br/>
<% 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.

*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

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 %><br/>
<% 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.

*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.

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

Quoting blackflash <dalibor.nasevic@gmail.com>:

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 %><br/>
<% 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

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

Jeffrey L. Taylor wrote:

Quoting blackflash <dalibor.nasevic@gmail.com>:
  

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 %><br/>
<% 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
  

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.