改进查询/视图加载时间[Ruby on Rails]

Question

我在Rails应用程序的一个页面上遇到了一些非常慢的加载时间。我在尝试提高性能方面取得了一些进展，但没有我希望的那么多。我想知道我是否在做一些愚蠢的事情来导致自己这种悲伤，或者是否有人可以为我提供建议，以更好地优化我的模型/迁移/查询/视图。我正在运行Rails 3.1和使用PostgreSQL。这是我到目前为止所尝试的:

• 添加了(:items =>:category)到控制器的查询，作为提前获取所有ItemCategories的尝试，而不是在outfits_helper需要时运行额外的查询。
• 将部分呈现为集合，而不是手动遍历数组/关系
• 为正在被有问题的控制器动作/视图访问的表添加索引
  • 服装表:user_id和category_id
  的索引
  • items表:user_id和category_id
  的索引
  • outfit_items表:item_id、outfit_id和[outfit_id、item_id]的索引

下面是我的代码的相关部分:

<标题>控制器h1> 模型

# outfit.rb
has_many :items, :through => :outfit_items
belongs_to :category, :class_name => 'OutfitCategory'
scope :does_not_belong_to_user, proc {|user| where('user_id != ?', user.id) }
scope :in_category_with_id, proc {|cat_id|
  if cat_id.blank?
    scoped
  else
    where(:category_id => cat_id)
  end
}

.

# outfit_item.rb
belongs_to :item
belongs_to :outfit

.

# item.rb
has_many :outfit_items
has_many :outfits, :through => :outfit_items
belongs_to :category, :class_name => 'ItemCategory'

.

# item_category.rb
has_many :items, :foreign_key => 'category_id'

.

outfit_category.rb
has_many :outfits, :foreign_key => 'outfit_id'

<标题>迁移h1> .

# create_outfit_items.rb
def self.up
  create_table :outfit_items, :id => false do |t|
    t.column :item_id, :integer
    t.column :outfit_id, :integer
    t.timestamps
  end
  add_index :outfit_items, :item_id
  add_index :outfit_items, :outfit_id
  add_index :outfit_items, [:outfit_id, :item_id]   
end

.

# create_items.rb
def self.up
  create_table :items do |t|
    t.column :category_id, :integer
    t.column :user_id, :integer
    t.timestamps
  end
  add_index :items, :category_id
  add_index :items, :user_id
end

.

# outfits_controller.rb
NUM_OUTFITS_PER_PAGE = 8
def other_users_outfits
  # using Kaminari for pagination
  @outfits = Outfit.does_not_belong_to_user(current_user).in_category_with_id(category_id).includes(:items => :category).page(params[:page]).per(NUM_OUTFITS_PER_PAGE)
end

0

.

# create_outfit_categories.rb
def self.up
  create_table :outfit_categories do |t|
    t.column :name, :string, :limit => 100, :null => false
    t.timestamps
  end
end

<<h1>视图/h1>

# other_users_outfits.html.haml
= render :partial => 'other_users_outfit', :collection => outfits, :as => :outfit

.

# _other_users_outfit.html.haml
.outfit
  .primary-items
    = render :partial => 'other_users_outfit_primary_item', :collection => primary_outfit_items_top_to_bottom(outfit), :as => :item
  .secondary-items
    = render :partial => 'other_users_outfit_secondary_item', :collection => secondary_outfit_items(outfit), :as => :item

.

# _other_users_outfit_primary_item.html.haml
= image_tag item.image_url(:outfit_item), :class => 'primary-outfit-item'

.

# _other_users_outfit_secondary_item.html.haml
= image_tag item.image_url(:baby_thumb), :class => 'secondary-outfit-item'

<标题>辅助h1> 控制台输出

