# Database optimization (ways to decrease the number of sql selects) **URL:** \ **Category:** Rails\ **Created:** [January 9, 2008, 10:55am UTC](https://www.ruby-forum.com/t/database-optimization-ways-to-decrease-the-number-of-sql-selects/125465 "2008-01-09T10:55:49Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![blackflash](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/dfb087/32.png) [@blackflash](https://www.ruby-forum.com/u/blackflash)\ **Post date:** [January 9, 2008, 10:55am UTC](https://www.ruby-forum.com/t/database-optimization-ways-to-decrease-the-number-of-sql-selects/125465/1 "2008-01-09T10:55:49Z")
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? ---
**Author:** ![blackflash](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/dfb087/32.png) [@blackflash](https://www.ruby-forum.com/u/blackflash)\ **Post date:** [January 9, 2008, 11:18am UTC](https://www.ruby-forum.com/t/database-optimization-ways-to-decrease-the-number-of-sql-selects/125465/2 "2008-01-09T11:18:17Z")
On 1/9/08, blackflash [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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 ---
**Author:** ![blackflash](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/dfb087/32.png) [@blackflash](https://www.ruby-forum.com/u/blackflash)\ **Post date:** [January 9, 2008, 11:38am UTC](https://www.ruby-forum.com/t/database-optimization-ways-to-decrease-the-number-of-sql-selects/125465/3 "2008-01-09T11:38:11Z")
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 ---
**Author:** ![blackflash](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/dfb087/32.png) [@blackflash](https://www.ruby-forum.com/u/blackflash)\ **Post date:** [January 18, 2008, 1:06am UTC](https://www.ruby-forum.com/t/database-optimization-ways-to-decrease-the-number-of-sql-selects/125465/4 "2008-01-18T01:06:11Z")
Model.find :all, :include =\> :child ---
**Author:** ![blackflash](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/dfb087/32.png) [@blackflash](https://www.ruby-forum.com/u/blackflash)\ **Post date:** [January 18, 2008, 12:58am UTC](https://www.ruby-forum.com/t/database-optimization-ways-to-decrease-the-number-of-sql-selects/125465/5 "2008-01-18T00:58:49Z")
Quoting blackflash [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid): > @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 ---
**Author:** ![blackflash](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/dfb087/32.png) [@blackflash](https://www.ruby-forum.com/u/blackflash)\ **Post date:** [January 18, 2008, 1:46am UTC](https://www.ruby-forum.com/t/database-optimization-ways-to-decrease-the-number-of-sql-selects/125465/6 "2008-01-18T01:46:09Z")
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.