Skip to main content

Improve performance of simple searching method

Rails • Asked by Ernesto Gimeno
Hi,

I have been building an app these days. The functionality is nothing fancy at all, I have to connect to a client's SOAP webservice, fetch some data, save it into my pg database and build a search functionality based on this data.

The search has to be performed on two tables,  both combined are like 80K rows. It needs to look for every word in the input text in several fields from these two tables, which have a classical assocation one to many. 

Previous to get my hands dirty I was looking at the choices I had to get the functionality done (ransack, searchkick, scoped_search etc), but I ended up trying first just vanilla Active Record and I was very surprised to find that I could achieve the functionality way easier than I thought and with an acceptable response time, about to 400ms active record time for the most expensive  queries in local.

So the problem is, the performance of this app in Heroku is way worse than in local (I'm developing using  a vagrant box btw). On average, queries take 2-3 times longer than in local, so the user experience goes from acceptable to poor. I was wondering If someone could help to improve my query. I'm also worried about how the background job that fetchs the data is also way les performant than in local and about some issues with the memory, but that's a different story though.

The relevant snippets are these:

`part_master.rb` where the search method is implemented:

class PartMaster < ApplicationRecord
  has_many :part_variants, foreign_key: 'sap_cod', primary_key: 'sap_cod'
  has_many :locations, foreign_key: 'sap_cod', primary_key: 'sap_cod'

  scope :con_stock, -> { where("stock > 0") }
  scope :planta, -> (planta) { where planta_cod: planta}

  def self.search(params)
    recordset = PartMaster.joins(:part_variants).all
    recordset = recordset.con_stock if params[:stock].present?
    recordset = recordset.planta(params[:planta]) if params[:planta].present?
    recordset = search_keywords(params[:search], recordset)
    recordset
  end

  private 

  def self.search_keywords(query, recordset)
    keywords = query.to_s.strip.split
    if query
      keywords.each do |keyword|
        recordset = recordset.where('part_masters.sap_cod ILIKE :q OR unaccent(descripcion_maestro) ILIKE unaccent(:q)
                                     OR fabricante ILIKE :q OR ref_fabricante ILIKE :q 
                                     OR fabricante_prov ILIKE :q OR ref_prov ILIKE :q', q: "%#{keyword}%")
      end
      recordset.distinct.order(:sap_cod)
    end
  end
end

And this is the call to the method from the controller:

  def index
    parts = params[:search].present? ? PartMaster.search(params) : PartMaster.none
    @parts = parts.page(params[:page]).per(50)
  end

I have an index in every searchable field. 

Thank you in advance!


Hi, today I have been trying something different, so I decided to give a try to pg_search, using the trigram extension which I read is more performant for ILIKE %% type of conditions, but it is being like 3-4 times slower than the previous snippet from the post above. I have only tried in local.

Any ideas about what can be happening? I would appreciate any help.

class PartMaster < ApplicationRecord
  include PgSearch

  has_many :part_variants, foreign_key: 'sap_cod', primary_key: 'sap_cod'
  has_many :locations, foreign_key: 'sap_cod', primary_key: 'sap_cod'

  scope :con_stock, -> { where("stock > 0") }
  scope :planta, -> (planta) { where planta_cod: planta}

  pg_search_scope :search_keywords,
                against: {sap_cod: :A, descripcion_maestro: :B, fabricante: :C, ref_fabricante: :D},
                ignoring: :accents,
                :associated_against => {
                    part_variants: [:fabricante_prov, :ref_prov]
                },
                using: {
                  tsearch: {dictionary: 'spanish'},
                  trigram: {dictionary: "english"}
                }

  def self.search(params)
    recordset = PartMaster.joins(:part_variants).all
    recordset = recordset.con_stock if params[:stock].present?
    recordset = recordset.planta(params[:planta]) if params[:planta].present?
    recordset = recordset.search_keywords(params[:search])
    recordset
  end

end





Login or Create An Account to join the conversation.

Subscribe to the newsletter

Join 24,647+ developers who get early access to new screencasts, articles, guides, updates, and more.

    By clicking this button, you agree to the GoRails Terms of Service and Privacy Policy.

    More of a social being? We're also on Twitter and YouTube.