Rails - Jan 2008 - 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?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On 1/9/08, blackflash
<dalibor.nasevic-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
>
> 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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On 9 Jan 2008, at 10:17, Isak Hansen wrote:
>
> On 1/9/08, blackflash
<dalibor.nasevic-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>>
>> 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

>
> Isak
>
> >

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Quoting blackflash
<dalibor.nasevic-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>:
> 
> 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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Model.find :all, :include => :child
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Jeffrey L. Taylor wrote:
> Quoting blackflash
<dalibor.nasevic-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>:
>   
>> 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.
> >
>   

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---