4

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.

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
Improve this question
4
  • slight note that when you enter .map function on Case B you're actually dropping to ruby. You can achieve the same result, similar to how you did it in Case A, and stay in SQL with pluck(:name) which is an AREL method. you may have to specify table.column, i.e. .pluck('table_name.name') where table_name is the table you're fetching the name records from. Commented Dec 22, 2016 at 21:40
  • It is only a small thing, so not going to add a full answer, but won't each fire multiple queries here? Won't find_each work better? Commented Dec 22, 2016 at 22:56
  • Thanks Dave. I will remember that. Commented Dec 27, 2016 at 8:18
  • Right dgmora. In the meantime I already rewrote few lines and now I am using hash array as a result instead of active record, where find_each cannot be applied. Commented Dec 27, 2016 at 8:21

5 Answers 5

Reset to default
1

Case A

A best thing to do here is to cache count number of active suppliers in Category model.

  • Add active_supliers_count column in Category model
  • Add after_save hook to suppliers model and when it becomes active/inactive increment/decrement categories's counter

Case B

The problem here is the same as in Case A, you need to add cache counter.

It seems you make multiple includes in a loop. You can make only one with everything thats needed.

For example

Category.includes(children: [:image, :products])
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks Nikita for your tips. I am already using cache count numbers, but the main issue is with the code below (see also UPDATE 2 section in the main description).
Category.includes(:translations).where(categories: {:active => true, :ancestry_depth => 0..2 }, category_translations: {:locale => I18n.locale.to_s} ).arrange_serializable(:order => 'category_translations.name')
0

Is there a reason you need the pluck on

self.suppliers.where(:active => true).pluck(:id).count?

It will likely be faster to just use self.suppliers.where(:active => true).count because the pluck returns an array of every id, and it sounds like you just need the count there.

Improve this answer

1 Comment

I corrected this one. It was just an experiment with a belief it will help with the performance as based on the information I read so far it would not load the full record in memory, but just part of it. Anyway there does not seem to be any significant performance difference :/. Also I uploaded a screen shot of the menu as reference above (update 1).
0

Your queries are obviously resources intensive. Indexes make them really fast. Which database are you using? What are your table structures like? Have you indexed category_id in translations table(foreign key attribute should always be indexed). What type of column is locale? Is it a string? By default string gets stored as 255 varchar by rails, and the maximum a mysql2 can index is varchar of 190 length. So to correct that you can use limit in your migration because I don't think locale would need a lot of space. Then you'll be able to index it. This will make your queries really fast.

Improve this answer

4 Comments

Thanks @gaurav-sobti for your tips. I updated the main description (UPDATE 3 section). I use Posgres database and both category_id and locale are indexed. Locale is a string, two characters long (e.g. "cs", "en"). So it is already configured as you have proposed.
@Miroslav by default t.string makes a varchar(255), you don't need that. Make a migration and add this line: change_column :category_translation, :locale, :string, :limit => 10
This will reduce memory consumption as well. Could you please try this and tell me if time of the query decreases or not?
@Miroslav Also could you please divide your query in two parts and then benchmark? Please check whether arrange_serializable is the culprit for making the query slow or whether it's an indexing issue.
0

Case A

You are querying the count of suppliers for each and every category. You can either cache the count of categories as Nikita suggested, or how about getting the counts for each category in the controller using ruby group_by? 

active_suppliers = Suppliers.where(:active => true).select('id, category_id')
@category_suppliers = active_suppliers.group_by(&:category_id)
# =>  {2 =>[#<Supplier id: 4, category_id: 2>, ...], 3 => ...}

(You would want this in a model method). Then in your view, do something as with that hash as:

<span class="txt-alt">
  (<%= @category_suppliers[category.id].length %>)
</span>

Case B

I haven't used the ancestry gem but from what I read it attempts to pull your associated objects in one fat query, yeah? I think one big issue is the serializer. When using arrange_serializable i'd guess the gem has to traverse the whole structure to serialize it, But then you go on ahead and access each node of the data yet again to render your page.

Why not instead of: 

<% children_sorted = root['children'].sort_by { |child| child['products_sum_count'] }.reverse!.first(3)

You do: 

<% children_sorted = root.children.sort_by { |child| child['products_sum_count'] }.reverse!.first(3)

Also, another thing about instancing objects for path helpers. You do a lot of:

<%= link_to Category.find(sub_cat['id']).category_link, ...%>

But since you already have the id, why not just build it with the helper like this? 

<%= link_to category_path(sub_cat['id']), ...%>

It seems a waste of resources to query the database and instance each category just for building a 'categories/:id' type of link? or is this link more complex and it requires more data? If so, why not build a helper for it that uses the data you have at that point without querying the database?

Note: After you fix the speed problem, you should really tackle the maintainability of this code. If someone other than you has to find and modify the specific piece of code that sorts the categories by the subproduct count, (...root['children'].sort_by { |child| child['products_sum_count'] }.reverse!.first(3)) Wouldn't it be easier if it was a model/class method with a descriptive name, which you can unit test, instead of right there mixed with the view? Also consider extracting logical parts of the view code into partials that you pass the variables into.

Improve this answer

Comments

0

Case A

A best thing to do here is to cache count number of active suppliers in Category model.

Case B

It seems you make multiple includes in a loop. You can make only one with everything that's needed.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.