3

I want to do a query, but query speed is slow,I have five models like this:

class Mya < ActiveRecord::Base
    has_many :mybs
end

class Myd < ActiveRecord::Base
    belongs_to :myc
end

class Myc < ActiveRecord::Base
    belongs_to :myb
    has_many :myds
    has_many :myes
end

class Myd < ActiveRecord::Base
    belongs_to :myc
end

class Mye < ActiveRecord::Base
    belongs_to :myc
end

than, I insert some test data to mysql:(seed)

mya=Mya.create!(title: 'first test')
i=0
10.times{
    i=i+1
    myb=Myb.create!(title: "my_#{i}")
    5000.times{
        myc=Myc.create!(mya_id: mya.id, myb_id: myb.id)
        4.times {
            myd=Myd.create!(mya_id: mya.id, myb_id: myb.id, myc_id: myc.id)
            mye=Mye.create!(mya_id: mya.id, myb_id: myb.id, myc_id: myc.id)
        }
    }
}

in my controller, I do like this:

 def index
    @ms = Mya.first.to_json(:include => [{
                                              mybs: {
                                                  :include => {
                                                        :mycs => {
                                                            :include => [:myds, :myes]
                                                        }
                                                  }
                                              }
                                          }
                             ])
    render json:  @ms
  end

enter image description here

It is very very slow, help me, thanks. sorry about my english.

github: https://github.com/scottxu/mytest

scottxu
  • 913
  • 1
  • 7
  • 20

1 Answers1

5

You are running a double-nested n+1-query. That means you

  • Query 1 Mya (1 query)
    • Query 10 Mybs for the Mya (1 query)
      • Query 5000 Mycs for every Myb (10 queries)
        • Query all (4) Mycs for every Myc (10 * 5000 queries)
        • Query all (4) Myds for every Myc (10 * 5000 queries)

That means you are running 1 + 1 + 10 + (10 * 5,000) + (10 * 5,000) = 100,012 queries. Since every query has some overhead because it needs to send and receive data to and from the database, your controller action becomes very slow.

You can prevent this by telling ActiveRecord to include the nested Mybs Mycs and Myds in the first query with the includes method. This way, you will only execute one large query, and ActiveRecord will only communicate with the database one single time.

def index
  @ms = Mya.includes(mybs: {mycs: [:myds, :myes]})
           .first
           .to_json(:include => [
             # ...
           ])
  render json: @ms
end
Patrick Oscity
  • 53,604
  • 17
  • 144
  • 168
  • thanks for that, but it also very slow, "Completed 200 OK in 19236ms (Views: 0.1ms | ActiveRecord: 198.2ms)" – scottxu Jan 18 '15 at 10:17
  • 2
    Instantiating 200000 ActiveRecord objects will never be lightening quick. – Frederick Cheung Jan 18 '15 at 11:14
  • 2
    It is still magnitudes faster. Converting a n+1 queries to a single one is low hanging fruit, but speeding up json rendering is not. If the page is requested very often or needs badass performance, you should rather think about caching than how to shave off more ms. – Patrick Oscity Jan 18 '15 at 13:13
  • 1
    If you know the values you need, try bypassing instantiation. Pluck and format exactly the values you need. – Justin M Jan 18 '15 at 18:43