Cache queries when creating sub records?

I have an application which handles orders with line items. The line items come in as part of the order in JSON format, e.g.:

{
  "customer_id":24,
  "line_items":[
  {
     "variant_id":"1423_101_10",
     "quantity":"5",
     "product_id":"1423"
  },
  {
     "variant_id":"2396_101_12",
     "quantity":"3",
     "product_id":"2396"
  }
  ]
}

So this will set up an order in the orders table, e.g.:

id | customer_id
1  | 24

And line items in the line_items table, e.g.:

id | order_id | product_id | variant_id  | quantity | price*
1  | 1        | 1423       | 1423_101_10 | 5        | 10
2  | 1        | 2396       | 2396_101_10 | 3        | 15

*price doesn't come from the order JSON, it's retrieved via a lookup

However, when the new records are created it does a SELECT for the order for each line_item added. This wouldn't be an issue in the example above, but this application can and does have hundreds and sometimes thousands of line items for a particular order, so it seems like it's inefficient and potentially a cause of the Heroku server running out of memory. Is there a way to only load the Order once, rather than for each line item?

Another potential bottleneck is that a lookup is done against a Products table to get the price. In the example above, there's no possible caching, but if multiple variants of the same Product are selected, it seems inefficient to look up the Product each time when it may already have been loaded. For example, 1423_101_10, 1423_101_12, 1423_102_10 and 1423_102_12 are all the same Product with the same price. Is it better to try and cache Products already looked up or would that complicate things further?

Edit:

Completely forgot to add any code!

Order Model:

class Order < ActiveRecord::Base
has_many :line_items, :dependent => :destroy  

Line Item Model:

class LineItem < ActiveRecord::Base
before_create :set_price
belongs_to :order
belongs_to :product, :primary_key => "product_id", :conditions => proc { "season = '#{order.season}'" }

def set_price
  write_attribute :price, product.prices[order.currency] if price.nil? && product && order
end  

Product Model:

class Product < ActiveRecord::Base

Edit 2:

OrdersController (simplified)

class OrdersController < ApplicationController

def create
  @order = Order.new(order_params)
  authorize! :create, @order

  if @order.save
    render_order_json
  end
end

def order_params
  permitted = params.permit(:customer_id, :line_items => line_item_params)
  permitted[:line_items_attributes] = permitted.delete("line_items") if    permitted["line_items"]
  permitted
end

def line_item_params
  [:product_id, :variant_id, :quantity]
end

Edit 3: An example of the SQL I see reported:

Order Load (1.0ms)  SELECT "orders".* FROM "orders" WHERE "orders"."id" = $1 ORDER BY "orders"."id" ASC LIMIT 1  [["id", 1]]
Product Load (1.0ms)  SELECT "products".* FROM "products" WHERE "products"."product_id" = $1 AND (season = 'AW14') ORDER BY "products"."id" ASC LIMIT 1  [["product_id", 1423]]
SQL (2.0ms)  INSERT INTO "line_items" ("order_id", "price", "product_id", "quantity", "variant_id") VALUES ($1, $2, $3, $4, $5) RETURNING "id"  [["order_id", 1], ["price", 10.0], ["product_id", 1423], ["quantity", 5], ["variant_id", "1423_101_10"]]
Order Load (1.0ms)  SELECT "orders".* FROM "orders" WHERE "orders"."id" = $1 ORDER BY "orders"."id" ASC LIMIT 1  [["id", 1]]
Product Load (2.0ms)  SELECT "products".* FROM "products" WHERE "products"."product_id" = $1 AND (season = 'AW14') ORDER BY "products"."id" ASC LIMIT 1  [["product_id", 2396]]
SQL (1.0ms)  INSERT INTO "line_items" ("order_id", "price", "product_id", "quantity", "variant_id") VALUES ($1, $2, $3, $4, $5) RETURNING "id"  [["order_id", 1], ["price", 15.0], ["product_id", 2396], ["quantity", 3], ["variant_id", "2396_101_10"]]

Answers


If you want to speed up the create action, you have several options:

  • removing the database intensive callbacks
  • speeding up the callbacks through caching
  • delay creation to be executed through background tasks

Depending on your application needs, those might be viable options in the order of impact into your code-base and infrastructure. This totally depends on what you have already setup, so it might be the other way around.

By removing the callback (set_price) that creates the 1+n problem in your create code, you will have to create some lookup method that fetches all the prices at once and applies them to the order.

Caching could go into the set_price method, so that the lookup is only done once. You will have to take care of cache-expiry when the price changes, which might be none-trivial.

Using a background-job like resque or sidekiq can take the order and do all the processing without the response timing out. You will have to do an asynchronous check for the order to be processed to make it visible in the frontend.


In the end it just took a bit of a workflow change to speed up the Order creation.

Instead of calling set_price on the before_create method, it's done in the OrderController via the Order model. So now my code looks like:

Order Model:

class Order < ActiveRecord::Base
has_many :line_items, :dependent => :destroy  

def set_prices
  self.line_items.each do |item|
    item.set_price
  end
end 

LineItem model:

class LineItem < ActiveRecord::Base
belongs_to :order
belongs_to :product, :primary_key => "product_id", :conditions => proc { "season = '#{order.season}'" }

def set_price
  self.price = Product.where(:product_id => product_id, :season =>  season).first.prices[currency]
end  

OrdersController:

class OrdersController < ApplicationController

def create
  @order = Order.new(order_params)
  authorize! :create, @order
  @order.set_prices

  if @order.save
    render_order_json
  end
end

def order_params
  permitted = params.permit(:customer_id, :line_items => line_item_params)
  permitted[:line_items_attributes] = permitted.delete("line_items") if        permitted["line_items"]
  permitted
end

def line_item_params
   [:product_id, :variant_id, :quantity]
end

This question was also related and also sped things up. Stop child models updating when parent is updated


Need Your Help

Mercurial outgoing Hook

mercurial repository hook push mercurial-hook

I'm looking to create a Mercurial hook that pushes to a backup remote repository when I push to a local repository. I thought I could hook the 'outgoing' hook, but this creates a infinite loop tha...

HttpListenerResponse 404 and Firefox not interpreting it

internet-explorer c#-4.0 firefox

I got an issuee with Firefox. I implementet a Webservice using HttpListener.