Case analysis and optimization of NHibernate n+1 problem

1. The origin of the problem

Examine the following class structure definitions

public class Category    {        string _id;        Category _parent;        ilist<category> _children = new list<category> ();        Public virtual string Id        {            get            {                return _id;            }        }        Public virtual Category Parent        {            get            {                return _parent;            }        }        Public virtual ilist<category> Children        {            get            {                return _children;            }        }        Public virtual string Title        {            get;            Set;        }        Public virtual string ImageUrl        {            get;            Set;        }        public virtual int Displayorder        {            get;            Set;        }    }

　　

The contents of its NHibernate mapping file are:

<class name= "Category" >
<id name= "id" access= "nosetter.camelcase-underscore" length= ">"
<generator class= "uuid.string"/>
</id>
<property name= "Title" not-null= "true" length= "/>"
<property name= "ImageUrl" length= "/>"
<property name= "Displayorder" not-null= "true"/>
<many-to-one name= "Parent" class= "Category" column= "ParentID" access= "Nosetter.camelcase-underscore"/>
<bag name= "Children" access= "nosetter.camelcase-underscore" cascade = "All-delete-orphan" inverse= "true" order-by= "Displayorder ASC" >
<key column= "ParentID"/>
<one-to-many class= "Category"/>
</bag>
</class>

When the program asks "To filter out all category and then iterate through the sub-objects in the children below", we usually write the following code that meets the requirements :

