Fetching items from database with "Outstanding" status

Question

I am fetching around more than 300000 items through a query:

@items = current_company.item_masters.user_hierarchies_filter(current_user).accessible_by(current_ability).paginate(:conditions => condition.compile, :include=> includable_tables,
        :order => (sort_column + " " + sort_direction),
        :page=> new_params[:page],:per_page => new_params[:per_page_records].blank? ? 10 : new_params[:per_page_records])

And the scope used in that query is :

named_scope :user_hierarchies_filter, lambda { |user|
    {select: "DISTINCT item_masters.id", conditions: ["item_masters.company_id = ?", user.company_id]}
  }

It produces this log message:

ItemMaster Load (43445.6ms)   SELECT DISTINCT item_masters.id FROM `item_masters` WHERE (item_masters.status IN ('Outstanding')) AND (((item_masters.company_id = 1) AND (`item_masters`.company_id = 1))) ORDER BY item_number asc LIMIT 331230, 10

It takes around 44 seconds to load items. Can anyone please suggest any option to reduce the loading time of this query?

Answer 1

As far as I can see this is a very old Rails code. Please follow these steps:

1. Refactor the code a little bit:

   scope user_hierarchies_filter: lambda { |user|
     select('distinct item_masters.id').
     where("item_masters.company_id = ?", user.company_id)
   }
   

and

    @items = current_company.item_masters.
    user_hierarchies_filter(current_user).accessible_by(current_ability).
    includes(includable_tables).
    order(sort_column + " " + sort_direction).
    paginate(page: new_params[:page],
             per_page: new_params[:per_page_records].blank? ? 10 : new_params[:per_page_records])

2. from the generated query:

   SELECT DISTINCT item_masters.id 
   FROM `item_masters` 
   WHERE (item_masters.status IN ('Outstanding')) 
   AND (((item_masters.company_id = 1) 
   AND (`item_masters`.company_id = 1))) 
   ORDER BY item_number asc LIMIT 331230, 10
   

I propose a couple of things:

• Remove the duplicated condition on the company_id.

• Explain the query and try to see why is so slow. I guess you are missing an index on the status, company_id and item_number columns: do things get better if you run the following migration?

  add_index :item_masters, :company_id
  add_index :item_masters, :status
  add_index :item_masters, :item_number
  

Answer 2

Rails is not well suited for batch operations. Depending on your classes and depentencies instantiating such a hugh amount of objects (300,000 is a lot for Rails) costs time and memory. You should test if the query takes a long time or object creatation. Also you should have a look at your memory consumption.

I experienced a performance decrease with memory consumption increase. Maybe your logic allows you to use find_in_batches. With this not all objects are loaded at once. Instead only a batch of objects is loaded and evaluated.