Hi all,
I want to see if I''m not missing something with associations and eager
loading. (I''m currently using Locomotive on OS X and SQLite3.)
I have two simple tables/models, set up properly: teams has_many
:matches and matches belongs_to :teams. Matches has a match number and
a team_id field (along with several other fields) and teams has a
team_number field (again, along with several other fields.)
I want to get a list of all the matches with only one piece of data
from teams (there are about 360 rows in the matches table).
I did this and it works, but is a little slow:
@matches = Match.find(:all, :order => ''number'')
and I loop through it in the view and display the match number and the
team number, along with the usual "show", "edit", and
"destroy" links.
This works, but in the log it generates a SQL Select statement for
each line - 360 times. If I change to:
@matches = Match.find(:all, :include => ''team'', :order
=> ''number'')
it only generates one SQL call, but it is including all fields from
both tables and doesn''t reduce the time to generate the page (which is
on the order of almost 10 seconds - remember only 360 records.)
Now, if I change to this:
@matches = Match.find_by_sql(''select matches."id",
teams."team_number", matches."number" from matches left
outer join
teams on teams.id = matches.team_id order by number'')
it works much faster (though I did have to change how I accessed the
team number from "match.team.team_number" to
"match.team_number" -
less code is good ;) )
I''m just curious if I''m missing something that would make a
"plain"
find not return all of the fields. I tried using :select, but that
didn''t make a difference, nor change the actual SQL call.
Thanks for any info!
jt
if you take a generated query from your log and run it manually, does it still takes 10 seconds to respond? your approach is correct and although you cannot choose a subset of the fields in joined table, it should not take that long John Tsombakos wrote:> @matches = Match.find(:all, :include => ''team'', :order => ''number'') > > it only generates one SQL call, but it is including all fields from > both tables and doesn''t reduce the time to generate the page (which is > on the order of almost 10 seconds - remember only 360 records.) >-- Posted via http://www.ruby-forum.com/.
I did some testing and see in the logs that when I use the first method (not using the :include option) I see: Match Load (0.952378) SELECT * FROM matches ORDER BY number, alliance Rendering within layouts/matches Rendering matches/list SQL (0.011250) PRAGMA table_info(matches) Team Load (0.004108) SELECT * FROM teams WHERE (teams.id = 2) LIMIT 1 SQL (0.021775) PRAGMA table_info(teams) Team Load (0.004313) SELECT * FROM teams WHERE (teams.id = 13) LIMIT 1 (repeat 360 times...) Completed in 9.92100 (0 reqs/sec) | Rendering: 5.66656 (57%) | DB: 4.06175 (40%) When using the :include, only one SQL call is made, including all of the fields of the two tables (about 38 in one, and 21 in the other) and get: Completed in 6.81599 (0 reqs/sec) | Rendering: 2.62585 (38%) | DB: 2.38022 (34%) Running the SQL query using the sqlite command line, the query comes back very fast. Using the custom find_by_sql gets: Completed in 2.09801 (0 reqs/sec) | Rendering: 1.36840 (65%) | DB: 0.15935 (7%) Much faster... Thanks. On 3/13/06, Cheltis <cheltis@gmail.com> wrote:> if you take a generated query from your log and run it manually, does it > still takes 10 seconds to respond? > > your approach is correct and although you cannot choose a subset of the > fields in joined table, it should not take that long > > John Tsombakos wrote: > > @matches = Match.find(:all, :include => ''team'', :order => ''number'') > > > > it only generates one SQL call, but it is including all fields from > > both tables and doesn''t reduce the time to generate the page (which is > > on the order of almost 10 seconds - remember only 360 records.) > > > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
38 + 21 fields - that''s quite a few! if your database design is correct then find_by_sql seems to be the only appropriate option John Tsombakos wrote:> When using the :include, only one SQL call is made, including all of > the fields of the two tables (about 38 in one, and 21 in the other) > and get: > > Completed in 6.81599 (0 reqs/sec) | Rendering: 2.62585 (38%) | DB: > 2.38022 (34%) > > Running the SQL query using the sqlite command line, the query comes > back very fast. > > Using the custom find_by_sql gets: > > Completed in 2.09801 (0 reqs/sec) | Rendering: 1.36840 (65%) | DB: > 0.15935 (7%) > > Much faster... > > Thanks.-- Posted via http://www.ruby-forum.com/.
On 3/13/06, Cheltis <cheltis@gmail.com> wrote:> 38 + 21 fields - that''s quite a few! > > if your database design is correct then find_by_sql seems to be the only > appropriate option >Yeah.. there''s a lot of info per record that needs to be recorded. Find_by_sql works well, so I''ll just keep with that. Thanks.