Openfoodnetwork: [Spike] Performance on reports/orders&fulfilments

[lin-d-hop]

Description

This is a spike on reports/orders&fulfilments to find potential improvements and potential quick wins that will improve performance on this page.

Goals

• We are looking for improvements on page load time, for the page and the CSV download.
• We are seeking to reduce page load time to less that 5 seconds for a shop with 10,000 products

Success Criteria

• Create new issues for tasks that will improve page load time on this page. Please give a t-shirt size (estimated dev time to complete - XS S M L XL XXL) and a t-shirt value (estimated improvement on page load time - XS S M L XL XXL) for each issue and summarise here.

[Matt-Yorkley]

This report is one of our biggest pain points now.

[kristinalim]

Posting here some code to create orders in development. This works with the sample data created when running script/setup. This is collapsed below :point_down: - click on the heading to view.

Code to create orders in development

require "factory_bot_rails"

100.times do
  distributor = (Enterprise.is_distributor.all + Enterprise.is_hub.all).sample
  outgoing_exchange = Exchange.outgoing.where(receiver_id: distributor.id).all.sample
  order_cycle = outgoing_exchange.order_cycle
  variants = outgoing_exchange.variants

  payment_method = distributor.payment_methods.where(type: "Spree::PaymentMethod::Check").all.sample
  shipping_method = distributor.shipping_methods.all.sample

  order = FactoryBot.create(:order, {
    distributor: distributor,
    order_cycle: order_cycle,
    ship_address: FactoryBot.create(:address)
  })
  order.select_shipping_method(shipping_method)
  order.save!

  variants.each do |variant|
    FactoryBot.create(:line_item, order: order, variant: variant)
  end

  order.create_proposed_shipments

  FactoryBot.create(:payment, state: "checkout", order: order, amount: order.total,
                              payment_method: payment_method)

  order.reload
  order.update_distribution_charge!
  order.reload
  AdvanceOrderService.new(order).call!
end

[sigmundpetersen]

Hey @kristinalim, just passed through this #2099 where @mkllnk did a spike on reports performance last year. Maybe there's some good info to take into account while working on this 👍

[kristinalim]

Thank you very much, @sigmundpetersen! I went through that issue just now - there are some interesting concepts there, and some others are no longer applicable. For now, I looked into making the DB queries for the Customer Totals report more optimized.

[kristinalim]

There are 4 report subtypes for the Orders and Fulfillments report. I am testing with 226 orders in my DB (development environment), and with my sample data got the following response times when generating the report without any filters:

• Supplier Totals - ~4 secs / 90 SQL
• Supplier Totals by Distributor - ~4 secs / 315 SQL
• Distributor Totals by Supplier - ~5 secs / 538 SQL
• Customer Totals - ~42 secs / 3912 SQL

As you can see, the Customer Totals report really needs more work than the others.

I created the following issues:

• [x] PR #4365: Eager-load obvious associations used in OrdersAndFulfillmentsReport #4308
• [x] Using SQL query for each type of adjustment for each line item in Customer Totals report #4309
• [x] PR #4326: Order#shipping_method counts number of shipments before fetching max one shipment #4310
• [x] PR #4325: find_variant in OrdersAndFulfillmentsReport does not cache result and does not use LineItem#variant #4312
• [ ] Two calls to fetch customer record for each non-group row in Customer Totals report #4313
• [x] N+1 for shipment, selected shipping rate, and shipping method in Customer Totals report #4314
• [x] PR #4327: Pre-fill Orders and Fulfillments report date filters with start and end dates #4315
• [x] PR #4324: Use Permissions from controller for Orders and Fulfillment report and memoize methods #4316
• [ ] Add DB index for order state #4317
• [x] PR #4398: Add DB index for order_cycle_id and distributor_id in orders table #4318
• [x] Eager-load associations for Variant#options_text used in Orders and Fulfillment report #4319

Many of these are small and easy changes.

[kristinalim]

This doesn't tackle bigger changes such as paginating, splitting, and emailing this report. There are more discussions about this in #2099. I think we should discuss and prioritize those separate from this spike.

Moving this epic to Code Review now. :tada:

[Matt-Yorkley]

That list of small issues looks great :+1:

[luisramos0]

nice work!!

4319 is a problem in all corners of the app! I have no clue how to fix it with the logic in VariantAndLineItemNaming#full_name

I think we can close this spike 💪

(I have learnt the

tag on github :-))

[luisramos0]

I dont think these issues should be tagged bugs, I think it's better we keep a separate performance backlog with the performance tag.

[kristinalim]

I dont think these issues should be tagged bugs, I think it's better we keep a separate performance backlog with the performance tag.

@luisramos0 Yes actually, that sounds good to me! I'll remove the bug-s3 labels for now unless it's decided later to add the label.

[kristinalim]

@luisramos0 I thought you were making a joke, but later thought maybe you didn't realize it: :smile:

1:

2:

[luisramos0]

ahaha, I was just sharing that I didnt know about it but by using it in the sentence it created that funny effect :-)

[mkllnk]

And I just read this conversation via email and was surprised that the Details thing worked in my email program as well. :smile:
(3 year old Gnome Evolution)

[sauloperez]

Amazing job @kristinalim! this makes it super easy to have a constant stream of perf. improvements in each release :clap: :clap:

[kristinalim]

We can close this spike already. :+1: