CODEWITHSUNDEEP
mysqlruby-on-rails-3
amaseuk
amaseuk

Reputation: 2155

How to optimize and reduce database calls?

I'm calculating totals on the fly for a project but it is currently quite database intensive:

def sub_total(package)
  subtotal = 0.0
  self.quote_items.order("area_id").each do |item|
    subtotal += Unit.find(item.unit_id).highest_price(package)*item.quantity if item.unit
  end
  sprintf("%.2f", sub_total)
end

In Unit model:

def highest_price(package)
  highest_price = 0
  highest_id = 0
  self.options.where("options.id IN (SELECT option_id FROM options_packages WHERE package_id = '#{package.id}')").each do |option|
    if (option.price > highest_price)
      highest_price = option.price
      highest_id = option.id
    end
  end
  Quote.add_margin highest_price
end

At each iteration, it queries the database in the Unit.find method.

How can this be optimised? I'd appreciate any help or advice to clean this up! This project uses Rails 3.0.9 and Ruby 1.8.9.

Upvotes: 1

Views: 1490

Answers (2)

amaseuk
amaseuk

Reputation: 2155

I've updated price - this seems a bit more efficient for calculating the highest_price ... ?

highest_price = self.options.where("options.id IN (SELECT option_id FROM options_packages WHERE package_id = '#{package.id}')").order('price DESC').first.price
Quote.add_margin highest_price

Upvotes: 1

bfavaretto
bfavaretto

Reputation: 71908

I'm not very familiar with ruby, but I think you can get the max price directly from SQL. Something like this:

SELECT MAX(price) 
FROM options_packages 
WHERE package_id = "#{package.id}" 
GROUP BY package_id

Upvotes: 2

Related Questions