Page takes so long to load and 3 mins on production server, how do I optimise this?

[AfolabiOlaoluwa]

Controller

module Admin
  class AccountsController < BaseController
    load_resource :except => [:index, :batch_update]
    authorize_resource

    def index
      @grid = AccountsGrid.new(params[:accounts_grid])
      @grid_items = @grid.assets.page(params[:page]).per(20)
      @attention_items = (AccountsGrid.new() { |scope| scope.needs_attention }).assets
    end

account_grid.rb

Account Table is just 1,000 records on local system

Member Table is just 100 records on local system

Versions Table is just 380 0n local system

Total time for the log Views: 412.6ms | ActiveRecord: 16.7ms

class AccountsGrid
  include Datagrid
  include Datagrid::Naming
  include Datagrid::ColumnI18n

  scope do
    Account.includes(:member).includes(:versions).order(created_at: :desc)
  end

  filter(:currency, :enum, :select => Currency.all.map { |c| [c.code.upcase, c.id] })
  filter(:audit_state, :enum, :select => Account::STATES.map { |s| [s.to_s.titleize, s] })
  filter(:created_at, :datetime, :range => true) # for default date range use `:default => proc { [DateTime.strptime(ENV['LAUNCH_DAY'], ENV['DATETIME_FORMAT']), Time.current] })`
  filter(:keyword, :string) do |value|
    self.where(""\
        "LOWER(members.email) LIKE LOWER(?) OR "\
        "LOWER(members.first_name) LIKE LOWER(?) OR "\
        "LOWER(members.last_name) LIKE LOWER(?) OR "\
        "LOWER(accounts.id) LIKE LOWER(?) OR "\
        "LOWER(accounts.balance) LIKE LOWER(?) OR "\
        "LOWER(accounts.locked) LIKE LOWER(?) OR "\
        "LOWER(accounts.balance + accounts.locked) LIKE LOWER(?) OR "\
        "LOWER(accounts.coupon_balance) LIKE LOWER(?)",
        "%#{value}%", "%#{value}%", "%#{value}%", "%#{value}%", "%#{value}%", "%#{value}%", "%#{value}%", "%#{value}%"
        ).references(:members)
  end

  column(:id)
  column(:created_at) { created_at.strftime '%Y-%m-%d %I:%M:%S %p' }
  column(:name, :html => true) { |a| link_to a.member.name(include_middle_name: false).truncate(20), admin_member_path(a.member), target: '_blank' }
  column(:currency) { currency_text }
  column(:amount, :html => true) { |a| content_tag :code, "#{a.currency_text} #{a.amount.to_s(:delimited)}", class: 'text-info' }
  column(:balance, :html => true) { |a| content_tag :code, "#{a.currency_text} #{a.balance.to_s(:delimited)}", class: 'text-info' }
  column(:locked, :html => true) { |a| content_tag :code, "#{a.currency_text} #{a.locked.to_s(:delimited)}", class: 'text-info' }
  column(:coupon_balance, :html => true) { |a| content_tag :code, "#{a.currency_text} #{a.coupon_balance}", class: 'text-info' }
  # column(:reconciled, :html => true) { |a| content_tag :code, "#{a.currency_text} #{a.reconciled}", class: 'text-info' }
  column(:audit_state, :html => true) {|a| content_tag :span, a.audit_state_text, class: "label label-#{ state_color :account_audit_states, a.audit_state }" }
  column(:actions, :html => true) { |a| link_to t("actions.view"), admin_account_path(a), target: '_blank' }
end

Logs

