How do I improve these queries' performance?

I have a long complicated home page where a company is shown, for each project, information about recent events. The idea is that they have a kind of data-heavy information center from which they can monitor all activity.

I've had trouble getting this page to perform well - two days ago local load times were 4.5s(!) and they are currently at ~2.5s(!). The most alarming part about this horrible performance is that these are the load times with only 3 projects and practically no events. Performance on the live app is slightly better, but not nearly enough.

Purpose: Improve load time on home page

Here are the current queries.

# controller
@projects = @company.projects.order("project_title ASC").includes({:events => :owner}).search(params[:search], params[:page])

# view
@projects.each do |project|
  @events = project.events.where(:active => true).includes(:owner).order("priority DESC")
end

Removing the .where(:active => true).includes(:owner).order("priority DESC") is shaving off 1.1 seconds on an app with only 3 projects and 4 events in total.

How should these queries be written optimally? Should indexing play a role in this case?

I've been playing around with database indexes for the looped query in th开发者_开发问答e view but I haven't gotten one to cut down the time yet.

Your .includes(:events => :owners) is not doing what you think, as when you call .where on events later you have to retrieve from the data base again.

Also, if your search method is using the events and owners table you may want to used .joins() instead of .includes().

I would make sure you have indexes on every foreign key (xxx_id) and on events active.

I would also give this a shot (not sure if it works, may need some tweaking):

class Project < AR::Base
  has_many :events
  has_many :active_events,
    :class_name => 'Event',
    :conditions => {:active => true},
    :order => "events.priority DESC"
    :include => :owner
end

#in controller:
@projects = @company.projects.order("project_title ASC").includes(:active_events).search(...)


#in view: (abstract this to a render collection method if possible)
@project.each do |project|
  @events = project.active_events
end