# Find and eager loading questions **URL:** \ **Category:** Rails\ **Created:** [March 13, 2006, 5:35am UTC](https://www.ruby-forum.com/t/find-and-eager-loading-questions/54689 "2006-03-13T05:35:50Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![johnt519](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/cab0a1/32.png) [@johnt519](https://www.ruby-forum.com/u/johnt519)\ **Post date:** [March 13, 2006, 5:35am UTC](https://www.ruby-forum.com/t/find-and-eager-loading-questions/54689/1 "2006-03-13T05:35:50Z")
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 ---
**Author:** ![johnt519](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/cab0a1/32.png) [@johnt519](https://www.ruby-forum.com/u/johnt519)\ **Post date:** [March 13, 2006, 7:14am UTC](https://www.ruby-forum.com/t/find-and-eager-loading-questions/54689/2 "2006-03-13T07:14:39Z")
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 T. 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.) ---
**Author:** ![johnt519](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/cab0a1/32.png) [@johnt519](https://www.ruby-forum.com/u/johnt519)\ **Post date:** [March 13, 2006, 8:03am UTC](https://www.ruby-forum.com/t/find-and-eager-loading-questions/54689/3 "2006-03-13T08:03:25Z")
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. ---
**Author:** ![johnt519](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/cab0a1/32.png) [@johnt519](https://www.ruby-forum.com/u/johnt519)\ **Post date:** [March 13, 2006, 8:06am UTC](https://www.ruby-forum.com/t/find-and-eager-loading-questions/54689/4 "2006-03-13T08:06:27Z")
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 T. 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. ---
**Author:** ![johnt519](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/cab0a1/32.png) [@johnt519](https://www.ruby-forum.com/u/johnt519)\ **Post date:** [March 13, 2006, 8:21am UTC](https://www.ruby-forum.com/t/find-and-eager-loading-questions/54689/5 "2006-03-13T08:21:41Z")
On 3/13/06, Cheltis [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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.