Started GET “/admin/accounts” for 127.0.0.1 at 2019-04-12 13:52:05 +0100
Processing by Admin::AccountsController#index as HTML
  Member Load (0.3ms)  SELECT `members`.* FROM `members` WHERE `members`.`disabled` = 0 AND `members`.`id` = 4 ORDER BY `members`.`id` ASC LIMIT 1
   (0.3ms)  SELECT SUM(`trades`.`volume`) AS sum_id FROM `trades` WHERE `trades`.`currency` = 101 AND (created_at > ‘2019-04-11 12:52:05’)
   (0.3ms)  SELECT SUM(`trades`.`volume`) AS sum_id FROM `trades` WHERE `trades`.`currency` = 102 AND (created_at > ‘2019-04-11 12:52:05’)
   (0.3ms)  SELECT SUM(`trades`.`volume`) AS sum_id FROM `trades` WHERE `trades`.`currency` = 103 AND (created_at > ‘2019-04-11 12:52:05’)
   (0.3ms)  SELECT SUM(`trades`.`volume`) AS sum_id FROM `trades` WHERE `trades`.`currency` = 104 AND (created_at > ‘2019-04-11 12:52:05’)
   (0.3ms)  SELECT SUM(`trades`.`volume`) AS sum_id FROM `trades` WHERE `trades`.`currency` = 201 AND (created_at > ‘2019-04-11 12:52:05’)
   (0.3ms)  SELECT SUM(`trades`.`volume`) AS sum_id FROM `trades` WHERE `trades`.`currency` = 202 AND (created_at > ‘2019-04-11 12:52:05’)
   (0.3ms)  SELECT SUM(`trades`.`volume`) AS sum_id FROM `trades` WHERE `trades`.`currency` = 203 AND (created_at > ‘2019-04-11 12:52:05’)
   (0.3ms)  SELECT SUM(`trades`.`volume`) AS sum_id FROM `trades` WHERE `trades`.`currency` = 205 AND (created_at > ‘2019-04-11 12:52:05’)
   (0.3ms)  SELECT SUM(`trades`.`volume`) AS sum_id FROM `trades` WHERE `trades`.`currency` = 206 AND (created_at > ‘2019-04-11 12:52:05’)
  Account Load (0.8ms)  SELECT `accounts`.* FROM `accounts` WHERE `accounts`.`member_id` = 4
  Account Load (2.4ms)  SELECT `accounts`.* FROM `accounts` WHERE `accounts`.`audit_state` IN (‘imbalanced’) ORDER BY `accounts`.`created_at` DESC
  Rendered admin/shared/_filter.html.slim (30.1ms)
   (0.7ms)  SELECT COUNT(*) FROM `accounts`
  Rendered datagrid/_order_for.html.erb (16.5ms)
  Rendered datagrid/_order_for.html.erb (0.9ms)
  Rendered datagrid/_order_for.html.erb (0.7ms)
  Rendered datagrid/_order_for.html.erb (0.8ms)
  Rendered datagrid/_order_for.html.erb (1.1ms)
  Rendered datagrid/_order_for.html.erb (1.0ms)
  Rendered datagrid/_order_for.html.erb (1.3ms)
  Rendered datagrid/_head.html.erb (43.0ms)
   (0.4ms)  SELECT COUNT(count_column) FROM (SELECT 1 AS count_column FROM `accounts` LIMIT 20 OFFSET 0) subquery_for_count
  Account Load (1.8ms)  SELECT `accounts`.* FROM `accounts` ORDER BY `accounts`.`created_at` DESC LIMIT 20 OFFSET 0
  Member Load (0.6ms)  SELECT `members`.* FROM `members` WHERE `members`.`id` IN (139, 138, 137)
  AccountVersion Load (1.5ms)  SELECT `account_versions`.* FROM `account_versions` WHERE `account_versions`.`account_id` IN (1095, 1093, 1091, 1096, 1092, 1094, 1090, 1089, 1088, 1082, 1083, 1084, 1081, 1086, 1087, 1085, 1076, 1073, 1079, 1075)
  IdDocument Load (0.3ms)  SELECT `id_documents`.* FROM `id_documents` WHERE `id_documents`.`member_id` = 139 LIMIT 1
  Rendered datagrid/_row.html.erb (4.9ms)
  Rendered datagrid/_row.html.erb (1.8ms)
  Rendered datagrid/_row.html.erb (2.5ms)
  Rendered datagrid/_row.html.erb (2.9ms)
  Rendered datagrid/_row.html.erb (6.8ms)
  Rendered datagrid/_row.html.erb (11.8ms)
  Rendered datagrid/_row.html.erb (3.2ms)
  Rendered datagrid/_row.html.erb (1.5ms)
  IdDocument Load (0.5ms)  SELECT `id_documents`.* FROM `id_documents` WHERE `id_documents`.`member_id` = 138 LIMIT 1
  Rendered datagrid/_row.html.erb (6.2ms)
  Rendered datagrid/_row.html.erb (1.4ms)
  Rendered datagrid/_row.html.erb (2.4ms)
  Rendered datagrid/_row.html.erb (2.6ms)
  Rendered datagrid/_row.html.erb (4.5ms)
  Rendered datagrid/_row.html.erb (1.7ms)
  Rendered datagrid/_row.html.erb (4.4ms)
  Rendered datagrid/_row.html.erb (3.4ms)
  IdDocument Load (0.3ms)  SELECT `id_documents`.* FROM `id_documents` WHERE `id_documents`.`member_id` = 137 LIMIT 1
  Rendered datagrid/_row.html.erb (3.7ms)
  Rendered datagrid/_row.html.erb (2.0ms)
  Rendered datagrid/_row.html.erb (2.6ms)
  Rendered datagrid/_row.html.erb (1.4ms)
  Rendered datagrid/_table.html.erb (293.1ms)
  Rendered admin/accounts/index.html.slim within layouts/admin (364.4ms)
  Rendered shared/_svgs.html.slim (0.3ms)
  Rendered shared/_flash.slim (1.3ms)
Completed 200 OK in 612ms (Views: 412.6ms | ActiveRecord: 16.7ms) 
Not delivering sessions due to notify_release_stages :[“production”, “staging”, “review”]

[bogdan]

I believe that it is due to rendering. Rendering in rails is generally slow, but it is optimized in production env. Try launch your app in the production mode and see if there is any difference.

[AfolabiOlaoluwa]

It's way worse in Production. Literarily waiting for more than 3 mins for 1 million records to show/load

[bogdan]

Indexes are not fully supported for the LIKE operation especially with downcase. Your requests to scan 1m records with LIKE will be unavoidably slow: https://stackoverflow.com/questions/11418932/performance-of-like-queries-on-multmillion-row-tables-mysql

Please attach the log from your production request so that we can say for sure what is slow.

[bogdan]

Closing by inactivity. Please follow up if still actual.