如何改善这些查询的性能？

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.

我很难让这个页面表现良好 - 两天前本地加载时间是4.5秒（！），它们目前处于~2.5秒（！）。关于这种糟糕表现的最令人震惊的部分是，这些只是3个项目的加载时间，几乎没有任何事件。实时应用程序的性能略好一些，但还不够。

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.

删除.where（：active => true）.includes（：owner）.order（“priority DESC”）在一个只有3个项目和4个事件的应用程序上削减了1.1秒。

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 the view but I haven't gotten one to cut down the time yet.

我一直在为视图中的循环查询玩数据库索引，但我还没有减少时间。

1 个解决方案

#1

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.

您的.includes（：events =>：所有者）没有按照您的想法进行操作，就像您调用。之后的事件，您必须再次从数据库中检索。

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

此外，如果您的搜索方法使用的是事件和所有者表，则可能需要使用.joins（）而不是.includes（）。

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

我会确保每个外键（xxx_id）和活动事件都有索引。

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

#1