Find and eager loading questions


#1

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 :wink: )

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


#2

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.)


#3

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.


#4

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.


#5

On 3/13/06, Cheltis 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.