[NHibernate] N+1 Select query problem analysis

Directory

Write in front

Documentation and series articles

N+1 Select query problem analysis

Summarize

Write in front

In the previous article (lazy loading, immediate loading) are mentioned in the n+1 select problem, always feel that understanding is not in place, also please forgive, this is why the problem is taken out alone for analysis of the reasons. The default lazy load method for NHibernate is a solution to the n+1 select problem, and my own understanding is that immediate loading can be solved completely in the opposite direction. After writing that article, to this question, has always been thinking about, always feel which place is wrong. I am really sorry for the fact that I have a very bad understanding of the problem and also caused your misunderstanding.

Documentation and series articles

[Nhibernate] Architecture

[NHibernate] Isessionfactory Configuration

[NHibernate] Persistence class (persistent Classes)

[NHibernate] O/R Mapping Basics

[NHibernate] Collection Class (collections) mappings

[NHibernate] Association mappings

[NHibernate] Parent/child

[NHibernate] Cache (nhibernate.caches)

[NHibernate] NHibernate.Tool.hbm2net

[NHibernate] Nullables

[NHibernate] NHibernate How to map image fields in SQL Server

[NHibernate] Basic configuration and testing

[NHibernate] HQL Query

[NHibernate] Conditional query criteria

[NHibernate] additions and deletions to change the operation

[NHibernate] Transactions

[NHibernate] concurrency control

dependent objects of [NHibernate] components

[NHibernate] One-to-many relationship (cascade Delete, cascade Add)

[NHibernate] One-to-many relationship (associative query)

[NHibernate] Many-to-many relationships (associative queries)

[NHibernate] Lazy loading

[NHibernate] Load now

[NHibernate] View processing

N+1 Select query problem analysis

When querying data using SQL, if the select is too many, it will also cause pressure on the database, affecting performance. For example, if you query n customer objects, the query obtains all the customer objects at once, and then queries the associated order table based on the CustomerID. ( in case of immediate loading )

An example

1         /// <summary>2         ///Nhibernateutil, instantly load customer information and associated data3         /// </summary>4         /// <param name= "CustomerID" ></param>5         /// <returns></returns>6          PublicCustomer getcustomerbyimmediatelyloadnhibernateutil (Guid CustomerID)7         {8             //Get ISession Instances9             //The session is freed by using the method, in order to ensure that the data is loaded immediately, rather than deferred loadingTen             //remember the last article mentioned in the delay load, in the case of closing the session, then read the data when there will be an exception message One             //forget to go back to the previous article to see A             using(ISession session =nhibernatehelper.getsession ()) -             { -Customer customer = session. Get<customer>(CustomerID); the                 // -                 //Summary: -                 //Force initialization of a proxy or persistent collection. -                 // + NHibernate.NHibernateUtil.Initialize (Customer. Orders); -                 returncustomer; +             } A}

Here you use the Nhinernateutil utility class to force an immediate load, test the code

1         protected voidBtnimmediately_click (Objectsender, EventArgs e)2         {3Business.customerbusiness customerbusiness =Newbusiness.customerbusiness ();4Customer customer = Customerbusiness.getcustomerbyimmediatelyloadnhibernateutil (NewGuid ("B0720295-9541-40B3-9994-610066224DB8"));5             BOOLIsinitorder =NHibernate.NHibernateUtil.IsInitialized (Customer. Orders);6}

To view the generated SQL statement

Originally we want to query customer, one query is enough, but through the monitoring of SQL, found a more SQL query, that is, query the customer's associated data table data.

In the case of delayed loading, it will be queried when needed, performance is better. (For deferred loading, look in a series of articles, which are not included here)

If deferred loading is used, the resulting SQL is as follows (the illustration in the borrowed lazy load article)

One-to-many relationship lazy loading

Many-to-many relationship lazy loading

Order and product Many-to-many relationships, at which SQL statements are generated

You can see that the SQL statement generated at this point is associated with a left outer join (the outer join), and only one SQL statement is generated, significantly reducing the number of times the data is queried.

Summarize

(1) The number of SELECT statements is too large and requires frequent access to the database, which can affect retrieval performance. You can use the left OUTER join query in SQL to query all the data in an SQL statement and reduce the number of select times.
(2) In the case where the application logic only needs to access the customer object and does not need to access the order object, loading the order object is a completely redundant operation, and these extra order objects waste a lot of memory space.
In order to solve the above problems, Hibernate provides two other retrieval strategies: Deferred retrieval strategy and urgent left outer connection retrieval strategy. The deferred retrieval strategy avoids the unnecessary loading of the associated objects that the application does not need to access, and the urgent left outer connection retrieval strategy takes full advantage of the SQL outer join query function, and can reduce the number of SELECT statements

Here on the n+1 select problem to do a special note, there is a problem, if not a way to solve, always feel that there is a lump, early resolution, early good mood. Through the analysis of this article, you are not also to the n+1 problem enlightened? It was my fault that I was really wrong about that piece and I was sorry again.

[NHibernate] N+1 Select query problem analysis