ActiveRecord::QueryMethods#eager_load and ActiveRecord::QueryMethods#preload generate different SQL. Either a single but large query with JOINs or several, small and optimized queries.
Both approaches are equally effective whenever the SQL selection part does not refer to a joined table.
But which approach is faster?
Assertion
Parsing and instantiating the preload results should be faster than doing it with eager_load .
At first the test basis:
class Order < ApplicationRecord
end
class User < ApplicationRecord
has_many :orders
end
SQL by eager_load:
SELECT "users"."id" AS t0_r0, "users"."email" AS t0_r1, "users"."created_at" AS t0_r2, "users"."updated_at" AS t0_r3, "orders"."id" AS t1_r0, "orders"."user_id" AS t1_r1, "orders"."product_id" AS t1_r2, "orders"."created_at" AS t1_r3, "orders"."updated_at" AS t1_r4
FROM "users"
LEFT OUTER JOIN "orders"
ON "orders"."user_id" = "users"."id"
WHERE "users"."created_at" = '2017-02-27 22:23:34';
SQL by preload:
SELECT "users".* FROM "users";
SELECT "orders".* FROM "orders" WHERE "orders"."user_id" IN (1, 2, 3);
Benchmark
The benchmark test compares eager_load with preload:
require 'ips_benchmark_helper'
Benchmark.ips do |bm|
bm.report('#eager_load') do
User.eager_load(:orders)
end
bm.report('#preload') do
User.preload(:orders)
end
bm.report('#includes') do
User.includes(:orders)
end
end
In addition, ActiveRecord :QueryMethods#includes has also been included in the benchmark because it is based on preload and eager_load.
Results
Die findings:
Warming up --------------------------------------
#eager_load 6.020k i/100ms
#preload 6.326k i/100ms
#includes 4.961k i/100ms
Calculating -------------------------------------
#eager_load 71.768k (± 2.7%) i/s - 361.200k in 5.036819s
#preload 72.981k (± 2.3%) i/s - 366.908k in 5.030214s
#includes 58.485k (± 2.3%) i/s - 292.699k in 5.007564s
make clear that preload has a better performance compared to eager_load. The difference appears to be marginal. But it is expected, that the gap is greater, the more JOINs have to be made.
It is surprising that the overhead of includes accounts for 20% of the required resources.
Conclusion
If the database request does not require eager_load syntactically, then preload should be preferred.
In addition, from the performance perspective on includes, it should only be used in case of the preload or eager_load approach has to be applied at runtime.