-1

I'm using Active Record to send a few queries to a postgres database.

I have a User model, a Business model and a joiner named UserBiz.

The queries I mentioned go through the entire UserBiz collection and then filter out the businesses that match user provided categories and searches.

 if !params[:category].blank?
    dataset = UserBiz.all.includes(:business, :user).select{|ub| (ub.business.category.downcase == params[:category].downcase) }
 else 
    dataset = UserBiz.all.includes(:business, :user)
 end

 if !params[:search].blank?
    dataset = dataset.select{|ub| (ub.business.summary.downcase.include? params[:search].downcase) || (ub.business.name.downcase.include? params[:search].downcase) }
 end

These "work" but the problem is when I threw a quarter million UserBizs into my database to see what happens, one search or category change takes 15 seconds. How can I make these queries faster?

Eyeslandic
  • 14,553
  • 13
  • 41
  • 54
AnthonyH
  • 25
  • 2
  • 7

2 Answers2

1

select in your code loads everything into memory which is very bad for performance when dealing with a lot of records.

You have to do the filtering in the database, with something like this.

UserBiz
  .includes(:business, :user)
  .where("LOWER(businesses.category) = LOWER(?)", params[:category])
Eyeslandic
  • 14,553
  • 13
  • 41
  • 54
-1

it's slow because you selecting all data from UserBiz. Try out pagination. pagy, will_paginate etc.

togi
  • 814
  • 5
  • 13
  • I have already implemented my own version of pagination in my app. What I need help with is the query to go through all of the businesses in the db and select the ones with categories or search terms that the user is looking for. Displaying them is not really an issue. I'm using React Native for the frontend and I have a Flatlist that renders 7 businesses at a time. Everything runs smooth until I search or change categories because of those select methods. I realize that they have to go through every UserBiz but how else would a user be able to search the entire app? – AnthonyH Jun 22 '21 at 06:57
  • What would be nice would be a way to return the first x amount of results that match the category or search. I have tried .first(x), and .take(x) etc but those methods still seem to go through the entire collection before returning results. .limit can only be called before the select which defeats the purpose of searching the entire collection. – AnthonyH Jun 22 '21 at 07:17
  • why don't you paginate search result? i mentioned you are selecting all data that's why your query is slow. – togi Jun 22 '21 at 08:49
  • I am unfamiliar with those libraries. It just seems like overkill to make a basic filter query as it stands because I have my own pagination after the results come back from the db so I would need to refactor that etc I will save your suggestion as a last resort – AnthonyH Jun 22 '21 at 15:06