Amount of sql queries

Rails
1

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.

2

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|
…

3

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/