How to avoid n+1 problems in Ruby on Rails _ruby topics

N+1 problem

The n+1 problem is one of the most common performance issues in database access, and first describes what a n+1 problem is:

For example, we have two tables in our database, one is customers, and the other is orders. The orders contains a foreign key customer_id that points to the customers primary key ID.

To get all the customer and their respective order, one way to do this is

SELECT * from Customers;

For each customer;

SELECT * from Orders WHERE orders.customer_id = #{customer.id}

So we actually made a n+1 query to the database: Select all the customer at one time to get n customer, for n customer select their corresponding order altogether n times. So, a total of n+1 to execute the query, this is the n+1 problem

General solution to the problem of n+1

Use the LEFT JOIN to remove all data at once:

SELECT * from Customers left JOIN Orders on customers.id = orders.customer_id

This way, although there are more data to be removed, only one execution

The n+1 problem in Rails

Because Rails uses ActiveRecord to access the database. Therefore, its n+1 problems are not exposed so clearly.

Suppose we have two activerecord:customer, order.

Customer has_many:orders Order

Belong_to:customer

The general way we get all the customer is:

Customers = Customer.all

If we follow closely behind

Customers.each {|customer| puts Customer.orders.amount}

This creates a n+1 problem because the orders are not taken when the customer is captured. Then each of the following traversal will take orders, which constitutes the n+1 problem in rails.

N+1 problem Solving method in Rails

Customers = Customers.includes (: Orders)

This is the time to read the customers also one-time take orders are taken out.

More

Not all related to foreign Key association, a one-to-many problem will create a n+1 problem, it depends on your business. For example, when your method is executed, only very few of them will be able to get the customer's corresponding orders, so keep the default lazy way. Forcing eager to fetch is not worth the candle.