CODEWITHSUNDEEP
ruby-on-railsperformancepostgresqlactiverecordheroku
Skyalchemist
Skyalchemist

Reputation: 461

Caching an advanced search query in rails


I have this method in my model that powers the search function in my app:

def self.get_products(cat_ids, term, min_price, max_price, sort_column, sort_direction, current_store = nil)
        products = Product.where(category_id: cat_ids).joins('LEFT JOIN stores ON stores.id = products.store_id') 
        products = products.order(sort_column + " " + sort_direction)
        products = products.where(store_id: current_store.id) if current_store.present?
        products = products.where("lower(product_title) like ?", "%#{term}%") if term.present?
        products = products.where("price_pennies >= ?", (min_price.to_f/1.2).round * 100) if min_price.present?
        products = products.where("price_pennies <= ?", (max_price.to_f/1.2).round * 100) if max_price.present?
        products = products.where('stores.opened_to_customers = ?', true)
        products
    end

A brief description of the parameters in the method above:

cat_ids: An array of all the relevant category_ids. In this case, using the awesome_nested_set gem helper
cat_ids = @category.self_and_descendants.pluck(:id)
term: The search query entered by the user

I feel the remaining parameters are pretty self descriptive.
This has been working perfectly over the course of 2 months but now that the rows in the products table is nearing 300,000, it has gotten extremely slow and a lot of times it throws this error: Error R14 Memory Quota Exceeded(The app is hosted on heroku).

What's the best way to cache this query? And more importantly, is there a better way to write this query to improve it's speed and avoid memory leaks?

Ps: I use memcached to cache my app in general. And i've used Rails Cache fetch in other place but as this has a lot of parameters i'm confused as to how to cache something so dynamic.

Upvotes: 1

Views: 973

Answers (1)

SHS
SHS

Reputation: 7744

If you're wondering how exactly to cache the results, then one way would be to generate a cache key that depends on the passed params.

In the following implementation, I've seperated each condition into different variables and then created a hash key based on a string that is made from the condition-variables.

base_conditions = {
  products: {category_id: cat_ids.sort},
  stores: {opened_to_customers: true}
}

current_store_condition = \
  if current_store.present?
    {store_id: current_store.id}
  end || ""

term_condition = \
  if term.present?
    ["LOWER(product_title) LIKE ?", "%#{term.downcase}%"]
  end || ""

price_range_min_condition = \
  if min_price.present? 
    ["price_pennies >= ?", (100 * min_price.fdiv(1.2)).round]
  end || ""

price_range_max_condition = \
  if max_price.present?
    ["price_pennies <= ?", (100 * max_price.fdiv(1.2)).round]
  end || ""

Rather than all of this being in the same method, it would be better to have those conditions come from dedicated methods. Things then would be a lot tidier.

cache_key_string = [
  base_conditions,
  current_store_condition,
  term_condition,
  price_range_min_condition,
  price_range_max_condition
].join('/')

cache_key = Digest::MD5.hexdigest(cache_key_string)

some_time_limit = 1.day # or some other suitable value

Rails.cache.fetch(cache_key, expires_in: some_time_limit) do  
  Product.
  joins("LEFT OUTER JOIN stores ON stores.id = products.store_id").
  where(base_conditions).
  where(current_store_condition).
  where(term_condition).
  where(price_range_min_condition).
  where(price_range_max_condition).
  order("#{sort_column} #{sort_direction}").
  all
end

Also, the LIKE query you've got in there will be slow. I suggest the usage of ThinkingSphinx or ElasticSearch.

An alternative would be to use pagination and get a selected number of results at a time. It will be a lesser strain on memory and you would be getting updated results every time. To accomplish this, you could pass a page argument to your method and do something like:

limit = 20 # show 20 results at a time

Product.
joins(joins_string).
where(conditions).
order(order_string).
offset((page - 1) * limit). # value of first page is 1
limit(limit)

Upvotes: 3

Related Questions