User Load (1.0ms)  SELECT DISTINCT users.id, users.* FROM "users" WHERE "users"."id" = 3 LIMIT 1
(0.5ms)  SELECT COUNT(count_column) FROM (SELECT 1 AS count_column FROM "outfits" WHERE (user_id != 3) LIMIT 8 OFFSET 0) subquery_for_count 
Outfit Load (0.7ms)  SELECT DISTINCT outfits.id, outfits.* FROM "outfits" WHERE (user_id != 3) ORDER BY outfits.created_at DESC LIMIT 8 OFFSET 0
OutfitItem Load (0.6ms)  SELECT "outfit_items".* FROM "outfit_items" WHERE "outfit_items"."outfit_id" IN (28, 27, 26, 25, 24, 23, 22, 21)
Item Load (2.2ms)  SELECT DISTINCT items.id, items.*, "items".* FROM "items" WHERE "items"."id" IN (18, 20, 23, 7, 6, 30, 4, 1, 17, 5, 15, 12, 9, 29, 10, 19, 3, 8, 13) ORDER BY items.created_at DESC
ItemCategory Load (0.5ms)  SELECT "item_categories".* FROM "item_categories" WHERE "item_categories"."id" IN (4, 6, 2, 1, 3, 7)
Rendered outfits/_other_users_outfit_secondary_item.html.haml (1.2ms)
Rendered outfits/_other_users_outfit_secondary_item.html.haml (0.1ms)
Rendered outfits/_other_users_outfit_primary_item.html.haml (2.8ms)
Rendered outfits/_other_users_outfit_secondary_item.html.haml (1.4ms)
Rendered outfits/_other_users_outfit_secondary_item.html.haml (0.9ms)
Rendered outfits/_other_users_outfit_primary_item.html.haml (1.2ms)
Rendered outfits/_other_users_outfit_secondary_item.html.haml (0.3ms)
Rendered outfits/_other_users_outfit_secondary_item.html.haml (0.9ms)
Rendered outfits/_other_users_outfit_primary_item.html.haml (2.7ms)
Rendered outfits/_other_users_outfit_secondary_item.html.haml (0.8ms)
Rendered outfits/_other_users_outfit_secondary_item.html.haml (0.7ms)
Rendered outfits/_other_users_outfit_primary_item.html.haml (1.2ms)
Rendered outfits/_other_users_outfit_secondary_item.html.haml (1.1ms)
Rendered outfits/_other_users_outfit_secondary_item.html.haml (0.3ms)
Rendered outfits/_other_users_outfit_primary_item.html.haml (2.2ms)
Rendered outfits/_other_users_outfit_secondary_item.html.haml (0.8ms)
Rendered outfits/_other_users_outfit_secondary_item.html.haml (0.3ms)
Rendered outfits/_other_users_outfit_primary_item.html.haml (3.4ms)
Rendered outfits/_other_users_outfit_secondary_item.html.haml (0.3ms)
Rendered outfits/_other_users_outfit_secondary_item.html.haml (0.0ms)
Rendered outfits/_other_users_outfit_primary_item.html.haml (2.6ms)
Rendered outfits/_other_users_outfit_secondary_item.html.haml (0.3ms)
Rendered outfits/_other_users_outfit_secondary_item.html.haml (0.3ms)
Rendered outfits/_other_users_outfit_primary_item.html.haml (2.1ms)
Rendered outfits/_other_users_outfit.html.haml (56.3ms)
   (0.5ms)  SELECT COUNT(*) FROM "outfits" WHERE (user_id != 3)
Rendered outfits/other_users.html.haml within layouts/application (2073.5ms)
Role Load (0.4ms)  SELECT "roles".* FROM "roles" WHERE "roles"."name" = 'admin' LIMIT 1
   (0.4ms)  SELECT 1 FROM "roles" INNER JOIN "roles_users" ON "roles"."id" = "roles_users"."role_id" WHERE "roles_users"."user_id" = 3 AND "roles"."id" = 1 LIMIT 1
Rendered layouts/_top_nav.html.haml (1.0ms)
   (0.6ms)  SELECT COUNT(*) FROM "items" WHERE "items"."user_id" = 3
Rendered layouts/_points_display.html.haml (5.7ms)
Outfit Load (0.6ms)  SELECT DISTINCT outfits.id, outfits.* FROM "outfits" WHERE "outfits"."user_id" = 3 ORDER BY outfits.created_at DESC
OutfitCategory Load (0.3ms)  SELECT "outfit_categories".* FROM "outfit_categories"

.

任何帮助将非常感激!

Answer 1

我认为唯一可以改进的是helper中的代码。你这么做有什么原因吗?

def outfit_item_in_category(outfit, cat_name)
  outfit.items.select{|item| item.category.name == cat_name }.first
end

而不是将此代码推入Item模型中的作用域?如果你有很多项，你基本上是对它们执行SELECT *，然后用Ruby进行过滤。