Investigate performance of darkswarm layout's injections

What we should change and why (this is tech debt)

At least half (if not more) of the queries triggered by EnterprisesController#shop seem to be caused by the Darkswarm layout. Precisely, it is due to the injection methods that are required to load the state of the app (current_order, current_user, etc.). The result is the following list of N+1s:

Enterprise Load (2.5ms) SELECT DISTINCT enterprises.* FROM "enterprises" INNER JOIN "distributors_shipping_methods" ON "distributors_shipping_methods"."distributor_id" = "enterprises"."id " INNER JOIN "spree_shipping_methods" ON "spree_shipping_methods"."id" = "distributors_shipping_methods"."shipping_method_id" INNER JOIN "distributors_payment_methods" ON "distributors_payme nt_methods"."distributor_id" = "enterprises"."id" INNER JOIN "spree_payment_methods" ON "spree_payment_methods"."id" = "distributors_payment_methods"."payment_method_id" LEFT OUTER JOIN exch anges ON (exchanges.receiver_id = enterprises.id AND exchanges.incoming = 'f') LEFT OUTER JOIN order_cycles ON (order_cycles.id = exchanges.order_cycle_id) WHERE "spree_payment_methods"."deleted_ at" IS NULL AND "spree_payment_methods"."active" = 't' AND (order_cycles.orders_open_at <= '2019-10-02 12:42:03.190564' AND order_cycles.orders_close_at >= '2019-10-02 12:42:03.190573') AND ("spree_payment_methods".deleted_at IS NULL) AND (spree_payment_methods.display_on='both' OR spree_payment_methods.display_on='' OR spree_payment_methods.display_on IS NULL) AND (spree_payme nt_methods.environment='development' OR spree_payment_methods.environment='' OR spree_payment_methods.environment IS NULL) Spree::Property Load (1.4ms) SELECT DISTINCT spree_properties.* FROM "spree_properties" INNER JOIN "spree_product_properties" ON "spree_product_properties"."property_id" = "spree_properti es"."id" INNER JOIN "spree_products" ON "spree_products"."id" = "spree_product_properties"."product_id" INNER JOIN "spree_variants" ON "spree_variants"."product_id" = "spree_products"."id" A ND "spree_variants"."is_master" = 'f' AND "spree_variants"."deleted_at" IS NULL INNER JOIN "exchange_variants" ON "exchange_variants"."variant_id" = "spree_variants"."id" INNER JOIN "exchang es" ON "exchanges"."id" = "exchange_variants"."exchange_id" INNER JOIN "order_cycles" ON "order_cycles"."id" = "exchanges"."order_cycle_id" WHERE "exchanges"."incoming" = 'f' AND "exchanges" ."receiver_id" = 3 AND (order_cycles.orders_open_at <= '2019-10-02 12:42:03.194611' AND order_cycles.orders_close_at >= '2019-10-02 12:42:03.194620') Spree::Property Load (1.6ms) SELECT DISTINCT spree_properties.* FROM "spree_properties" INNER JOIN "producer_properties" ON "producer_properties"."property_id" = "spree_properties"."id" I NNER JOIN "enterprises" ON "enterprises"."id" = "producer_properties"."producer_id" INNER JOIN "spree_products" ON "spree_products"."supplier_id" = "enterprises"."id" INNER JOIN "spree_varia nts" ON "spree_variants"."product_id" = "spree_products"."id" AND "spree_variants"."is_master" = 'f' AND "spree_variants"."deleted_at" IS NULL INNER JOIN "exchange_variants" ON "exchange_var iants"."variant_id" = "spree_variants"."id" INNER JOIN "exchanges" ON "exchanges"."id" = "exchange_variants"."exchange_id" INNER JOIN "order_cycles" ON "order_cycles"."id" = "exchanges"."ord er_cycle_id" WHERE "exchanges"."incoming" = 'f' AND "exchanges"."receiver_id" = 3 AND (order_cycles.orders_open_at <= '2019-10-02 12:42:03.195051' AND order_cycles.orders_close_at >= '2019-1 0-02 12:42:03.195060') Exchange Load (0.6ms) SELECT exchanges.receiver_id AS receiver_id, MIN(order_cycles.orders_close_at) AS earliest_close_at FROM "exchanges" INNER JOIN "order_cycles" ON "order_cycles"."id" = "exchanges"."order_cycle_id" WHERE "exchanges"."incoming" = 'f' AN D (order_cycles.orders_open_at <= '2019-10-02 12:42:03.199460' AND order_cycles.orders_close_at >= '2019-10-02 12:42:03.199471') GROUP BY exchanges.receiver_id Rendered json/_injection_ams.html.haml (0.2ms) Rendered json/_injection_ams.html.haml (0.2ms) Rendered json/_injection_ams.html.haml (0.1ms) Spree::Taxon Load (0.5ms) SELECT "spree_taxons".* FROM "spree_taxons" Cache read: api/taxon_serializer/spree/taxons/1-20190923121042/serializable-hash Cache fetch_hit: api/taxon_serializer/spree/taxons/1-20190923121042/serializable-hash Cache read: api/taxon_serializer/spree/taxons/2-20190923121042/serializable-hash Cache fetch_hit: api/taxon_serializer/spree/taxons/2-20190923121042/serializable-hash Cache read: api/taxon_serializer/spree/taxons/3-20190923121042/serializable-hash Cache fetch_hit: api/taxon_serializer/spree/taxons/3-20190923121042/serializable-hash Cache read: api/taxon_serializer/spree/taxons/4-20190923121042/serializable-hash Cache fetch_hit: api/taxon_serializer/spree/taxons/4-20190923121042/serializable-hash Cache read: api/taxon_serializer/spree/taxons/5-20190923121042/serializable-hash Cache fetch_hit: api/taxon_serializer/spree/taxons/5-20190923121042/serializable-hash Cache read: api/taxon_serializer/spree/taxons/6-20190923121042/serializable-hash Cache fetch_hit: api/taxon_serializer/spree/taxons/6-20190923121042/serializable-hash Cache read: api/taxon_serializer/spree/taxons/7-20190923121042/serializable-hash Cache fetch_hit: api/taxon_serializer/spree/taxons/7-20190923121042/serializable-hash Rendered json/_injection_ams.html.haml (0.2ms) Spree::Property Load (0.5ms) SELECT "spree_properties".* FROM "spree_properties" Rendered json/_injection_ams.html.haml (0.2ms) CACHE (0.0ms) SELECT "variant_overrides".* FROM "variant_overrides" WHERE "variant_overrides"."permission_revoked_at" IS NULL AND "variant_overrides"."hub_id" = 3 Spree::Shipment Load (0.4ms) SELECT "spree_shipments".* FROM "spree_shipments" WHERE "spree_shipments"."order_id" = 5 Cache read: spree/app_configuration/display_currency Cache read: spree/app_configuration/currency_symbol_position Cache read: spree/app_configuration/hide_cents Cache read: spree/app_configuration/currency_decimal_mark Cache read: spree/app_configuration/currency_thousands_separator Spree::Payment Load (0.4ms) SELECT "spree_payments".* FROM "spree_payments" WHERE "spree_payments"."order_id" = 5 LIMIT 1 CACHE (0.0ms) SELECT "variant_overrides".* FROM "variant_overrides" WHERE "variant_overrides"."permission_revoked_at" IS NULL AND "variant_overrides"."hub_id" = 3 Spree::LineItem Load (0.8ms) SELECT "spree_line_items".* FROM "spree_line_items" INNER JOIN "spree_orders" ON "spree_orders"."id" = "spree_line_items"."order_id" WHERE "spree_orders"."sta te" = 'complete' AND "spree_orders"."user_id" = 1 AND "spree_orders"."distributor_id" = 3 AND "spree_orders"."order_cycle_id" = 1 AND (completed_at IS NOT NULL) Spree::Variant Load (0.5ms) SELECT "spree_variants".* FROM "spree_variants" WHERE "spree_variants"."id" = 6 LIMIT 1 CACHE (0.0ms) SELECT "spree_variants".* FROM "spree_variants" WHERE "spree_variants"."id" = 6 LIMIT 1 Spree::Product Load (0.5ms) SELECT "spree_products".* FROM "spree_products" WHERE "spree_products"."id" = 3 LIMIT 1 Spree::OptionValue Load (0.6ms) SELECT "spree_option_values".* FROM "spree_option_values" INNER JOIN "spree_option_types" ON "spree_option_types"."id" = "spree_option_values"."option_type _id" INNER JOIN "spree_option_values_variants" ON "spree_option_values"."id" = "spree_option_values_variants"."option_value_id" WHERE "spree_option_values_variants"."variant_id" = 6 ORDER BY spree_option_types.position asc CACHE (0.0ms) SELECT "spree_option_values".* FROM "spree_option_values" INNER JOIN "spree_option_types" ON "spree_option_types"."id" = "spree_option_values"."option_type_id" INNER JOIN "s pree_option_values_variants" ON "spree_option_values"."id" = "spree_option_values_variants"."option_value_id" WHERE "spree_option_values_variants"."variant_id" = 6 ORDER BY spree_option_type s.position asc Cache read: spree/app_configuration/currency Spree::Price Load (0.5ms) SELECT "spree_prices".* FROM "spree_prices" WHERE "spree_prices"."variant_id" = 6 AND "spree_prices"."currency" = 'AUD' LIMIT 1 (0.4ms) SELECT COUNT(*) FROM "spree_stock_items" WHERE "spree_stock_items"."variant_id" = 6 Spree::StockItem Load (0.5ms) SELECT "spree_stock_items".* FROM "spree_stock_items" WHERE "spree_stock_items"."variant_id" = 6 ORDER BY id ASC LIMIT 1 Cache read: spree/app_configuration/track_inventory_levels Spree::StockItem Load (0.6ms) SELECT "spree_stock_items".* FROM "spree_stock_items" INNER JOIN "spree_stock_locations" ON "spree_stock_locations"."id" = "spree_stock_items"."stock_locatio n_id" WHERE "spree_stock_items"."variant_id" = 6 AND "spree_stock_locations"."active" = 't' Exchange Load (0.5ms) SELECT "exchanges".* FROM "exchanges" INNER JOIN "exchange_variants" ON "exchange_variants"."exchange_id" = "exchanges"."id" WHERE "exchanges"."order_cycle_id" = 1 A ND (exchanges.incoming OR exchanges.receiver_id = 3) AND (exchange_variants.variant_id = 6) EnterpriseFee Load (0.6ms) SELECT "enterprise_fees".* FROM "enterprise_fees" INNER JOIN "spree_calculators" ON "spree_calculators"."calculable_id" = "enterprise_fees"."id" AND "spree_calc ulators"."calculable_type" = 'EnterpriseFee' INNER JOIN "exchange_fees" ON "enterprise_fees"."id" = "exchange_fees"."enterprise_fee_id" WHERE "exchange_fees"."exchange_id" = 1 AND (spree_cal culators.type NOT IN ('Spree::Calculator::FlatRate','Spree::Calculator::FlexiRate','Spree::Calculator::PriceSack')) EnterpriseFee Load (0.6ms) SELECT "enterprise_fees".* FROM "enterprise_fees" INNER JOIN "spree_calculators" ON "spree_calculators"."calculable_id" = "enterprise_fees"."id" AND "spree_calc ulators"."calculable_type" = 'EnterpriseFee' INNER JOIN "exchange_fees" ON "enterprise_fees"."id" = "exchange_fees"."enterprise_fee_id" WHERE "exchange_fees"."exchange_id" = 2 AND (spree_cal culators.type NOT IN ('Spree::Calculator::FlatRate','Spree::Calculator::FlexiRate','Spree::Calculator::PriceSack')) EnterpriseFee Load (0.6ms) SELECT "enterprise_fees".* FROM "enterprise_fees" INNER JOIN "spree_calculators" ON "spree_calculators"."calculable_id" = "enterprise_fees"."id" AND "spree_calc ulators"."calculable_type" = 'EnterpriseFee' INNER JOIN "coordinator_fees" ON "enterprise_fees"."id" = "coordinator_fees"."enterprise_fee_id" WHERE "coordinator_fees"."order_cycle_id" = 1 AN D (spree_calculators.type NOT IN ('Spree::Calculator::FlatRate','Spree::Calculator::FlexiRate','Spree::Calculator::PriceSack')) Spree::Calculator Load (0.4ms) SELECT "spree_calculators".* FROM "spree_calculators" WHERE "spree_calculators"."calculable_id" = 3 AND "spree_calculators"."calculable_type" = 'EnterpriseF ee' LIMIT 1 Cache read: calculator/flat_percent_per_item/flat_percent/3 CACHE (0.0ms) SELECT "exchanges".* FROM "exchanges" INNER JOIN "exchange_variants" ON "exchange_variants"."exchange_id" = "exchanges"."id" WHERE "exchanges"."order_cycle_id" = 1 AND (exch anges.incoming OR exchanges.receiver_id = 3) AND (exchange_variants.variant_id = 6) CACHE (0.0ms) SELECT "enterprise_fees".* FROM "enterprise_fees" INNER JOIN "spree_calculators" ON "spree_calculators"."calculable_id" = "enterprise_fees"."id" AND "spree_calculators"."cal culable_type" = 'EnterpriseFee' INNER JOIN "exchange_fees" ON "enterprise_fees"."id" = "exchange_fees"."enterprise_fee_id" WHERE "exchange_fees"."exchange_id" = 1 AND (spree_calculators.type NOT IN ('Spree::Calculator::FlatRate','Spree::Calculator::FlexiRate','Spree::Calculator::PriceSack')) CACHE (0.0ms) SELECT "enterprise_fees".* FROM "enterprise_fees" INNER JOIN "spree_calculators" ON "spree_calculators"."calculable_id" = "enterprise_fees"."id" AND "spree_calculators"."cal culable_type" = 'EnterpriseFee' INNER JOIN "exchange_fees" ON "enterprise_fees"."id" = "exchange_fees"."enterprise_fee_id" WHERE "exchange_fees"."exchange_id" = 2 AND (spree_calculators.type NOT IN ('Spree::Calculator::FlatRate','Spree::Calculator::FlexiRate','Spree::Calculator::PriceSack')) CACHE (0.0ms) SELECT "enterprise_fees".* FROM "enterprise_fees" INNER JOIN "spree_calculators" ON "spree_calculators"."calculable_id" = "enterprise_fees"."id" AND "spree_calculators"."cal culable_type" = 'EnterpriseFee' INNER JOIN "coordinator_fees" ON "enterprise_fees"."id" = "coordinator_fees"."enterprise_fee_id" WHERE "coordinator_fees"."order_cycle_id" = 1 AND (spree_calc ulators.type NOT IN ('Spree::Calculator::FlatRate','Spree::Calculator::FlexiRate','Spree::Calculator::PriceSack')) CACHE (0.0ms) SELECT "spree_calculators".* FROM "spree_calculators" WHERE "spree_calculators"."calculable_id" = 3 AND "spree_calculators"."calculable_type" = 'EnterpriseFee' LIMIT 1 Cache read: calculator/flat_percent_per_item/flat_percent/3 CACHE (0.0ms) SELECT "spree_products".* FROM "spree_products" WHERE "spree_products"."id" = 3 LIMIT 1 CACHE (0.0ms) SELECT "spree_option_values".* FROM "spree_option_values" INNER JOIN "spree_option_types" ON "spree_option_types"."id" = "spree_option_values"."option_type_id" INNER JOIN "s pree_option_values_variants" ON "spree_option_values"."id" = "spree_option_values_variants"."option_value_id" WHERE "spree_option_values_variants"."variant_id" = 6 ORDER BY spree_option_type s.position asc CACHE (0.0ms) SELECT "spree_option_values".* FROM "spree_option_values" INNER JOIN "spree_option_types" ON "spree_option_types"."id" = "spree_option_values"."option_type_id" INNER JOIN "s pree_option_values_variants" ON "spree_option_values"."id" = "spree_option_values_variants"."option_value_id" WHERE "spree_option_values_variants"."variant_id" = 6 ORDER BY spree_option_type s.position asc Cache read: spree/app_configuration/currency CACHE (0.0ms) SELECT "spree_prices".* FROM "spree_prices" WHERE "spree_prices"."variant_id" = 6 AND "spree_prices"."currency" = 'AUD' LIMIT 1 CACHE (0.0ms) SELECT COUNT(*) FROM "spree_stock_items" WHERE "spree_stock_items"."variant_id" = 6 CACHE (0.0ms) SELECT "spree_stock_items".* FROM "spree_stock_items" WHERE "spree_stock_items"."variant_id" = 6 ORDER BY id ASC LIMIT 1 Cache read: spree/app_configuration/track_inventory_levels CACHE (0.0ms) SELECT "spree_stock_items".* FROM "spree_stock_items" INNER JOIN "spree_stock_locations" ON "spree_stock_locations"."id" = "spree_stock_items"."stock_location_id" WHERE "spr ee_stock_items"."variant_id" = 6 AND "spree_stock_locations"."active" = 't' CACHE (0.0ms) SELECT "exchanges".* FROM "exchanges" INNER JOIN "exchange_variants" ON "exchange_variants"."exchange_id" = "exchanges"."id" WHERE "exchanges"."order_cycle_id" = 1 AND (exch anges.incoming OR exchanges.receiver_id = 3) AND (exchange_variants.variant_id = 6) CACHE (0.0ms) SELECT "enterprise_fees".* FROM "enterprise_fees" INNER JOIN "spree_calculators" ON "spree_calculators"."calculable_id" = "enterprise_fees"."id" AND "spree_calculators"."cal culable_type" = 'EnterpriseFee' INNER JOIN "exchange_fees" ON "enterprise_fees"."id" = "exchange_fees"."enterprise_fee_id" WHERE "exchange_fees"."exchange_id" = 1 AND (spree_calculators.type NOT IN ('Spree::Calculator::FlatRate','Spree::Calculator::FlexiRate','Spree::Calculator::PriceSack')) CACHE (0.0ms) SELECT "enterprise_fees".* FROM "enterprise_fees" INNER JOIN "spree_calculators" ON "spree_calculators"."calculable_id" = "enterprise_fees"."id" AND "spree_calculators"."cal culable_type" = 'EnterpriseFee' INNER JOIN "exchange_fees" ON "enterprise_fees"."id" = "exchange_fees"."enterprise_fee_id" WHERE "exchange_fees"."exchange_id" = 2 AND (spree_calculators.type NOT IN ('Spree::Calculator::FlatRate','Spree::Calculator::FlexiRate','Spree::Calculator::PriceSack')) CACHE (0.0ms) SELECT "enterprise_fees".* FROM "enterprise_fees" INNER JOIN "spree_calculators" ON "spree_calculators"."calculable_id" = "enterprise_fees"."id" AND "spree_calculators"."cal culable_type" = 'EnterpriseFee' INNER JOIN "coordinator_fees" ON "enterprise_fees"."id" = "coordinator_fees"."enterprise_fee_id" WHERE "coordinator_fees"."order_cycle_id" = 1 AND (spree_calc ulators.type NOT IN ('Spree::Calculator::FlatRate','Spree::Calculator::FlexiRate','Spree::Calculator::PriceSack')) CACHE (0.0ms) SELECT "spree_calculators".* FROM "spree_calculators" WHERE "spree_calculators"."calculable_id" = 3 AND "spree_calculators"."calculable_type" = 'EnterpriseFee' LIMIT 1 Cache read: calculator/flat_percent_per_item/flat_percent/3 CACHE (0.0ms) SELECT "exchanges".* FROM "exchanges" INNER JOIN "exchange_variants" ON "exchange_variants"."exchange_id" = "exchanges"."id" WHERE "exchanges"."order_cycle_id" = 1 AND (exch anges.incoming OR exchanges.receiver_id = 3) AND (exchange_variants.variant_id = 6) CACHE (0.0ms) SELECT "enterprise_fees".* FROM "enterprise_fees" INNER JOIN "spree_calculators" ON "spree_calculators"."calculable_id" = "enterprise_fees"."id" AND "spree_calculators"."cal culable_type" = 'EnterpriseFee' INNER JOIN "exchange_fees" ON "enterprise_fees"."id" = "exchange_fees"."enterprise_fee_id" WHERE "exchange_fees"."exchange_id" = 1 AND (spree_calculators.type NOT IN ('Spree::Calculator::FlatRate','Spree::Calculator::FlexiRate','Spree::Calculator::PriceSack')) CACHE (0.0ms) SELECT "enterprise_fees".* FROM "enterprise_fees" INNER JOIN "spree_calculators" ON "spree_calculators"."calculable_id" = "enterprise_fees"."id" AND "spree_calculators"."cal culable_type" = 'EnterpriseFee' INNER JOIN "exchange_fees" ON "enterprise_fees"."id" = "exchange_fees"."enterprise_fee_id" WHERE "exchange_fees"."exchange_id" = 2 AND (spree_calculators.type NOT IN ('Spree::Calculator::FlatRate','Spree::Calculator::FlexiRate','Spree::Calculator::PriceSack')) CACHE (0.0ms) SELECT "spree_calculators".* FROM "spree_calculators" WHERE "spree_calculators"."calculable_id" = 3 AND "spree_calculators"."calculable_type" = 'EnterpriseFee' LIMIT 1

Each of these methods should be investigated separately so that we find the most cost-effective solution to improve their performance. Chances are that some of them are used in all pages but others are not and we're paying their performance cost.

Context

This was created in relation to the investigation done in https://github.com/openfoodfoundation/openfoodnetwork/issues/3853 to optimize DB queries in /enterprise/shop.

Impact and timeline

Given this layout is used in all shopfront pages, any improvements done reducing the number and execution time of queries will have a big impact across all pages reducing all response times at least a bit. This, in turn, will also reduce the load on the DB.

openfoodfoundation / openfoodnetwork