[ORM] Eager Loading and N+1 Query Problem

What is N+1 query problem?

When your code loads the children in a parent-child relationship via the parent, most ORM have lazy-loading enabled by default, so queries are issued for the parent record, and then one query for EACH child record. As you can expect, doing N + 1 queries instead of a single query will floor your database with queries; something to avoid. 

Consider this code:

#Articles model
class Article < ActiveRecord::Base
    belongs_to :author
end 

#Authors model
class Author < ActiveRecord::Base
    has_many :posts
end

If you then ran:

#In our controller
@recent_articles = Article.order(published_at: :desc).limit(5)

#in our view file
@recent_articles.each do |article|
    Title: <%= article.title %>
    Author:<%= article.author.name %> # <-- this will trigger N+1 query
end

You would send 6 (5+1) queries to the database. 1 to fetch 5 recent articles, and then 5 for their corresponding authors.

Remember: it is faster to send 1 query which returns 100 results, than to issue 100 queries which returns 1 result. 

Solution - Eager Loading

In Rails, ActiveRecord has a method called includes, which ensures that all the associated datas specified are loaded with the minimum number of queries.

Eager loading means to fetch all the data ahead of time. Instead of accessing EACH child id and calling a single query, it will fetch the ids all the child and then use a single query to get data of all the child of interest. 

Before Eager Loading:

Article Load (0.9ms) SELECT 'articles'.* FROM 'articles'
Author Load (0.4ms) SELECT 'authors'.* FROM 'authors' WHERE 'authors'.'id' = ? ORDER BY 'authors'.'id' ASC LIMIT 1 [["id", 1]]
Author Load (0.3ms) SELECT 'authors'.* FROM 'authors' WHERE 'authors'.'id' = ? ORDER BY 'authors'.'id' ASC LIMIT 1 [["id", 2]]
Author Load (0.4ms) SELECT 'authors'.* FROM 'authors' WHERE 'authors'.'id' = ? ORDER BY 'authors'.'id' ASC LIMIT 1 [["id", 3]]
Author Load (0.3ms) SELECT 'authors'.* FROM 'authors' WHERE 'authors'.'id' = ? ORDER BY 'authors'.'id' ASC LIMIT 1 [["id", 4]]    
Author Load (0.4ms) SELECT 'authors'.* FROM 'authors' WHERE 'authors'.'id' = ? ORDER BY 'authors'.'id' ASC LIMIT 1 [["id", 5]]

After Eager Loading:

Article Load (0.4ms) SELECT 'articles'.* FROM 'articles'
Author Load (0.4ms) SELECT 'authors'.* FROM 'authors' WHERE 'authors'.'id' IN (1,2,3,4,5)

Remember: less query = better.



Comments

Post a Comment

Popular posts from this blog

[Redis] Redis Cluster vs Redis Sentinel

1) In-Depth Guides to Redis Cluster and Sentinel For more in-depth explanation for Redis Cluster or Redis Sentinel, please read my other posts: Sentinel:  https://notafraidofwong.blogspot.com/2019/05/redis-redis-sentinel.html Cluster:  https://notafraidofwong.blogspot.com/2019/05/redis-redis-cluster.html 2) Deciding between Redis Sentinel and Redis Cluster Similarities : Both solutions provide high availability for your system. 2.1) Redis Cluster 2.1.1) Pros Shards data across multiple nodes Has replication support Has built-in failover of master 2.1.2) Cons Not every library supports it May not be as robust (yet) as Standalone Redis or Sentinel Setup and maintenance is more complicated 2.2) Redis Sentinel 2.2.1) Pros Automatically selects new master in case of failure Easy to setup, (seems) easy to maintain 2.2.2) Cons No data sharding; master might be overutilized. Another distributed system to maintain. 3) So how do I decide? In
Read more

[Unit Testing] Test Doubles (Stubs, Mocks....etc)

Image
In unit testing, isolation is key. The goal of unit testing is to test individual components, and not an entire system . The class/object/function you are testing is System Under Test (SUT) , and the other components of the system are Collaborators or Dependencies. Test Double  is a generic term for any kind of 'pretend' object used in place of a real object for testing purposes.  There are several types of Testing Doubles. We will start with: Fake Stub Mock Command Queries Mocks vs Stubs And then: Dummy Spies Spies vs Mocks I) Basic Testing Doubles a) Fake Fake are objects that have working implementations, but not same as production one. Usually they take some shortcut and have simplified version of production code. Note that Fake is a generic term - that can point to anything; usually mock and stubs. An example of this is an in-memory implementation of a database. This fake implementation will not access the actual database, but will u
Read more

[Node.js] Pending HTTP requests lead to unresponsive nodeJS

This is a recap conclusion to the potential problems of a NodeJS program I worked on previously. The program simulates thousands of clients navigating a website by sending thousands of http requests to a given API . The idea is to load test a particular API to make sure they can handle high traffic. Because we are simulating clients, it's important to keep track of the state of the simulated client as well. The problem with this program is it is buggy and becomes unresponsive from time to time (the only solution is to restart it). The problem was fixed by re-writing the entire program in Java, but it was regretful that I never had enough time to find the exact reason the nodeJS version of it failed. The following are potential causes I suspect: 1. Exceptions The NodeJS program was not written very neatly and stacked callbacks on top of callbacks on top of callbacks. This made it very difficult to keep track of exceptions and unexpected http responses are prone to happen. So
Read more