Preload, EagerLoad, Includes, and Joins
Optimizing Database Queries in Ruby on Rails: Exploring Preload, EagerLoad, Includes, and Joins.
Ruby on Rails provides several methods — preload, eager_load, includes, and joins — to optimize database queries for associated models. Understanding when to use each one is key to avoiding performance pitfalls like the “N+1 query” problem.
In this post, we’ll explore these querying methods through examples with User, Post, and Comment models. You’ll learn the tradeoffs of each technique and when to apply them to efficiently retrieve related data. By the end, you’ll know how to squeeze the best performance out of Rails database queries by eagerly loading and joining associations.
Eager Loading
Eager loading is a database technique to improve query performance by loading the necessary data and its related entities, including the associated entities, in a single query. It allows the query to load the required resources as soon as the code is executed, reducing the number of queries and improving the overall efficiency of the database.
Eager Loading methods provided by Rails
- Preload
- Eager_load
- Includes
- Joins
Understanding the above methods
Suppose we have a User
model and a Post
model. The User
model will have a one-to-many association with the Post
model, which means that a user can have many posts, but a post can belong to only one user.
The Post and Comment models have the same association as the User and Comment models.
User model
class User < ActiveRecord::Base
devise :database_authenticatable, :registerable,
:recoverable, :rememberable, :validatable
has_many :posts
has_many :comments
end
Post model
class Post < ActiveRecord::Base
has_many :comments, dependent: :destroy
belongs_to :user
validates :title, presence: true
validates :content, presence: true, length: { minimum: 10 }
end
Comment model
class Comment < ActiveRecord::Base
belongs_to :post
belongs_to :user
validates :comment, presence: true, length: { minimum: 1 }
validates :post, presence: true
end
As an illustrative example, let’s consider that we’ve populated our database with 4 users, 9 posts, and 0 comments.
Preload
Pros: Preload loads associated records in a separate query, reducing the number of database queries and improving performance. It is useful when you want to access the associated records but do not need them immediately.
If we want to retrieve all posts with their associated users, we can use the preload method.
Post.preload(:user).limit(5)
Output :
Post Load (0.3ms) SELECT "posts".* FROM "posts" LIMIT 5
User Load (0.3ms) SELECT "users".* FROM "users" WHERE "users"."id" IN (1, 2)
=> #<ActiveRecord::Relation [#<Post id: 1, title: "Post 1", content: "hi everyone", created_at: "2023-04-04 12:50:32", updated_at: "2023-04-04 12:50:32", user_id: 1, email: nil>, #<Post id: 2, title: "Post 2", content: "hello post 2\r\n", created_at: "2023-04-04 12:50:48", updated_at: "2023-04-04 12:50:48", user_id: 1, email: nil>, #<Post id: 3, title: "Post 3", content: "hello Post3 ", created_at: "2023-04-04 12:51:03", updated_at: "2023-04-04 12:51:03", user_id: 1, email: nil>, #<Post id: 4, title: "post 4", content: "hello post 4", created_at: "2023-04-04 12:51:56", updated_at: "2023-04-04 12:51:56", user_id: 2, email: nil>, #<Post id: 5, title: "Post 5", content: "hello Post 5", created_at: "2023-04-04 12:52:10", updated_at: "2023-04-04 12:52:10", user_id: 2, email: nil>]>
We can also access the user related to the post.
Post.preload(:user).limit(5).first.user
Output:
#<User id: 1, email: "rails@test.com", created_at: "2023-04-04 12:49:30", updated_at: "2023-04-04 12:49:30">
Cons: 1) Preload can lead to N+1 query problems, where you end up making one query for the main records and then one query per associated record. This can result in slower performance than expected.
2) While preload can be useful for loading associations in a separate query, one drawback is that it cannot be used with a where clause. This is because preload executes a separate query to retrieve the associated records, which means that any conditions specified in the where clause would not be applied to the associated records.
Post.preload(:user).where(users: {id: 1})
Output:
ActiveRecord::StatementInvalid (SQLite3::SQLException: no such column: users.id: SELECT "posts".* FROM "posts" WHERE "users"."id" = ?)
Eagerload
In Ruby on Rails, eager_load is a method that can be used to eager load associations when retrieving records from the database, but the catch is that it uses LEFT OUTER JOIN for executing its query.
Pros: 1) EagerLoad loads all associated records in a single query, which can significantly improve performance. It is useful when you know you will need the associated records and want to avoid N+1 query problems.
If we want to retrieve all posts with their associated users, we can use the eager_load method.
Post.eager_load(:user).limit(5)
Output:
SELECT "posts"."id" AS t0_r0, "posts"."title" AS t0_r1, "posts"."content" AS t0_r2, "posts"."created_at" AS t0_r3, "posts"."updated_at" AS t0_r4, "posts"."user_id" AS t0_r5, "posts"."email" AS t0_r6, "users"."id" AS t1_r0, "users"."email" AS t1_r1, "users"."encrypted_password" AS t1_r2, "users"."reset_password_token" AS t1_r3, "users"."reset_password_sent_at" AS t1_r4, "users"."remember_created_at" AS t1_r5, "users"."created_at" AS t1_r6, "users"."updated_at" AS t1_r7 FROM "posts" LEFT OUTER JOIN "users" ON "users"."id" = "posts"."user_id" LIMIT 5
=> #<ActiveRecord::Relation [#<Post id: 1, title: "Post 1", content: "hi everyone", created_at: "2023-04-04 12:50:32", updated_at: "2023-04-04 12:50:32", user_id: 1, email: nil>, #<Post id: 2, title: "Post 2", content: "hello post 2\r\n", created_at: "2023-04-04 12:50:48", updated_at: "2023-04-04 12:50:48", user_id: 1, email: nil>, #<Post id: 3, title: "Post 3", content: "hello Post3 ", created_at: "2023-04-04 12:51:03", updated_at: "2023-04-04 12:51:03", user_id: 1, email: nil>, #<Post id: 4, title: "post 4", content: "hello post 4", created_at: "2023-04-04 12:51:56", updated_at: "2023-04-04 12:51:56", user_id: 2, email: nil>, #<Post id: 5, title: "Post 5", content: "hello Post 5", created_at: "2023-04-04 12:52:10", updated_at: "2023-04-04 12:52:10", user_id: 2, email: nil>]>
The LEFT OUTER JOIN used by eager_load can cause duplicate data to be returned, as well as slow down performance when working with large datasets. This is because the LEFT OUTER JOIN joins all records from both tables, regardless of whether they have an association or not.
2) It can be used with a where clause.
Post.eager_load(:user).where(users: {id: 1} ).count
Output:
SELECT COUNT(DISTINCT "posts"."id") FROM "posts" LEFT OUTER JOIN "users" ON "users"."id" = "posts"."user_id" WHERE "users"."id" = ? [["id", 1]]
=> 3
Cons: 1) EagerLoad can be memory-intensive, especially if there are many associated records. It can also lead to more complex queries and potentially slower performance if not used correctly.
2) The LEFT OUTER JOIN used by eager_load can cause duplicate data to be returned, as well as slow down performance when working with large datasets. This is because the LEFT OUTER JOIN joins all records from both tables, regardless of whether they have an association or not.
Includes
Pros: 1) Includes loads associated records in a separate query, like Preload, but also includes the main records in the same query. This can improve performance and reduce the risk of N+1 query problems.
If we want to retrieve all posts with their associated users, we can use the Includes method to eager load the associated users.
Post.includes(:user)
Output:
Post Load (0.7ms) SELECT "posts".* FROM "posts"
User Load (0.1ms) SELECT "users".* FROM "users" WHERE "users"."id" IN (1, 2, 3)
=> #<ActiveRecord::Relation [#<Post id: 1, title: "Post 1", content: "hi everyone", created_at: "2023-04-04 12:50:32", updated_at: "2023-04-04 12:50:32", user_id: 1, email: nil>, #<Post id: 2, title: "Post 2", content: "hello post 2\r\n", created_at: "2023-04-04 12:50:48", updated_at: "2023-04-04 12:50:48", user_id: 1, email: nil>, #<Post id: 3, title: "Post 3", content: "hello Post3 ", created_at: "2023-04-04 12:51:03", updated_at: "2023-04-04 12:51:03", user_id: 1, email: nil>, #<Post id: 4, title: "post 4", content: "hello post 4", created_at: "2023-04-04 12:51:56", updated_at: "2023-04-04 12:51:56", user_id: 2, email: nil>, #<Post id: 5, title: "Post 5", content: "hello Post 5", created_at: "2023-04-04 12:52:10", updated_at: "2023-04-04 12:52:10", user_id: 2, email: nil>, #<Post id: 6, title: "Post 6", content: "hello post 6", created_at: "2023-04-04 12:52:41", updated_at: "2023-04-04 12:52:41", user_id: 2, email: nil>, #<Post id: 7, title: "post 7", content: "hello post 7", created_at: "2023-04-04 12:53:51", updated_at: "2023-04-04 12:53:51", user_id: 3, email: nil>, #<Post id: 8, title: "post 8", content: "hello post 8", created_at: "2023-04-04 12:54:04", updated_at: "2023-04-04 12:54:04", user_id: 3, email: nil>, #<Post id: 9, title: "post 9", content: "hello post 9", created_at: "2023-04-04 12:54:19", updated_at: "2023-04-04 12:54:19", user_id: 3, email: nil>]>
2) It can be used with a where clause.
Post.includes(:user).where(users: {id: 1}).count
Output:
SELECT COUNT(DISTINCT "posts"."id") FROM "posts" LEFT OUTER JOIN "users" ON "users"."id" = "posts"."user_id" WHERE "users"."id" = ? [["id", 1]]
=> 3
This method in Rails is actually a combination of eager loading and preloading.
As we use the WHERE clause, it uses LEFT OUTER JOIN of eager loading. When it comes to normal fetching of associated data, it uses preload.
Joins
Pros: 1) Joins combine tables from multiple models into a single query, which can be more efficient than making multiple queries. It is useful when you need to filter or order based on associated records.
It uses inner join for the above feature.
If we want to retrieve all posts with their associated users, we can use the joins method to eager load the associated users:
Post.joins(:user).limit(5)
Output:
SELECT "posts".* FROM "posts" INNER JOIN "users" ON "users"."id" = "posts"."user_id" LIMIT 5
=> #<ActiveRecord::Relation [#<Post id: 1, title: "Post 1", content: "hi everyone", created_at: "2023-04-04 12:50:32", updated_at: "2023-04-04 12:50:32", user_id: 1, email: nil>, #<Post id: 2, title: "Post 2", content: "hello post 2\r\n", created_at: "2023-04-04 12:50:48", updated_at: "2023-04-04 12:50:48", user_id: 1, email: nil>, #<Post id: 3, title: "Post 3", content: "hello Post3 ", created_at: "2023-04-04 12:51:03", updated_at: "2023-04-04 12:51:03", user_id: 1, email: nil>, #<Post id: 4, title: "post 4", content: "hello post 4", created_at: "2023-04-04 12:51:56", updated_at: "2023-04-04 12:51:56", user_id: 2, email: nil>, #<Post id: 5, title: "Post 5", content: "hello Post 5", created_at: "2023-04-04 12:52:10", updated_at: "2023-04-04 12:52:10", user_id: 2, email: nil>]>
2) It can also be used with where clause.
Post.joins(:user).where(users: {id: 1} ).count
Output:
SELECT COUNT(*) FROM "posts" INNER JOIN "users" ON "users"."id" = "posts"."user_id" WHERE "users"."id" = ? [["id", 1]]
=> 3
Cons) There are some drawbacks of using the Joins model, as it uses INNER JOIN:
- It may give a duplicate record in a few cases.
- It may result in empty records if an associated record does not have data, which can be represented as a null or empty value.
We will take the example of post and comment as it has 0 comments.
Post.joins(:comments)
Output:
#<ActiveRecord::Relation []>
And if we use any of the above methods (Preload, eager_load, Includes), it will show the post as follows:
Post.includes(:comments)
Output:
SELECT "posts".* FROM "posts"
Comment Load (0.3ms) SELECT "comments".* FROM "comments" WHERE "comments"."post_id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9)
=> #<ActiveRecord::Relation [#<Post id: 1, title: "Post 1", content: "hi everyone", created_at: "2023-04-04 12:50:32", updated_at: "2023-04-04 12:50:32", user_id: 1, email: nil>, #<Post id: 2, title: "Post 2", content: "hello post 2\r\n", created_at: "2023-04-04 12:50:48", updated_at: "2023-04-04 12:50:48", user_id: 1, email: nil>, #<Post id: 3, title: "Post 3", content: "hello Post3 ", created_at: "2023-04-04 12:51:03", updated_at: "2023-04-04 12:51:03", user_id: 1, email: nil>, #<Post id: 4, title: "post 4", content: "hello post 4", created_at: "2023-04-04 12:51:56", updated_at: "2023-04-04 12:51:56", user_id: 2, email: nil>, #<Post id: 5, title: "Post 5", content: "hello Post 5", created_at: "2023-04-04 12:52:10", updated_at: "2023-04-04 12:52:10", user_id: 2, email: nil>, #<Post id: 6, title: "Post 6", content: "hello post 6", created_at: "2023-04-04 12:52:41", updated_at: "2023-04-04 12:52:41", user_id: 2, email: nil>, #<Post id: 7, title: "post 7", content: "hello post 7", created_at: "2023-04-04 12:53:51", updated_at: "2023-04-04 12:53:51", user_id: 3, email: nil>, #<Post id: 8, title: "post 8", content: "hello post 8", created_at: "2023-04-04 12:54:04", updated_at: "2023-04-04 12:54:04", user_id: 3, email: nil>, #<Post id: 9, title: "post 9", content: "hello post 9", created_at: "2023-04-04 12:54:19", updated_at: "2023-04-04 12:54:19", user_id: 3, email: nil>]>
irb(main):021:0>
3. There is also a drawback which is related to the (n + 1) issue that it does not solve because it does not store associated data for future use.
For example,
post = Post.joins(:user).limit(5)
# when we want to find the first post user
post.first.user
Output:
# output of post = Post.joins(:user).limit(5)
Post Load (0.1ms) SELECT "posts".* FROM "posts" INNER JOIN "users" ON "users"."id" = "posts"."user_id" LIMIT 5
=> #<ActiveRecord::Relation [#<Post id: 1, title: "Post 1", content: "hi everyone", created_at: "2023-04-04 12:50:32", updated_at: "2023-04-04 12:50:32", user_id: 1, email: nil>, #<Post id: 2, title: "Post 2", content: "hello post 2\r\n", created_at: "2023-04-04 12:50:48", updated_at: "2023-04-04 12:50:48", user_id: 1, email: nil>, #<Post id: 3, title: "Post 3", content: "hello Post3 ", created_at: "2023-04-04 12:51:03", updated_at: "2023-04-04 12:51:03", user_id: 1, email: nil>, #<Post id: 4, title: "post 4", content: "hello post 4", created_at: "2023-04-04 12:51:56", updated_at: "2023-04-04 12:51:56", user_id: 2, email: nil>, #<Post id: 5, title: "Post 5", content: "hello Post 5", created_at: "2023-04-04 12:52:10", updated_at: "2023-04-04 12:52:10", user_id: 2, email: nil>]>
# output of post.first.user
SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT 1 [["id", 1]]
=> #<User id: 1, email: "rails@test.com", created_at: "2023-04-04 12:49:30", updated_at: "2023-04-04 12:49:30">
As shown in the output, using joins
results in an additional query to retrieve the user associated with the first post. This is because joins
does not store the preloaded associated data for future use.
For example,
post = Post.includes(:user).limit(5)
# when we want to find the first post user
post.first.user
Output:
# output of post = Post.includes(:user).limit(5)
SELECT "posts".* FROM "posts" LIMIT 5
User Load (0.1ms) SELECT "users".* FROM "users" WHERE "users"."id" IN (1, 2)
=> #<ActiveRecord::Relation [#<Post id: 1, title: "Post 1", content: "hi everyone", created_at: "2023-04-04 12:50:32", updated_at: "2023-04-04 12:50:32", user_id: 1, email: nil>, #<Post id: 2, title: "Post 2", content: "hello post 2\r\n", created_at: "2023-04-04 12:50:48", updated_at: "2023-04-04 12:50:48", user_id: 1, email: nil>, #<Post id: 3, title: "Post 3", content: "hello Post3 ", created_at: "2023-04-04 12:51:03", updated_at: "2023-04-04 12:51:03", user_id: 1, email: nil>, #<Post id: 4, title: "post 4", content: "hello post 4", created_at: "2023-04-04 12:51:56", updated_at: "2023-04-04 12:51:56", user_id: 2, email: nil>, #<Post id: 5, title: "Post 5", content: "hello Post 5", created_at: "2023-04-04 12:52:10", updated_at: "2023-04-04 12:52:10", user_id: 2, email: nil>]>
# output of post.first.user
#<User id: 1, email: "rails@test.com", created_at: "2023-04-04 12:49:30", updated_at: "2023-04-04 12:49:30">
Conclusion
In general, the fastest method for retrieving associated records in Ruby on Rails is Joins, followed by eager_load, Preload, and finally, Includes. However, the choice of method depends on the specific requirements of your application and the relationships between your models.
For more updates on the latest development trends, follow the Simform Engineering blog.