Topic: Improving response times, fast queries, slow scopes.

Hey all,

In my app I have organizations that has_many projects through participations. I also have a named_scope that determines what the latest project is for an organization:

named_scope :latest, lambda { { :conditions => ['projects.start_date < ? and projects.end_date > ?', time = Time.current, time],
                                :order => 'projects.start_date desc' } }

Not terribly complicated I think, it compares two columns with a date and orders by one of them. Looking at the database query results are returned by mysql in a matter of miliseconds (about 4) on my dev box.

However timing the query in console here's what happened:

>> t = Time.new
=> Sat Jun 27 22:48:42 +0200 2009
>> Organization.first.projects.latest ; nil
=> nil
>> puts "Total time to completion: #{Time.new - t}"
Total time to completion: 0.152634
=> nil

So 153 miliseconds to execute this query. But looking at the dev log:
  Organization Load (0.2ms)   SELECT * FROM `organizations` LIMIT 1
  Organization Columns (1.5ms)   SHOW FIELDS FROM `organizations`
  Project Load (1.3ms)   SELECT `projects`.* FROM `projects` INNER JOIN `participations` ON `projects`.id = `participations`.project_id WHERE ((`participations`.organization_id = 1)) AND ((projects.start_date < '2009-06-27 20:50:33' and projects.end_date > '2009-06-27 20:50:33') AND ((`participations`.organization_id = 1))) ORDER BY projects.start_date desc LIMIT 1

Clearly the database isn't taking much more than a handful of miliseconds, so it seems that Rails is taking about 150 miliseconds just to build the query, which seems a little high to me.

I can fragment cache the individual projects but I need this query to see what projects are to be shown to the current_user.

I don't have a whole lot of experience with optimizing this sort of thing so any pointers would be very much appreciated.

Re: Improving response times, fast queries, slow scopes.

Have you tried changing your development environment temporarily to ?:
config.cache_classes = true

A good chunk of that 150ms response time is the loading of all the ActiveRecord classes and a lot of meta-programming putting together named_scopes.  If you enable caching I'm going to guess the query drops down to somewhere in the neighborhood of 40-70ms.

Also of note, do you have an index specified on participations.project_id ?  If not your going to see performance degrade fairly quickly as the participations table grows.

I'm Chris Chandler, a Phoenix Ruby on Rails developer and startup junkie kicking ass at Flatterline, a Ruby on Rails web application development company.