activerecord and slow DB connection: (n+1) queries: how to hit DB efficiently?

Question

I have a RoR app that hits the DB very often with individual queries. Problem is each query is taking like 100ms and some controller actions are taking too long to complete because of the amount of individual queries that ActiveRecord is generating.

Another problem is that I cannot use AR includes() (which would generate just one query) because I am having problems with AR-sqlserver-adapter and unicode strings ignoring indexes and I am removing unicode prefix from sql query by-hand (this is a long story...)

Example:

#model
class Company < ActiveRecord::Base
   has_one: city
end
#controller
sql_string = where(:code => ['12B1', '34C8', '87DD', ...]).to_sql
sql_string = sql_string.gsub("N'","'")  #remove unicode prefix
companies = find_by_sql(sql_string)
#companies = Company.where(:code => ['12B1', '34C8', '87DD', ...]).includes(:city) #I wish I could use this line

when I am accessing cities inside companies array, I see several individual queries for each city in the console, what slows down things a lot.

I would like to have 2 queries only: one for companies using SQL IN and one for cities.

I have done that by hand (using virtual attributes), but, is there a "Rails way" of doing it ?

What I have done so far (caution, ugly code ahead):

    def self.get_companies_by_code(code_array)
       comps = Company.where(company_code: ['12B1', '34C8', '87DD', ...])
       cities_id_array = comps.map {|c| c.city_id}.compact.uniq
       cities = City.find(cities_id_array)
       comps.each {|co| 
           co.virtual_city = cities.select{|ct| co.city_id==ct.id}.first }
       comps
   end

That include should have done it - what does the code that triggers the individual queries look like? — Frederick Cheung, Feb 25 '15 at 20:15
I have updated the question. Trying to simplify and abstract things did not work first time. Thanks. — Fernando Fabreti, Feb 25 '15 at 20:32
The approach detailed in this answer may interest you http://stackoverflow.com/a/26251892/525478 — Brad Werth, Feb 25 '15 at 23:02

score 0 · Answer 1 · answered Feb 25 '15 at 22:58

0

comps = Company.where(company_code: ['12B1', '34C8', '87DD', ...]).select(:city_id).distinct(:city_id)
cities = City.where(id: comps.collect { |c| c.city_id })

That said, the rails way is really to use "includes". I'd highly recommend fixing the unicode problem at the core, so you don't end up with a lot of workarounds (like this) permanently scattered throughout your code.

answered Feb 25 '15 at 22:58

Joshua

5,336
1
28
42

yeah, select().distinct() is good, but how do I return comps with cities included ? Like .includes() would do. I have used virtual attribs. Any ideas? – Fernando Fabreti Feb 26 '15 at 10:44
Fixing unicode on sqlserver-adapter is out of question, I can't find any better workaround... maybe dealing with Arel... – Fernando Fabreti Feb 26 '15 at 10:46

Fernando Fabreti · Answer 2 · 2015-02-26T18:32:17.783

Better than having 2 queries (one for Companies, one for Cities), is to have only 1 query using join:

def self.get_companies_by_code(code_array)
  sql_string = Company.where(company_code: code_array).joins(:city).select("*").to_sql #['12B1', '34C8', '87DD', ...]
  sql_string = sql_string.gsub("N'","'")  #remove unicode prefix
  companies = find_by_sql(sql_string)  
end

I get this only query on console:

Company Load (60.6ms)  EXEC sp_executesql N'SELECT * FROM
  [companies] INNER JOIN [cities] ON [cities].[id] = [companies].[city_id] 
  WHERE [companies].[code] IN (''12B1'', ''34C8'', ''87DD'', ''9AA2'')'

Now, the weird part: On the console, it returns a array of Company objects that doesn't show up the joined cities, but they are there! I mean, if I do a result.first.attributes it lists all attributes from the company and city joined

Then, I can access the "invisible" attributes inside the returned array:

> result = Company.get_companies_by_code( ['12B1', '34C8', '87DD'] )
> result.first.city_name
> "Atlanta"

I can only imagine what happens if there are colliding collumn names...

A more "Rails" approach would be to set the association manually:

def self.get_companies_by_code(code_array)
  sql_string = Company.where(company_code: code_array).to_sql 
  sql_string = sql_string.gsub("N'","'")  #remove unicode prefix
  companies = find_by_sql(sql_string)  

  cities_ids_array = companies.map(&:city_id)
  cities_array = City.where(:id => cities_ids_array)
  cities_hash = cities_array.group_by {|c| c.id}

  companies.each {|comp|
       association = comp.association(:city)
       association.target = cities_hash[comp.city_id]
  }
  companies
end

some references:

http://blog.bigbinary.com/2013/07/01/preload-vs-eager-load-vs-joins-vs-includes.html

https://mrbrdo.wordpress.com/2013/09/25/manually-preloading-associations-in-rails-using-custom-scopessql

http://blog.arkency.com/2013/12/rails4-preloading/

activerecord and slow DB connection: (n+1) queries: how to hit DB efficiently?

2 Answers2