Forming of SQL query in Rails Application

Question

I am new to Ruby on Rails. Now I am working on performance issues of a Rails application. I am using New Relic rpm to find out the bottlenecks of the code. While doing this I find something that I cannot figure out. The problem is that here in my Rails application I have used two models A, B and C where model B has two properties: primary key of A and primary key of C like following:

class B
  include DataMapper::Resource
  belongs_to :A, :key=>true
  belongs_to :C, :key=>true
end

Model of A is as follows:

 class A
   include DataMapper::Resource
   property :prop1
   ...
   has n, :bs
   has n, :cs, :through => :bs
 end

While issuing the following statement a.find(:c.id=>10) then internally it is executing the following SQL query:

 select a.prop1, a.prop2,... from a INNER JOIN b on a.id = b.a_id INNER JOIN c on b.c_id = c.id where (c.id=10) GROUP BY a.prop1, a.prop2,....[here in group by all the properties that has been mentioned in select appears, I don't know why]

And this statement is taking too much time during web transaction. Interesting thing is that, when I am executing the same auto generated query in mysql prompt of my terminal it's taking very less amount of time. I think it's because of mentioning so many fields in group by clause. I cannot understand how the query is being formed. If anyone kindly help me to figure this out and optimize this, I will be really grateful. Thank you.

Answer 1

I assume you have you model associations properly configured, something like this:

class A < ActiveRecord
  has_many :B
  has_many :C, through: :B
end

class B < ActiveRecord
  belongs_to :A
  belongs_to :C
end

class C < ActiveRecord
  has_many :B
  has_many :A, through: :B
end

then you could simply call:

a.c.find(10) #mind the plural forms though

You will get better performance this way.