var query = from O in currentsession.queryover<category> () select O;
ilist<category> list = query. List (); First level query
foreach (Category item in list)
{
foreach (Category child in item. Children)//second level query
{
//...
}
This code runs normally, and the output SQL statement is similar to the following:--First level query
Select * FROM [Category]
--Second level query (each parameter is different)
Select * FROM [Category] Where [parentid][email protected]
Select * FROM [Category] Where [parentid][email protected]
.......
Select * FROM [Category] Where [parentid][email protected]

As you can see from the output SQL, the above code hides a serious performance problem. Assuming that the first-level query returns 20 category objects saved to the list, and each category contains 10 sub-objects, then two foreach loops are executed, and a total of 20*10=200 Select query statements are sent to the database. For each category in the list, it takes itself to execute a SELECT statement (that is, a first-level query) from the database, and the child elements under it need to execute 10 SELECT statements, that is, from removing the category to traversing all its child objects. Need to execute the N+1 SELECT statement, N is the number of sub-objects, which is called the "n+1" problem, its biggest drawback is obvious, is to send too many query statements to the database, resulting in unnecessary overhead, and usually this can be optimized.

2. Solution

2.1 Bulk Load

Since the "n+1" problem is sending too many SELECT statements, it is first thought that the statements can be combined in a database query, in order to solve this problem, nhibernate in the collection map, provides a "bulk load" strategy, namely: Batch-size, The modified bag map is as follows:

<bag name= "Children" access= "nosetter.camelcase-underscore" cascade = "All-delete-orphan" inverse= "true" order-by= "Displayorder ASC" batch-size= ">"
<key column= "ParentID"/>
<one-to-many class= "Category"/>
</bag>

Batch-size indicates that when the Category.children list is loaded, 20 sub-objects are read each time, instead of one load. As a result, n+1 evolved into n/20+1. For the two foreach procedures above, the output SQL statement is similar to the following:

--First level query
Select * FROM [Category]
--Second level query
Select * FROM [Category] Where [ParentID] In (@p0, @[email protected])

Compared to SQL output without a "bulk load" policy, it is clear that the new solution can greatly reduce the sending of query statements to the database.

If you need to set the multi-object loading process to batch, you can add the Default_batch_fetch_size property to the NHibernate configuration file without modifying the mapping file for each class.

2.2 Pre-load

Another way to solve the "n+1" problem is to use preload (Eager fetching), and NHibernate also provides support for it in the collection map, namely, outer-join or Fetch. The modified bag map is configured as follows:

<bag name= "Children" access= "nosetter.camelcase-underscore" cascade = "All-delete-orphan" inverse= "true" order-by= "Displayorder ASC" outer-join= "true" >
<key column= "ParentID"/>
<one-to-many class= "Category"/>
</bag>

Outer-join= "true" is equivalent to fetch= "join", while Fetch has "select" and "Subselect" two options (default = "select" option), they refer to what SQL statement is used to load the child collection. When outer-join= "true" or fetch= "join", the output SQL statement is similar to the following:

--First level query
Select t0. Id,t0. Parentid,t0. Title...t1. Id,t1. Parentid,t1. Title ... From [Category] T0 left joins [category] T1 on T1. Parentid=t0. Id

Preload in the first level of the query, through the join one-time fetching the object itself and its child objects, than using bulk load generated statements less, the first load efficiency.

Although it is easy to enable preload settings in a mapping file, it is very simple to consider other ways (such as get or load) to automatically mount child objects when the object is fetched, causing unnecessary performance damage, and, in addition, setting preload in the mapping file whose scope is applicable only to: Get an object by GET or load , lazy-loading (implicit) association objects, criteria queries, and HQL statements with a LEFT join fetch, it is often required to avoid writing the configuration that enables preloading to the mapping file (NHibernate is not recommended in the mapping file), Instead, it is written in code that needs to be preloaded, and other places maintain the original logic so that there is no adverse effect, and there are three types of preload in the code:

Icriteria.setfetchmode (string associationpath, Fetchmode mode);

or used in 3.0.

Iqueryover<troot, Tsubtype> Fetch (Expression<func<troot, object>> path);

Or use the LEFT join fetch in HQL

From category A LEFT join fetch Category b

Here is a curious case, Fetchmode enumeration contains the eager and join two options, but the actual use of the effect is the same, is the output join statement, no difference, nhibernate so set, I guess the possible reason is to start with only join an option, And then feel inadequate, thus adding a eager, but considering the old version compatibility, did not delete the join, so it is now this way. The following code illustrates how preloading is used in Iqueryover

Q = q.fetch (o = o.children). Eager;

So far, everything seems perfect, but it's not over yet, preloading because its generated SQL statements include join or subquery statements, it does not guarantee the uniqueness of the elements in the collection, such as: A contains two child elements B and C, after preloading, The first-level query takes out a list of two a objects, rather than one we usually imagine. Therefore, to enable the pre-load to get to the list, you need to solve the problem of uniqueness, the simplest is to put the list into the Iset "filter" once.

Protected ilist<t> touniquelist (ienumerable<t> collection)
{
iset<t> set = new Hashset<t> (collection);
return set. ToList ();
}

2.3 Mixed load

Above, we only assume that the category contains sub-objects only a single layer of nesting, however, if the child objects have child objects, when the infinite layer nesting, bulk loading and preloading what happens, first, only in bulk loading, in addition to the first layer, the following each layer nesting will be in bulk load way, The first layer of loading efficiency is relatively low, and secondly, only in the case of pre-loading, the first use of join loading, get to the first and second layers of objects, and the second layer down, the loading process of each layer of object is restored to a simple select, and the case at the beginning of this article is a touch, therefore, Multi-level loading efficiency is low. Then combine them, both in the mapping file set Batch-size, and in the code to open Fetchmode.eager, will not combine the advantages of two to overcome the shortcomings? After practice, the answer is yes. At the same time using bulk loading and preloading, the first query, SQL appeared in the join statement, that is, the pre-loading function, get to the first and second layers of objects, and then each layer of the query, SQL appeared in the statement, that is, bulk loading and play a role, I put this combination of two loading methods, The new way of combining each of these advantages is called "hybrid loading", which is not in the official NHibernate documentation.

3. Crawl Strategy

What we talked about above, collectively referred to as the crawl strategy (fetching strategy). In NHibernate, several crawl strategies are defined:

• Join fetching: Obtains an associated instance or associated collection of an object by using a OUTER join (outer join) in the SELECT statement.
• Query fetch (SELECT fetching): Also sends a SELECT statement to fetch the associated entity or collection of the current object (lazy= "true", which is the default option).
• subquery fetch (Subselect fetching): Also sends a SELECT statement to fetch the associated collection of all entity objects that were previously queried (or crawled to). (lazy= "true")
• Batch crawl (batch fetching): An optimization scheme for query fetching, by specifying a primary key or foreign key list, and using a single SELECT statement to get a batch of object instances or collections.

In addition, the NHibernate crawl strategy distinguishes between the following scenarios:

• Immediate fetching, crawl now: When the host is loaded, the association, collection, or property is immediately fetched.
• Lazy collection fetching, deferred collection crawl: The collection is not crawled until the application has performed a single operation on the collection. (This is the default behavior for collections.) ）
• "Extra-lazy" collection fetching, "Extra-lazy" collection fetch: For each element in the collection class, the database is not accessed until it is needed. Unless absolutely necessary, Hibernate does not attempt to crawl the entire collection into memory (for very large collections).
• Proxy fetching, agent fetch: For an association that returns a single value, it is crawled when a method is called rather than a get operation on its keyword.
• "No-proxy" fetching, non-agent fetch: When an instance variable is accessed, the connection to the return single value is fetched.
• Lazy attribute fetching, property lazy loading: For a property or an association that returns a single value, is crawled when its instance variable is accessed. Compile-time bytecode hardening is required, so this approach is rarely necessary.

By default, NHibernate uses a deferred select fetch for the collection, which is valid for most applications, and if you need to optimize this default policy, you need to select the appropriate crawl strategy, the specific available solution listed in chapter II of this article.

4. General principles

As described above, NHibernate's crawl strategy and specific solutions, when using a crawl strategy to improve performance, the general principle is: As far as possible in the first query or each query to load the associated collection objects, in the right place to use the crawl strategy, both improve performance, but also affect other scenarios as well.

Thanks for watching!

Reference documents:

1. "NHibernate Reference Documentation 3.0":http://nhforge.org/doc/nh/en/index.html

2.Pierre Henri Kuaté, Tobin Harris, Christian Bauer, and Gavin king.nhibernate in Action February, 2009

Case analysis and optimization of NHibernate n+1 problem