What is n + 1 SELECT query?

The session cache stores the correlated object graphs. By default, when hibernate loads the customer object from the database, it loads all associated order objects at the same time. Take the customer and order classes as an example. Assuming that the customer_id foreign key of the orders table can be null, figure 1 lists the records in the customers table and the orders table.

The find () method of the following session is used to retrieve all customer objects in the database:

List customerlists = session. Find ("from customer as C ");

When running the find () method, Hibernate first queries all records in the MERs table, and then queries records with reference relationships in the orders table based on the ID of each record, hibernate runs the following SELECT statement in sequence:

Select * from MERs MERS;
Select * from orders where customer_id = 1;
Select * from orders where customer_id = 2;
Select * from orders where customer_id = 3;
Select * from orders where customer_id = 4;

Using the preceding five select statements, Hibernate finally loads four Customer objects and five order objects to form an associated object graph in the memory. See figure 2.

Hibernate uses the default immediate search policy when retrieving the order object associated with the customer. There are two shortcomings in this search policy:

(1) The number of select statements is too large. Frequent database access is required, which affects the retrieval performance. To query n customer objects, you must execute n + 1 SELECT query statement. This is the classic n + 1 SELECT query problem. This search policy does not use the SQL connection query function. For example, the preceding five select statements can be completed using one of the following select statements:

Select * from MERs left Outer Join orders
On customers. ID = orders. customer_id

The preceding SELECT statement uses the SQL left outer join query function to query all records of the MERs table and matching records of the orders table in a select statement.

(2) When the application logic only needs to access the customer object instead of the Order object, loading the order object is totally redundant, these redundant order objects waste a lot of memory space.
To solve the above problems, Hibernate provides two other Retrieval Strategies: The latency retrieval policy and the urgent left Outer Join retrieval policy. Delayed retrieval policies can avoid unnecessary application loading.ProgramJoin objects that do not need to be accessed. The query policy of the left Outer Join takes full advantage of the SQL outer join query function, which can reduce the number of select statements.