I have two cases where it takes long time to load the pages and am not sure how to make the DB calls faster as I started with RoR recently. 

Case A
======

I am trying to show root categories (using ancestry gem) and number of suppliers who are in associated with any of these root categories. There are 15 root categories in total. 

The result is something like: 

* Fashion (14)
* Auto (26)
* ...

### suppliers.html.erb

```
<% Category.roots.each do |category| %>
    <li class="pointer txt-hover-grey mili-margin-bottom">
        <span class="micro-margin-right">
            <%= link_to category.name, category_suppliers_path(category), :title => category.name %>
        </span>
        <span class="txt-alt">
            (<%= category.suppliers_count_active %>)
        </span>
    </li>
<% end %>

```

### category.rb

```
def suppliers_count_active
    self.suppliers.where(:active => true).pluck(:id).count
end

```

Case B (Ancestry gem)
=====================

This is related to main categories menu (like you can find in any eshop). There are as noted 15 root categories (level 0), then every root category has 3 subcategories (45 in total) and every subcategory has around 5 subsubcategories (so around 225 in total). For every subsub-category, I am also populating number of products in that category. 

The result is something as follows:

* Fashion

	+ Mens
		- T-Shirts (34555)
		- Underwear (14555)
		- ...
	+ Women
		- T-Shirts (43000)
		- Underwear (23000)
* Sport

	+ Snowboarding
		- XYT (2323)
		- ...
	+ ...
* ...

### categories\_menu.html.erb

```
<div class="content no-padding padding-static relative table menu-nr">
 <!--   root_categories -->
<% Category.includes(:image, :products).serializable.each do |root| %>
    <div class="table-center container-center full-h categories-trigger">
        <%= link_to Category.find(root['id']).category_link, :title => root['name'] do %>                
            <div class="uppercase full-h size-tiny semi-bold txt-hover-main_light semi-padding-top semi-padding-bottom">
            <%= root['name'] %>
            </div>
        <% end %>
        <div class="categories-hide categories-dropdown shadow z-1000 bg-white txt-black size-tiny">
            <div class="table full-w inwrap-dropdown">
                <div class="cell">
                    <div class="table dropdown-left">
                    <% 
                        children_sorted = root['children'].sort_by { |child| child['products_sum_count'] }.reverse!.first(3)
                        children_sorted.each do |cat| %>
                        <div class="cell container-left">
                            <div class="table">
                                <div class="cell container-top">
                                    <div class="mili-margin-left mili-margin-right">
                                    <% cat2 = Category.find_by(:id => cat['id'])
                                    if !cat2.image.blank? %>
                                        <%= image_tag(cat2.image.image.url(:small), :title => cat2.image.title, :alt => cat2.image.title, :class => "img-category") %> 
                                        <% end %>    
                                    </div>
                                </div>
                                <div class="cell">
                                    <h5 class="mili-margin-bottom">
                                        <%= link_to "#{cat['name']}", Category.find(cat['id']).category_link, :title => cat['name'] %>
                                    </h5>
                                    <div class="txt-grey_dark semi_bold mili-margin-bottom">
                                    <% 
                                       # cat_children = cat.children.includes(:products)
                                        cat['children'].first(7).each do |sub_cat| 
                                    %>  
                                        <%= link_to Category.find(sub_cat['id']).category_link, :title => sub_cat['name'], :class => "block txt-hover-grey micro-margin-bottom" do %>
                                            <%= "#{sub_cat['name']}" %> <span class="txt-alt"><%= "(#{sub_cat['products_sum_count']})" %></span>
                                        <% end %>
                                    <% end %>
                                    </div>

                                    <%= link_to "Další kategorie >", Category.find(cat['id']).category_link, :class => "semi-margin-top block txt-alt txt-hover-alt_dark" %>
                                </div>
                            </div>
                        </div>
                    <% end %>
                    </div>
                </div>
                <div class="cell bg-grey_light semi-padding-left">
                    <div class="table">
                        <div class="cell container-left">
                            <div class="mili-margin-left mili-margin-right">
                                <h5 class="txt-alt mili-margin-bottom">
                                    <%= t(:menu_suppliers_title) %>
                                </h5>

                                <% 
                                suppliers = Supplier.joins(:categories).where(:active => true, categories: { :id => root['id'] }).last(3)
                                suppliers.each do |supplier| 
                                %>
                                <% cache supplier do %>
                                <div class="table relative mili-margin-bottom">
                                    <div class="cell inline relative wrap-shop">
                                        <div class="absolute-center-nr center inwrap-shop inwrap-shop-rohlik btn border">
                                            <%= link_to supplier_path(supplier), :title => "#{t(:menu_suppliers_link_title)} #{supplier.name}" do %>
                                                <%= image_tag(supplier.image.image.url(:small), :alt => supplier.image.title) if !supplier.image.blank? %>
                                            <% end %>
                                        </div>
                                    </div>
                                    <div class="col inline semi-margin-left-nr">
                                        <div class="table txt-avatar-small full-w">
                                            <div class="table-center">
                                                <%= link_to supplier.name, supplier_path(supplier), :title => "#{t(:menu_suppliers_link_title)} #{supplier.name}", :class => "semi-bold block micro-margin-bottom" %>
                                                <div class="txt-alt">
                                                    <%= t(:homepage_suppliers_logo_text, :commission => supplier.commission_donated, :commission_type => supplier.commission_type ) %>
                                                </div>
                                            </div>
                                        </div>
                                    </div>
                                </div>
                                <% end %>
                                <% end %>
                                <span class="block txt-alt txt-hover-alt_dark half-margin-bottom">
                                </span>
                                <%#= link_to t(:menu_suppliers_link_others), category_suppliers_path(:id => root['id']), :title => t(:menu_suppliers_link_others), :class => "block txt-alt txt-hover-alt_dark half-margin-bottom" %>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
        <div class="categories-hide wrap-categories-arrow relative">
            <div class="categories-arrow absolute z-1000">
            </div>
        </div>
    </div>
<% end %>
</div>

```

