Rails：对许多记录执行更新的更快方法 [英] Rails: Faster way to perform updates on many records
问题描述
在我们的Rails 3.2.13应用程序（Heroku中的Ruby 2.0.0 + Postgres）中，我们经常从API检索大量Order数据，然后我们需要更新或创建数据库中的每个订单，如下所示：以及协会。单个订单会自行创建/更新以及大约。 10-15个关联的对象，我们一次最多可以导入500个订单。
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.
以下代码可以工作，但是问题在于，从根本上说，这根本没有效率速度。创建/更新500条记录大约需要花费时间。 1分钟并生成6500+个数据库查询！
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
如您所见，循环遍历每个订单，找出是否存在（然后加载现有订单或创建新订单），然后调用update_attributes传递该订单的详细信息。之后，我们创建或更新每个关联。每个关联的模型看起来都非常类似：
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
我已经研究了activerecord-import宝石，但不幸的是，它似乎更适合批量创建记录，而不是更新为我们也需要。
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?
非常感谢
更新：
我想出了这个小改进，
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
以及相关对象：
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
还有更好的建议吗？
推荐答案
尝试将整个代码包装到单个数据库事务中。由于您使用的是Heroku，因此它将是Postgres的最低端。拥有这么多的update语句，一次处理所有更新语句可能会大大受益，因此您的代码执行速度更快，并且基本上只有6500条语句的队列可以在Postgres端运行，因为服务器可以使它们出队。根据底端的情况，您可能必须进行较小的交易-但是即使一次交易100次（然后关闭并重新打开交易）也将大大提高Pg的吞吐量。
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
因此，在第2行之前，您需要添加类似：
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
这篇关于Rails：对许多记录执行更新的更快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
