This project is archived and is in readonly mode.

#5767 ✓resolved

ActiveRecord/Arel(?) takes over 2 minutes to generate a pseudo-complex SQL query.

Reported by Ray | October 7th, 2010 @ 07:06 PM

Here is an example query:

joins = [{:dim_locations=>[:dim_zip, {:dim_city=>{:dim_state=>:dim_country}}]}, :dim_ages, :dim_car_makes, :dim_educations, :dim_employments, :dim_foods, :dim_genders, :dim_get_aways, :dim_home_ownerships, :dim_incomes, :dim_marital_statuses, :dim_teams]

conditions = ["(dim_zips.zip_code = ? OR dim_zips.zip_code IS NULL) AND ( = ? OR IS NULL) AND (dim_states.state_code = ? OR dim_states.state_code IS NULL) AND (dim_states.state = ? OR dim_states.state IS NULL) AND (dim_countries.country_code = ? OR dim_countries.country_code IS NULL) AND ( = ? OR IS NULL) AND (dim_ages.age = ? OR dim_ages.age IS NULL) AND (dim_car_makes.car_make IN (?) OR dim_car_makes.car_make IS NULL) AND ( = ? OR IS NULL) AND (dim_employments.employment = ? OR dim_employments.employment IS NULL) AND ( IN (?) OR IS NULL) AND (dim_genders.gender = ? OR dim_genders.gender IS NULL) AND (dim_get_aways.get_away IN (?) OR dim_get_aways.get_away IS NULL) AND (dim_home_ownerships.home_ownership = ? OR dim_home_ownerships.home_ownership IS NULL) AND (dim_incomes.income = ? OR dim_incomes.income IS NULL) AND (dim_marital_statuses.marital_status = ? OR dim_marital_statuses.marital_status IS NULL) AND ( IN (?) OR IS NULL)", "98104", "Seattle", "WA", "Washington", "US", "United States of America", "18-34", ["Ashton Martin"], "Bachelors", "Full Time", ["Brazilian"], "Male", ["France"], "Rent", "Less than 30k", "Married", ["Arizona Cardinals", "Boston Bruins"]]

Benchmark.measure do
Koupon.includes(joins).where(conditions).each end
=> 127.930000 0.390000 128.320000 (129.897278)

(I append the .each to force a query)

In Rails 2.x ActiveRecord this query was generated nearly instantaneously. I have
verified it isn't even hitting the database until the very end of that 2 minute period.

Comments and changes to this ticket

  • Aaron Patterson

    Aaron Patterson October 8th, 2010 @ 02:15 AM

    • State changed from “new” to “resolved”
    • Importance changed from “” to “Low”

    This is fixed by ARel 2.0. Upgrading to Rails 3.0.2 should fix the problem (when it's released).

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href=""></a>

People watching this ticket