CODEWITHSUNDEEP
ruby-on-railsoptimizationactiverecord
Sid
Sid

Reputation: 6264

Rails optimization Question

In Rails while using activeRecord why are join queries considered bad.

For example

Here i'm trying to find the number of companies that belong to a certain category.


  class Company ActiveRecord::Base
    has_one :company_profile
  end

Finding the number of Company for a particular category_id

number_of_companies = Company.find(:all, :joins=>:company_profile, :conditions=>["(company_profiles.category_id = #{c_id}) AND is_published = true"])

How could this be better or is it just poor design?


  company_profiles = CompanyProfile.find_all_by_category_id(c_id)
  companies = []
  company_profiles.each{|c_profile| companies.push(c_profile.company) }

Isn't it better that the first request creates a single query while i'd be running several queries for the second case.

Could someone explain why joins are considered to be bad practice in Rails

Thanks in advance

Upvotes: 0

Views: 156

Answers (3)

Sam Phillips
Sam Phillips

Reputation: 619

Join queries are not bad, in fact, they are good, and ActiveRecord has them at its very heart. You don't need to break into find_by_sql to use them, options like :include will handle it for you. You can stay within the ORM, which gives the readability and ease of use, whilst still, for the most part, creating very efficient SQL (providing you have your indexes right!)

Bottom line - you need to do the bare minimum of database operations. Joins are a good way of letting the database do the heavy lifting for you, and lowering the number of queries that you execute.

By the by, DataMapper and Arel (the query engine in Rails 3) feature a lot of lazy loading - this means that code such as: 

@category = Category.find(params[:id])
@category.companies.size

Would most likely result in a join query that only did a COUNT operation, as the first line wouldn't result in a query being sent to the db.

Upvotes: 1

nathanvda
nathanvda

Reputation: 50057

To my knowledge, there is no such rule. The rule is to hit the database as least as possible, and rails gives you the right tools for that, using the joins.

The example Sam gives above is exemplary. Simple code, but behind the scenes rails has to do two queries, instead of only one using a join.

If there is one rule that comes to mind, that i think is related, is to avoid SQL where possible and use the rails way as much as possible. This keeps your code database agnostic (as rails handles the differences for you). But sometimes even that is unavoidable.

It comes down to good database design, creating the correct indexes (which you need to define manually in migrations), and sometimes big nested structures/joins are needed.

Upvotes: 1

Sam 山
Sam 山

Reputation: 42865

If you just want to find the number of companies on a category all you need to do is find the category and then call the association name and size because it will return an array.

@category = Category.find(params[:id])
@category.companies.size

Upvotes: 0

Related Questions