# Amount of sql queries

**URL:** <https://www.ruby-forum.com/t/amount-of-sql-queries/135876>\
**Category:** Rails\
**Created:** [April 25, 2008, 8:58pm UTC](https://www.ruby-forum.com/t/amount-of-sql-queries/135876 "2008-04-25T20:58:59Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![fox](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/c5a1d2/32.png) [@fox](https://www.ruby-forum.com/u/fox)\
**Post date:** [April 25, 2008, 8:58pm UTC](https://www.ruby-forum.com/t/amount-of-sql-queries/135876/1 "2008-04-25T20:58:59Z")

</div>

hi all,  
looking at my development log, i can see rails generating a huge amount  
of queries .

my models are set up as follows:  
user 1-m item 1-m item\_document

the relationships are has\_many\_through, with user\_items, item\_documents  
join tables.

a video which renders 10 users will generate 60 + queries.

how do you guys design things when there’s a few levels of  
normalization?

thanks,  
paul.

---

<div class="post-metadata">

**Author:** ![fox](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/c5a1d2/32.png) [@fox](https://www.ruby-forum.com/u/fox)\
**Post date:** [April 25, 2008, 9:20pm UTC](https://www.ruby-forum.com/t/amount-of-sql-queries/135876/2 "2008-04-25T21:20:17Z")

</div>

there are a few tricks to reduce the amount of queries necessary.  
one (the most important) is eager loading of associations.  
example:  
user has\_many :videos

then if you run a find on user:  
users = User.find(:all, :include =\> [:videos])

this will generate a single joined query and load  
and it will not generate any further queries when using something like:  
users.videos.each do |video|  
…

---

<div class="post-metadata">

**Author:** ![fox](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/c5a1d2/32.png) [@fox](https://www.ruby-forum.com/u/fox)\
**Post date:** [April 26, 2008, 12:50am UTC](https://www.ruby-forum.com/t/amount-of-sql-queries/135876/3 "2008-04-26T00:50:53Z")

</div>

Paul F. wrote:

> looking at my development log, i can see rails generating a huge amount  
> of queries .
> 
> my models are set up as follows:  
> user 1-m item 1-m item\_document
> 
> the relationships are has\_many\_through, with user\_items, item\_documents  
> join tables.
> 
> a video which renders 10 users will generate 60 + queries.

Your top-level queries should eager-load, like this in a controller  
action:

@frogs = Frog.find\_all\_by\_genera(genera.id, :include =\> [:species,  
:habitat])

The :include pulls in everything your page needs - if your page needs  
all the  
species and habitat records for a given set of frogs. You can also add  
:conditions to narrow them down. They all come in with one big SELECT  
result;  
not a bunch of little ones.

> how do you guys design things when there’s a few levels of  
> normalization?

I use assert\_efficient\_sql, in unit tests, in this mode:

```
def test_frog_roster_is_efficient
  report = assert_efficient_sql :ALL => true, :Using_filesort => 

```

true do  
clade = get\_example\_clade  
get :frog\_roster, :clade\_id =\> clade.id  
end  
report.pretty\_inspect.split(“\n”).each do |line|  
deny(‘eeeeger load genera’ ){ line =~ /SELECT \* FROM genera/ }  
deny(‘eeeeger load species’){ line =~ /SELECT \* FROM species/ }  
end  
assert{ report.length \< 30 }  
end

That is a Rails functional test calling the frog\_roster page. The test  
asserts  
that the entire page does not call more than 30 SQL statements, and  
asserts that  
we eager-loaded the genera and species for the sample clade of frogs. We  
didn’t  
write extra SELECT statements for those - the SELECT for the clade  
itself would  
have pulled them all in for us.

The general goal is frog\_roster is safe for us to develop. We can change  
the  
innards of frog\_roster, assured that the customer has reviewed the  
page’s  
performance and likes it, and that our changes will not exceed these  
bounds.

This example shows assert\_efficient\_sql in its coarsest mode - the  
general  
metrics for an entire page. I can also put it around a single unit test,  
to show  
that a single method call only produces optimized SQL statements.

To make sure you eager load, no matter how often you call  
frog.species.each…  
or frog.habitats.each…, set the assert{ report.length \< 30 } to some  
comfortably low number.

–  
Phlip  
[http://assert2.rubyforge.org/](http://assert2.rubyforge.org/)