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

rubyonrails-talk
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 %><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?

2

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

3

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

4

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

5

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

6

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.