### category.rb

```
def self.serializable
  Category.includes(:translations).where(categories: {:active => true, :ancestry_depth => 0..2 }, category_translations: {:locale => I18n.locale.to_s} ).arrange_serializable(:order => 'category_translations.name') 
end

def category_link
   category_path(self)
end

```

Both of these cases takes several seconds to load. Any advices really appreciated. 

Thank you,
Miroslav

UPDATE 1:
=========

Here you can see the output from NewRelic. It is related to the Case B, after an attempt to implemented dalli memcache and identity\_cache. Also I uploaded a screen shot of the menu how it looks like.

* [NewRelic Output](https://i.stack.imgur.com/KwzrZ.png)
* [Menu](https://i.stack.imgur.com/Rt8PX.png)

UPDATE 2:
=========

The most time consuming part seems to be the following code: 

```
result = Benchmark.ms { Category.includes(:translations).where(categories: {:active => true, :ancestry_depth => 0..2 }, category_translations: {:locale => I18n.locale.to_s} ).arrange_serializable(:order => 'category_translations.name') }
=> 7207.116272300482

```

It generates a hash of all active categories (around 1000) in hierarchy, so I can render it properly for the menu. 

Not sure how to optimize this part.

UPDATE 3
========

I use postgres database. 

**CategoryTranslation table**

```
create_table "category_translations", force: :cascade do |t|
  t.integer  "category_id", null: false
  t.string   "locale",      null: false
  t.datetime "created_at",  null: false
  t.datetime "updated_at",  null: false
  t.string   "name"
end

add_index "category_translations", ["category_id"], name: "index_category_translations_on_category_id", using: :btree
add_index "category_translations", ["locale"], name: "index_category_translations_on_locale", using: :btree

```

**Category Table**

```
create_table "categories", force: :cascade do |t|
  t.string   "name"
  t.boolean  "active",             default: false
  t.integer  "level"
  t.datetime "created_at",                         null: false
  t.datetime "updated_at",                         null: false
  t.string   "ancestry"
  t.string   "mapping"
  t.integer  "products_count",     default: 0,     null: false
  t.integer  "products_sum_count", default: 0
  t.integer  "ancestry_depth",     default: 0
  t.string   "category_link"
  t.string   "image_link"
end

add_index "categories", ["ancestry"], name: "index_categories_on_ancestry", using: :btree

```