CODEWITHSUNDEEP
sqlruby-on-rails
Teej
Teej

Reputation: 12873

optimizing memory intensive script

I currently have this code:

  shop_category.shop_sub_categories.each do |sub_category|
     products << sub_category.products 
   end 

   products = products.flatten.uniq.compact


   data = products.first(100).map do |product|
    {
      :name => CGI.unescapeHTML(product.name),
      :manufacturer => product.manufacturer,
      :detail => product.description,
      :sales_rank => product.sales_rank,
      :price => product.price,
      :retailer => product.retailer
    }
   end

I have found this pretty much memory intensive as it uses up to 96% of my CPU:

   shop_category.shop_sub_categories.each do |sub_category|
     products << sub_category.products 
   end

I am thinking of making a writing something in tune of a WHERE IN statement so that I might only query data from like so:

SELECT * FROM products JOIN shop_categorization ON product_id = products.id WHERE shop_sub_category_id IN (1,2,3,4,5)

I currently put on this script which does great. I am thinking if there is something that can be done to make this more Rails-y.

  sub_category_ids = shop_category.shop_sub_categories.map(&:id)
  sub_category_ids = sub_category_ids.join(',')
  products = Product.find_by_sql("SELECT * FROM products JOIN shop_sub_categorizations ON product_id = products.id WHERE shop_sub_categorizations.shop_sub_category_id IN (#{sub_category_ids}) LIMIT 100")

Upvotes: 0

Views: 91

Answers (1)

Ben Simpson
Ben Simpson

Reputation: 4049

If you just want the sub category ids, instead of:

  sub_category_ids = shop_category.shop_sub_categories.map(&:id)

You can instead select just the id column:

  sub_category_ids = shop_category.shop_sub_categories.select('id').map(&:id)

Additionally, to reconstruct your find_by_sql statement to work using the ActiveRecord methods you could do the following:

  products = Product.join("JOIN shop_sub_categorizations ON product_id = products.id").where(["shop_sub_categorizations.shop_sub_category_id IN (?)", sub_category_ids]).limit(100)

Remember that by not specifying the columns to select you are increasing the computational overhead of selecting back all columns and mapping them into memory.

EDIT:

Look at a process monitor to determine if your overhead is the Ruby script, or the database. It is possible that adding indexes to the database will decrease the CPU overhead of the database query. More information on SQL indexes here: http://www.w3schools.com/sql/sql_create_index.asp

Upvotes: 1

Related Questions