CODEWITHSUNDEEP
ruby-on-railsrubyjoinactiverecord
Neil
Neil

Reputation: 5178

ActiveRecord use joins for performance but load all associated records into memory like with includes

Unless I am mistaken: joins has better performance than includes because at the database level:

And in general, an inner join is faster than a subquery.

Example:

#app/models/owner.rb
class Owner < ActiveRecord::Base
  has_many :pets
end 

#app/models/pet.rb
class Pet < ActiveRecord::Base
  belongs_to :owner
end

Using rails console:

# showing how 'includes' in rails causes an IN statement which is a subquery 
irb(main):001:0> @owners = Owner.all.includes(:pets)
Owner Load (2.7ms)  SELECT "owners".* FROM "owners"
Pet Load (0.4ms)  SELECT "pets".* FROM "pets" WHERE "pets"."owner_id" IN (1, 2, 3)

And now using joins which causes an inner join:

irb(main):001:0> @owners = Owner.all.joins(:pets)
Owner Load (0.3ms)  SELECT "owners".* FROM "owners" INNER JOIN "pets" ON "pets"."owner_id" = "owners"."id"

So it would seem like it would almost always be better the use joins over includes because:

However, there is one gotcha with using joins. This article does a great job describing it. Basically, includes loads all the associated objects into memory, so that if you query for any of the attributes for those associated objects, it doesn't hit the database. Meanwhile, joins DOES NOT load into memory the associated objects' attributes, so if you query for any of the attributes, it makes additional hits on the database.

So here is my question: Is it possible to do inner joins like with joins for performance but at the same time load all the associated objects into memory like includes does?
Put another way: is it possible to load all the associated objects into memory like includes does, but causes an inner join as opposed to a subquery?

Upvotes: 5

Views: 2157

Answers (1)

spickermann
spickermann

Reputation: 106932

I think your assumption that a JOIN is always faster than two queries is not correct. It highly depends on the size of your database tables.

Imagine you have thousands of owners and pets in your database. Then your database had to join all together first, even if you just want to load 10 records. On the other hand one query loading 10 owners and one query to load all pets for that 10 owners would be faster than that JOIN.

I would argue that both methods exist to solve different problems:

  • joins is used when you need to combine two tables to run a query on the data of both tables.
  • includes is used to avoid N+1 queries.

Btw: The Rails documentation has a note that includes has performance benefits over joins:

This will often result in a performance improvement over a simple join.

Upvotes: 7

Related Questions