CODEWITHSUNDEEP
ruby-on-railspostgresqlheroku
Huy
Huy

Reputation: 11206

Improving Rails user query on huge table (timing out on Heroku)

I have an issue with one of my pages not loading on Heroku because my query on my Users table is timing out. There is a significant amount of records.

I use the result of the query to render my users as options in a select dropdown tag.

I am currently using ActiveRecord to fetch my records.

User.where(filters).order(first_name: :asc, last_name: :asc).select(:first_name, :last_name, :id).map{ |u| [u.full_name, u.id] }

Filters is just a hash that consists of attributes as keys and their respective value. The value can also be an array (i.e. { status: ['active', 'inactive'] }).

I have tried using find_each to load batches of users, but it is still not good enough.

users = []
User.where(filters).order(first_name: :asc, last_name: :asc).select(:first_name, :last_name, :id).find_each do |group|
 users += group.map{ |u| [u.full_name, u.id] }
end

I don't think full_name is an issue since it is just a string concatenation method:

def full_name
  return nil if first_name.blank? || last_name.blank?
  "#{first_name} #{last_name}"
end

How do I reduce my query time?

Edit: Adding an index improved my query performance a lot, but is still timing out on Heroku.

Upvotes: 0

Views: 44

Answers (1)

penner
penner

Reputation: 2737

User.where(filters).order(first_name: :asc, last_name: :asc).select("last_name||', '||first_name as full_name, first_name, last_name, id")

Loading all the records into memory just to concatenate strings is a big performance hit. Also having a select of 500+ users is a bad idea. You should look into an api endpoint and an ajax typeahead search.

Concatenating the strings in the select with an alias (using as) will allow you to access the alias as if it were a column, e.g.:

@users.map{|u| u.full_name}

Upvotes: 1

Related Questions