SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    mouse monkey
    Join Date
    Dec 1999
    Location
    UK
    Posts
    656
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    [rails] Optimisation - Count leads to many queries

    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.

    Code:
    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.

    Code:
    @products = Product.find(:all,  :include => {:licenses => :allocations})
    Code:
    <% 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?

  2. #2
    Resident Java Hater
    Join Date
    Jul 2004
    Location
    Gerodieville Central, UK
    Posts
    446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    http://virtualfunction.net - Rails Web Development
    http://squaremove.co.uk - Rails powered Property Listings


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •