Rails: Faster way to perform updates on many records

10,277

Solution 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

Solution 2

You can monkey-patch ActiveRecord like this:

class ActiveRecord::Base

  #http://stackoverflow.com/questions/15317837/bulk-insert-records-into-active-record-table?lq=1
  #https://gist.github.com/jackrg/76ade1724bd816292e4e
  #  "UPDATE THIS SET <list_of_column_assignments>  FROM <table_name> THIS  JOIN (VALUES (<csv1>, <csv2>,...) VALS ( <column_names> ) ON <list_of_primary_keys_comparison>"
  def self.bulk_update(record_list)
      pk = self.primary_key
      raise "primary_key not found" unless pk.present?

      raise "record_list not an Array of Hashes" unless record_list.is_a?(Array) && record_list.all? {|rec| rec.is_a? Hash }
      return nil if record_list.empty?

      result = nil

      #test if every hash has primary keys, so we can JOIN
      record_list.each { |r|  raise "Primary Keys '#{self.primary_key.to_s}' not found on record: #{r}" unless hasAllPKs?(r) }


      #list of primary keys comparison
      pk_comparison_array = []
      if (pk).is_a?(Array)
          pk.each {|thiskey| pk_comparison_array << "THIS.#{thiskey} = VALS.#{thiskey}" }
      else
          pk_comparison_array << "THIS.#{pk} = VALS.#{pk}"
      end
      pk_comparison = pk_comparison_array.join(' AND ')

      #SQL
      (1..record_list.count).step(1000).each do |start|
        key_list, value_list = convert_record_list(record_list[start-1..start+999])
        #csv values
        csv_vals = value_list.map {|v| "(#{v.join(", ")})" }.join(", ")
        #column names
        column_names = key_list.join(", ")
        #list of columns assignments
        columns_assign_array = []
        key_list.each {|col|
          unless inPK?(col)
            columns_assign_array << "THIS.#{col} = VALS.#{col}"
          end }
        columns_assign = columns_assign_array.join(', ')

        sql = "UPDATE THIS SET #{columns_assign}  FROM #{self.table_name} THIS  JOIN ( VALUES #{csv_vals} ) VALS ( #{column_names} ) ON ( #{pk_comparison} )"
        result = self.connection.execute(sql)

        return result if result<0
      end

      return result

  end

  def self.inPK?(str)
      pk = self.primary_key

      test = str.to_s
      if pk.is_a?(Array)
            (pk.include?(test))
      else
            (pk==test)
      end
  end

  #test if given hash has primary keys included as hash keys and those keys are not empty
  def self.hasAllPKs?(hash)
      h = hash.stringify_keys
      pk = self.primary_key

      if pk.is_a?(Array)
           (pk.all? {|k| h.key?(k) and h[k].present? })
      else
           h.key?(pk) and h[pk].present?
      end
  end

  def self.convert_record_list(record_list)
    # Build the list of keys
    key_list = record_list.map(&:keys).flatten.map(&:to_s).uniq.sort

    value_list = record_list.map do |rec|
      list = []
      key_list.each {|key| list <<  ActiveRecord::Base.connection.quote(rec[key] || rec[key.to_sym]) }
      list
    end

    # If table has standard timestamps and they're not in the record list then add them to the record list
    time = ActiveRecord::Base.connection.quote(Time.now)
    for field_name in %w(created_at updated_at)
      if self.column_names.include?(field_name) && !(key_list.include?(field_name))
        key_list << field_name
        value_list.each {|rec| rec << time }
      end
    end

    return [key_list, value_list]
  end
end

Then, you can generate a array of hashes containing your models attributes (including theirs primary keys) and do something like:

ActiveRecord::Base.transaction do
   Model.bulk_update [ {attr1: val1, attr2: val2,...},  {attr1: val1, attr2: val2,...},   ... ]
end

It will be a single SQL command without Rails callbacks and validations.

Share:
10,277

Related videos on Youtube

Bjorn Forsberg
Author by

Bjorn Forsberg

I'm a 30 something Australian/Swedish guy, living in beautiful Copenhagen.. with a passion for web, design and entrepreneurship... and learning Rails!

Updated on September 16, 2022

Comments

  • Bjorn Forsberg
    Bjorn Forsberg about 1 year

    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?

  • Bjorn Forsberg
    Bjorn Forsberg about 10 years
    That's an excellent answer, just the type of thing I was looking for! I'll test over the weekend and update once I've given it a go. We actually already pass in 250 Orders at a time to this, so I can easily adjust that to pass in 100 at a time to limit the size of the transaction. If that is still too much I can just move the Order.transaction do line below the shopify_orders.each loop so at least it will only do 1 transaction per Order (reducing it from 6500 transactions to 500 trasactions total).
  • Bjorn Forsberg
    Bjorn Forsberg about 10 years
    Thanks, this worked great. Using your suggested technique combined with the other changes I outlined above in my question, reduced the processing time by 50% !! :)
  • Jack R-G
    Jack R-G almost 9 years
    That sounds great, but when I try it, I get ActiveRecord::StatementInvalid: PG::Error: ERROR: relation "this" does not exist. Command looks like UPDATE this SET this."best_ranking" = vals."best_ranking",this."updated_at" = vals."updated_at" FROM books this JOIN (VALUES ...) vals ("best_ranking", "id", "created_at", "updated_at") on this.id = vals.id. Any thoughts about why this might happen (running on Postgres 9.1)?
  • Fernando Fabreti
    Fernando Fabreti almost 9 years
    Sorry to hear that, in fact, it's my mistake. The code above works with SQLServer and MySQL. I did not see the original question was about PostGreSQL. Trying to help, I've looked at PostGreSQL UPDATE Syntax (postgresql.org/docs/9.1/static/sql-update.html) and maybe you could try to modify the line that assigns the sql variable to this: sql = "UPDATE table #{self.table_name} as THIS SET #{columns_assign} FROM #{self.table_name} JOIN ( VALUES #{csv_vals} ) as VALS ( #{column_names} ) ON ( #{pk_comparison} )" beware I have NOT tested it! Good luck.
  • Fernando Fabreti
    Fernando Fabreti almost 9 years
    You can also try to run the above query inside PGAdmin or thru CLI, so that you cant modify the syntax until it works. Let us know of the progress so that we can update the answer and share with others!
  • Jack R-G
    Jack R-G almost 9 years
    Thanks for the suggestion. I was able to get a variation of what you suggested to work. sql = "UPDATE #{self.table_name} AS this SET #{columns_assign} FROM (VALUES #{cvs_vals}) AS vals (#{column_names}) where #{pk_comparison}". I had tried something similar to that but did not use the "AS" keyword, which seems to be required in this case although the syntax definition shows it as optional. The important point here is that you cannot repeat the table being updated in the FROM phrase; therefore you cannot do a join and you must put the join condition in a WHERE phrase.
  • Jack R-G
    Jack R-G almost 9 years
    Another issue is that there can be type issues when using FROM (VALUES ...). If, for example, one of the fields is a timestamp (e.g. updated_at), you cannot simply use a string value in the VALS array as you would in a simple UPDATE table SET updated_at = '12/14/2013', you must cast the string into a timestamp (UPDATE table SET updated_at = CAST(vals.updated_at AS TIMESTAMP) ...
  • Fernando Fabreti
    Fernando Fabreti almost 9 years
    Ok! Maybe you could share an updated sql string to reflect the join problem, I'll update the answer. Or simply add a new answer.