Improve performance of simple searching method
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:
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
endAnd 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)
endI 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.
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