Article summary
Rails and Active Record make many things easy: connecting to the database, building complex queries based on your object model, and easily migrating your schema up, down, and sideways. They also let you very easily introduce N+1 queries.
Using N+1 Queries
N+1 queries are common when working with an ORM like Active Record. Active Record lazily loads relationships when querying. The developer has to explicitly ask for things to preload via includes
.
For example, you can trigger one query for the car and N more to get the driver of each car.
cars = Car.where(color: :red)
cars.each do |car|
puts car.driver.first_name
end
Preloading the Relationship
Calling includes
preloads the relationship. By adding the includes
, Rails will either do the needed JOIN
or a separate query/queries (for multiple relationships).
cars = Car.where(color: :red).includes(:driver)
cars.each do |car|
puts car.driver.first_name
end
Finding N+1 Queries
A great way to see where N+1 queries have sneaked into your Rails app is to use the bullet. New Relic, Skylight, and others can help you find problematic queries.
Once I find a problem query, I like to write a preloading rspec to make sure I’ve properly preloaded what I need. I’ll often wrap groups of common includes
into named scopes to keep things a little cleaner. I’ve also added a matcher, not_talk_to_db
, that mocks out the database to make sure no calls are made to load additional data.
it 'preloads drivers' do
cars = Car.with_driver.all
expect(->() {
# ask for all the things that should have
# been preloaded
cars.map{|c|c.driver.first_name}
}).to not_talk_to_db
end
RSpec::Matchers.define :not_talk_to_db do |_expected|
match do |block_to_test|
%w(exec_delete exec_insert exec_query exec_update).each do |meth|
expect(ActiveRecord::Base.connection).not_to receive(meth)
end
block_to_test.call
end
supports_block_expectations
end
N+1 queries are quick to sneak in and can wreak havoc on performance. I hope this approach will help you find them quickly, write the test, and preload correctly. Good luck on your Active Record adventures.
I’d add ‘true’ after block_to_test.call, because when our lambda returns nil tests didn’t pass