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?

• coorasse: can you add the ruby code you wrote, please?
• huzefa biyawarwala: @coorasse : Added, please let me know how can i reduce the execution time. Thnx :)
• Mike Brant: Please show your table definition.

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
  

• huzefa biyawarwala: Thnx for the answer. Somethings, i want to mention are i have already had an index on all the columns that you have mentioned. Can you suggest me how can i remove that duplicate company_id condition ? . I am asking this because i have given that condition only in the scope so i am confused how to remove it. Thnx again :)
• coorasse: I guess your accessible_by column is already adding this filter. The ability.rb file should help you to point out where. Maybe you have something like can :read, :item_masters, company_id: 1. If none of those will help I would then try to explain the query and see where it takes longer. As last step copy/paste it into a sql editor and try to remove pieces one by one so you will see which one is slowing the query down. Maybe the limit option?

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.