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.
- When a use this:
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.
- 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?
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
On 9 Jan 2008, at 10:17, Isak H. wrote:
- 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
Model.find :all, :include => :child
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
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.