ruby on rails - Activerecord opitimization - best way to query all at once? -

ruby on rails - Activerecord opitimization - best way to query all at once? -

i trying accomplish reducing numbers of queries using activerecord 3.0.9. generated 'dummy' 200k customers , 500k orders.

here's models:

class client < 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 using code in controller:

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

and utilize in view (i removed haml codes easier read):

@order = @customers.each |customer| customer.orders.each |order| # select * orders ... %td= order.products.count # select count(*) products ... %td= order.products.sum(:amount) # select sum(*) products ... end end

however, page rendered table 100 rows per page. problem kinda slow load because firing 3-5 queries per customer's orders. thats 300 queries load page.

there's alternative way cut down number of queries , load page faster?

notes:

1) have attempted utilize includes(:orders), included more 200,000 order_ids. that's issue.

2) indexed.

if you're using count , sum(amount) need retrieve info , not orders themselves. done sql:

select customer_id, order_id, count(id) order_count, sum(amount) order_total orders left bring together products on orders.id=products.order_id grouping orders.customer_id, products.order_id

you can wrap in method returns nice, orderly hash re-mapping sql results construction fits requirements:

class order < activerecord::base def self.totals query = "..." # query above result = { } self.connection.select_rows(query).each |row| # build out array each unique customer_id in results customer_set = result[row[0].to_i] ||= [ ] # add together hash representing each order client 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 can fetch order counts , totals in single pass. if have index on customer_id, imperative in case, query fast big numbers of rows.

you can save results of method variable such @order_totals , reference when rendering table:

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

ruby-on-rails ruby ruby-on-rails-3 activerecord