Rails: Faster way to perform updates on many records

Question

In our Rails 3.2.13 app (Ruby 2.0.0 + Postgres on Heroku), we are often retreiving a large amount of Order data from an API, and then we need to update or create each order in our database, as well as the associations. A single order creates/updates itself plus approx. 10-15 associcated objects, and we are importing up to 500 orders at a time.

The below code works, but the problem is it's not at all efficient in terms of speed. Creating/updating 500 records takes approx. 1 minute and generates 6500+ db queries!

def add_details(shop, shopify_orders)
  shopify_orders.each do |shopify_order|
    order = Order.where(:order_id => shopify_order.id.to_s, :shop_id => shop.id).first_or_create
    order.update_details(order,shopify_order,shop)  #This calls update_attributes for the Order
    ShippingLine.add_details(order, shopify_order.shipping_lines)
    LineItem.add_details(order, shopify_order.line_items)
    Taxline.add_details(order, shopify_order.tax_lines)
    Fulfillment.add_details(order, shopify_order.fulfillments)
    Note.add_details(order, shopify_order.note_attributes)
    Discount.add_details(order, shopify_order.discount_codes)
    billing_address = shopify_order.billing_address rescue nil
    if !billing_address.blank?
      BillingAddress.add_details(order, billing_address)
    end
    shipping_address = shopify_order.shipping_address rescue nil
    if !shipping_address.blank?
      ShippingAddress.add_details(order, shipping_address)
    end
    payment_details = shopify_order.payment_details rescue nil
    if !payment_details.blank?
      PaymentDetail.add_details(order, payment_details)
    end
  end
end

  def update_details(order,shopify_order,shop)
    order.update_attributes(
      :order_name => shopify_order.name,
      :order_created_at => shopify_order.created_at,
      :order_updated_at => shopify_order.updated_at,
      :status => Order.get_status(shopify_order),
      :payment_status => shopify_order.financial_status,
      :fulfillment_status => Order.get_fulfillment_status(shopify_order),
      :payment_method => shopify_order.processing_method,
      :gateway => shopify_order.gateway,
      :currency => shopify_order.currency,
      :subtotal_price => shopify_order.subtotal_price,
      :subtotal_tax => shopify_order.total_tax,
      :total_discounts => shopify_order.total_discounts,
      :total_line_items_price => shopify_order.total_line_items_price,
      :total_price => shopify_order.total_price,
      :total_tax => shopify_order.total_tax,
      :total_weight => shopify_order.total_weight,
      :taxes_included => shopify_order.taxes_included,
      :shop_id => shop.id,
      :email => shopify_order.email,
      :order_note => shopify_order.note
    )
  end

So as you can see, we are looping through each order, finding out if it exists or not (then either loading the existing Order or creating the new Order), and then calling update_attributes to pass in the details for the Order. After that we create or update each of the associations. Each associated model looks very similar to this:

  class << self
    def add_details(order, tax_lines)
      tax_lines.each do |shopify_tax_line|
        taxline = Taxline.find_or_create_by_order_id(:order_id => order.id)
        taxline.update_details(shopify_tax_line)
      end
    end
  end
  def update_details(tax_line)
    self.update_attributes(:price => tax_line.price, :rate => tax_line.rate, :title => tax_line.title)
  end

I've looked into the activerecord-import gem but unfortunately it seems to be more geared towards creation of records in bulk and not update as we also require.

What is the best way that this can be improved for performance?

Many many thanks in advance.

UPDATE:

I came up with this slight improvement, which essentialy removes the call to update the newly created Orders (one query less per order).

 def add_details(shop, shopify_orders)
      shopify_orders.each do |shopify_order|
      values = {:order_id => shopify_order.id.to_s, :shop_id => shop.id,
        :order_name => shopify_order.name,
            :order_created_at => shopify_order.created_at,
            :order_updated_at => shopify_order.updated_at,
            :status => Order.get_status(shopify_order),
            :payment_status => shopify_order.financial_status,
            :fulfillment_status => Order.get_fulfillment_status(shopify_order),
            :payment_method => shopify_order.processing_method,
            :gateway => shopify_order.gateway,
            :currency => shopify_order.currency,
            :subtotal_price => shopify_order.subtotal_price,
            :subtotal_tax => shopify_order.total_tax,
            :total_discounts => shopify_order.total_discounts,
            :total_line_items_price => shopify_order.total_line_items_price,
            :total_price => shopify_order.total_price,
            :total_tax => shopify_order.total_tax,
            :total_weight => shopify_order.total_weight,
            :taxes_included => shopify_order.taxes_included,
            :email => shopify_order.email,
            :order_note => shopify_order.note}
        get_order = Order.where(:order_id => shopify_order.id.to_s, :shop_id => shop.id)
        if get_order.blank?
            order = Order.create(values)
        else
        order = get_order.first  
            order.update_attributes(values)
        end
        ShippingLine.add_details(order, shopify_order.shipping_lines)
        LineItem.add_details(order, shopify_order.line_items)
        Taxline.add_details(order, shopify_order.tax_lines)
        Fulfillment.add_details(order, shopify_order.fulfillments)
        Note.add_details(order, shopify_order.note_attributes)
        Discount.add_details(order, shopify_order.discount_codes)
        billing_address = shopify_order.billing_address rescue nil
        if !billing_address.blank?
          BillingAddress.add_details(order, billing_address)
        end
        shipping_address = shopify_order.shipping_address rescue nil
        if !shipping_address.blank?
          ShippingAddress.add_details(order, shipping_address)
        end
        payment_details = shopify_order.payment_details rescue nil
        if !payment_details.blank?
          PaymentDetail.add_details(order, payment_details)
        end
      end
 end

and for the associated objects:

  class << self
    def add_details(order, tax_lines)
      tax_lines.each do |shopify_tax_line|
        values = {:order_id => order.id,
            :price => tax_line.price,
            :rate => tax_line.rate,
            :title => tax_line.title}
        get_taxline = Taxline.where(:order_id => order.id)
        if get_taxline.blank?
            taxline = Taxline.create(values)
        else
            taxline = get_taxline.first  
            taxline.update_attributes(values)
        end
      end
    end
  end

Any better suggestions?

Answer 1

Try wrapping your entire code into a single database transaction. Since you're on Heroku it'll be a Postgres bottom-end. With that many update statements, you can probably benefit greatly by transacting them all at once, so your code executes quicker and basically just leaves a "queue" of 6500 statements to run on Postgres side as the server is able to dequeue them. Depending on the bottom end, you might have to transact into smaller chunks - but even transacting 100 at a time (and then close and re-open the transaction) would greatly improve throughput into Pg.

http://api.rubyonrails.org/classes/ActiveRecord/Transactions/ClassMethods.html http://www.postgresql.org/docs/9.2/static/sql-set-transaction.html

So before line 2 you'd add something like:

def add_details(shop, shopify_orders)
  Order.transaction do
    shopify_orders.each do |shopify_order|

And then at the very end of your method add another end:

      if !payment_details.blank?
        PaymentDetail.add_details(order, payment_details)
      end
    end //shopify_orders.each..
  end //Order.transaction..
end //method