CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-3activerecord
bry4n
bry4n

Reputation: 327

Activerecord opitimization - best way to query all at once?

I am trying to achieve by reducing the numbers of queries using ActiveRecord 3.0.9. I generated about 'dummy' 200K customers and 500K orders.

Here's Models:

class Customer < ActiveRecord::Base
  has_many :orders
end

class Orders < ActiveRecord::Base
  belongs_to :customer
  has_many :products
end

class Product < ActiveRecord::Base
  belongs_to :order
end

when you are using this code in the controller:

@customers = Customer.where(:active => true).paginate(page => params[:page], :per_page => 100)
# SELECT * FROM customers ...

and use this in the view (I removed HAML codes for easier to read):

@order = @customers.each do |customer|
  customer.orders.each do |order|      # SELECT * FROM orders ...
    %td= order.products.count          # SELECT COUNT(*) FROM products ...
    %td= order.products.sum(:amount)   # SELECT SUM(*) FROM products ...
  end
end

However, the page is rendered the table with 100 rows per page. The problem is that it kinda slow to load because its firing about 3-5 queries per customer's orders. thats about 300 queries to load the page.

There's alternative way to reduce the number of queries and load the page faster?

Notes:

1) I have attempted to use the includes(:orders), but it included more than 200,000 order_ids. that's issue.

2) they are already indexed.

Upvotes: 3

Views: 777

Answers (3)

user483040
user483040

Reputation:

You can join the tables in with Arel (I prefer to avoid writing raw sql when possible). I believe that for your example you would do something like:

Customer.joins(:orders -> products).select("id, name, count(products.id) as count, sum(product.amount) as total_amount")

The first method--

Customer.joins(:orders -> products)

--pulls in the nested association in one statement. Then the second part--

.select("id, name, count(products.id) as count, sum(product.amount) as total_amount")

--specifies exactly what columns you want back.

Chain those and I believe you'll get a list of Customer instances only populated with what you've specified in the select method. You have to be careful though because you now have in hand read only objects that are possibly in in invalid state.

As with all the Arel methods what you get from those methods is an ActiveRecord::Relation instance. It's only when you start to access that data that it goes out and executes the SQL.

I have some basic nervousness that my syntax is incorrect but I'm confident that this can be done w/o relying on executing raw SQL.

Upvotes: 0

Victor Moroz
Victor Moroz

Reputation: 9225

You can try something like this (yes, it looks ugly, but you want performance):

orders = Order.find_by_sql([<<-EOD, customer.id])

SELECT os.id, os.name, COUNT(ps.amount) AS count, SUM(ps.amount) AS amount 
FROM orders os 
  JOIN products ps ON ps.order_id = os.id 
WHERE os.customer_id = ? GROUP BY os.id, os.name

EOD

%td= orders.name
%td= orders.count
%td= orders.amount

Added: Probably it is better to create count and amount cache in Orders, but you will have to maintain it (count can be counter-cache, but I doubt there is a ready recipe for amount).

Upvotes: 0

tadman
tadman

Reputation: 211740

If you're only using COUNT and SUM(amount) then what you really need is to retrieve only that information and not the orders themselves. This is easily done with SQL:

SELECT customer_id, order_id, COUNT(id) AS order_count, SUM(amount) AS order_total FROM orders LEFT JOIN products ON orders.id=products.order_id GROUP BY orders.customer_id, products.order_id

You can wrap this in a method that returns a nice, orderly hash by re-mapping the SQL results into a structure that fits your requirements:

class Order < ActiveRecord::Base
  def self.totals
    query = "..." # Query from above

    result = { }

    self.connection.select_rows(query).each do |row|
      # Build out an array for each unique customer_id in the results
      customer_set = result[row[0].to_i] ||= [ ]

      # Add a hash representing each order to this customer order set
      customer_set << { :order_id => row[1].to_i, :count => row[2].to_i, :total => row[3].to_i } ]
    end

    result
  end
end

This means you can fetch all order counts and totals in a single pass. If you have an index on customer_id, which is imperative in this case, then the query will usually be really fast even for large numbers of rows.

You can save the results of this method into a variable such as @order_totals and reference it when rendering your table:

- @order = @customers.each do |customer|
  - @order_totals[customer.id].each do |order|
    %td= order[:count]
    %td= order[:total]

Upvotes: 3

Related Questions