Rails: Faster way to perform updates on many records
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.
Related videos on Youtube
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, 2022Comments
-
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 about 10 yearsThat'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 theshopify_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 about 10 yearsThanks, 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 almost 9 yearsThat sounds great, but when I try it, I get
ActiveRecord::StatementInvalid: PG::Error: ERROR: relation "this" does not exist
. Command looks likeUPDATE 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 almost 9 yearsSorry 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 almost 9 yearsYou 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 almost 9 yearsThanks 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 almost 9 yearsAnother 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 almost 9 yearsOk! Maybe you could share an updated sql string to reflect the join problem, I'll update the answer. Or simply add a new answer.