[rails] Optimisation - Count leads to many queries

[Fletch]

I have the following Model for an application that stores serial keys and their usage.

A product has many licenses, each license has many allocations (when they are in use by a user). Some licenses can only belong to a single user, some can belong to multiple.

class Product < ActiveRecord::Base
	has_many :licenses

	def total_allowed_users
		self.licenses.sum(:allowed_users) || 0
	end
	
	def total_available_licenses
		return self.total_allowed_users - self.total_used_licenses
	end
	
	def total_used_licenses
		count = 0;
		self.licenses.each do |license|
			license.allocations.each do
				count+=1;
			end
		end
		return count
	end
end

In my controller, I fetch all the products and include the relationships.

@products = Product.find(:all,  :include => {:licenses => :allocations})

<% for product in @products %>
Name: <%= product.full_name %>
Total: <%= product.total_allowed_users %>
Used: <%= product.total_used_licenses %>
Available: <%= product.total_available_licenses %>
<% end %>

However, this results in 3 queries for each product. Is there any way I can optimise this so less queries are used?

[MiiJaySung]

While there is probably a way to reduce the queries, you're best focusing on making sure you've optimised the database to have indexes in the right places (which I assume you've done). Secondly, try and optimise your views to make use of caching where possible. This will reduce a fair number of queries if you go about it right.