How bad is it to have 7 joins in my find_by_sql?

My question is whether there’s a more idiomatic rails way of
structuring this query or of redoing the underlying models. First,
the ugly find_by_sql code, which is the method to generate an atom feed:

def atom
items_per_feed = 15
sql_query = “SELECT activities.*, users.real_name AS real_name,
accounts.last_scraped_at AS last_scraped_at,
accounts.total_miles as account_miles,
origins.name AS origin_name, origins.code AS origin_code,
destinations.name
AS destination_name, destinations.code AS destination_code,
airlines.name
AS airline_name, airlines.code AS airline_code, programs.name
AS program_name,
membership_levels.name AS membership_level FROM activities
LEFT OUTER JOIN accounts ON activities.account_id = accounts.id
LEFT OUTER JOIN
users on accounts.user_id = users.id LEFT OUTER JOIN
airports AS origins ON activities.origin_id = origins.id LEFT
OUTER JOIN airports
AS destinations ON activities.destination_id = destinations.id
LEFT OUTER JOIN
airlines ON activities.airline_id = airlines.id LEFT OUTER JOIN
programs ON
accounts.program_id = programs.id LEFT OUTER JOIN
membership_levels ON
accounts.membership_level_id = membership_levels.id
WHERE accounts.user_id = #{@session[:user].id}
ORDER BY activities.date DESC LIMIT #{items_per_feed}”
@activities = Activity.find_by_sql(sql_query)
end

Now, here’s a stripped down version of the models, which are for
keeping track of frequent flyer information:

class User < ActiveRecord::Base
has_many :accounts
end

class Account < ActiveRecord::Base
belongs_to :program
belongs_to :user
belongs_to :membership_level
has_many :activities
end

class Activity < ActiveRecord::Base
belongs_to :origin, :class_name => “Airport”, :foreign_key =>
“origin_id”
belongs_to :destination, :class_name => “Airport”, :foreign_key =>
“destination_id”
belongs_to :account
belongs_to :airline
end

class Airport < ActiveRecord::Base
has_many :activities, :foreign_key => “origin_id”
has_many :activities, :foreign_key => “destination_id”
end

class Program < ActiveRecord::Base
belongs_to :airline
has_many :membership_levels
has_many :accounts
end

class MembershipLevel < ActiveRecord::Base
belongs_to :program
has_many :accounts
end

class Airline < ActiveRecord::Base
has_many :activities
has_many :programs
end

A few things of note. One of the complexities is that I’m joining
the airports table twice, once as origin and once as destination. I
don’t think there’s any support for this using the regular find
method. Separately, I designed these models prior to the
availability of has many :through, but I now can’t decide whether and
how I should make use of that feature.

Obviously, I’ve gone through the trouble of making the above work.
However, I’d like any opinions on whether there’s a better way to
accomplish the same outcome. Also, will this many joins bring my
database to its knees under load? Thanks in advance for your thoughts.

        - dan


Dan K. mailto:[email protected]
http://www.dankohn.com/ tel:+1-415-233-1000

If you’re using a database version that supports it, you could use a
stored procedure. Put the whole select statement with all its joins into
the stored procedure and call it with find_by_sql. e.g. See
http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

This is perhaps not idiomatic for Rails but it has the advantage that
the database engine can optimise the query once. Your Rails app then
deals with a simple database entity called atom.

Regarding performance, with the correct indexes on the key fields, it
should be as fast as the complexity of your request allows. If there is
really a problem, you could run the query for all users periodically and
store the results in a temporary table for lookup by user id as
required. But that is definitely not idiomatic for SQL!

Julian

Thanks. I’m planning on doing action caching on the atom feed and
using a sweeper, which should limit the queries to only be done when
there’s new info for that user.

        - dan


Dan K. mailto:[email protected]
http://www.dankohn.com/ tel:+1-415-233-1000

On Jul 15, 2006, at 11:48 pm, Dan K. wrote:

Obviously, I’ve gone through the trouble of making the above work.
However, I’d like any opinions on whether there’s a better way to
accomplish the same outcome. Also, will this many joins bring my
database to its knees under load? Thanks in advance for your
thoughts.

Hi Dan

The number of joins in your query doesn’t bother me too much, but…
do they really all need to be left joins? I’ve found that database
servers have a much harder time optimising queries with left joins
than inner joins. If you can be sure that every row in table A has a
corresponding row in table B, then you should always use an inner
join. If it might not, double check why not, and also if it is
actually meaningful to bring back the part of the result set for A
without entries for B. Obviously the performance depends entirely on
what system you are using (if you didn’t write it by hand, then
judging by the SQL formatting I’m guessing it came from SQL Server’s
Enterprise Manager!!!)

Ashley

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs