Two extreme cases: n + 1 query and Cartesian Product

In the previous article, there were two extremes: frequent queries and huge result sets. Two extremes may occur when hibernate loads data: frequent queries and a large result set of detection. N + 1 queries are a typical case of the previous case, while Cartesian product is a typical case of the latter case. The following briefly summarizes the causes of these two extreme cases and the optimization method.

 

1. n + 1 queries

If a set is lazy loading, Hibernate will generate a SELECT statement to be loaded by this set when the set is accessed for the first time. This is 1 in n + 1. Then, in the process of iteration of this set, if you want to access other associated objects on which the collection element depends, if its associated object is also lazy loading, then hibernate generates a SELECT statement to load the associated object from the database. In this way, N select statements are generated for N cycles, which is N in n + 1.

To solve n + 1 queries, the eager fetch needs to output the dependent objects of the set and set elements when a dynamic crawling policy is formulated. In this way, only one SQL statement is generated. The specific method is to use the fetch keyword in Hol to capture the desired object. In criteria, use the setfetchmode method to set the target object to be captured. For specific examples, see JPwH-13.5.2

 

 

Ii. Cartesian Product

The opposite of N + 1 queries is Cartesian product. Generally, custom dynamic crawling policies do not load data that we don't need. However, even if we load only the required objects, a large amount of data will be selected, this is the Cartesian Product Caused by capturing a "Parallel" set.

For example, a forum has a moderator set. The size is 3 and a thread set. The size is 100. If the moderator set is captured separately when Forum is loaded, the result set is 3 and the thread set is captured separately. The result set is 100, at the same time, the result set for capturing these two sets is the multiplication (that is, the Cartesian Product) of the Two sets: 3*100. if the size of the third set is 50, the result set is 3*100*50. therefore, we can see that capturing a "Parallel" set produces Cartesian product. If there are many sets or there are many elements in the Set, the result set will expand sharply. What is the performance loss caused by a huge result set? Think about the time taken by the database server to process this data, the memory occupied by the server, and the data is transmitted over the network, in this way, the memory occupied by the application server and the time it takes for hibernate to encapsulate the data are divided into two or three SQL statements (the result set is reduced by an order of magnitude) compared with the overhead) it is much faster to obtain data.

Hibernate does not prohibit us from generating cartesian products. That is to say, you can capture multiple parallel sets in a capture plan. But one exception is the bag set. Hibernate does not allow capturing more than two bag collections at the same time. This is because: The resultset of a product can't be converted into bag collections, because hibernate can't know which rows contain duplicates that are valid (bags allow duplicates) and which aren't.

The issue of "Parallel" sets should be addressed flexibly during crawling. If the expected result set is large, do not use join fetch instead of Subselect fetch! Subselect fetch is a recommended optimization solution for parallel sets.

For more information about n + 1 queries and cartesian products, see section jpwh_13.2.5.