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.
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| ...
Paul Fox 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.