Java Engineering Optimization-ORM framework Query Optimization Principle

Java Engineering Optimization-ORM framework Query Optimization Principle

As we all know, in the current popular enterprise-level architecture, ORM has always been the most basic part. At the bottom of the architecture design, it provides object-oriented Operation Support for the logic layer, the fact is always different from what we expected. When ORM provides a good operation experience, it also loses some flexibility and efficiency of native SQL. Of course, this problem does not affect our use of the orm framework, but hinders the improvement of our website traffic, especially in the enterprise-level multi-link complex query, performance bottleneck is a part that has to be raised!

To solve this problem, most ORM frameworks provide a compromise solution, that is, to construct an object in a query statement, which can be an Entity or Map, this solves the issue of cascade queries to a large extent. Today, let's unveil this layer together to see how optimization is done here!

Symptom:

Let's take a look at the entities used in our experiment.

We can see that the student entity in the middle has too many associations with other entities. If we use a general query statement, it will become very slow. We test to query 10 records, if you do not enable lazy loading, the data cannot be loaded for 10 minutes. Check the log file and run the following SQL statement:

Cause

Query statement:

From Student where isDlete = 0

Log File:

This is only a small part. Our statistics are that we have issued 241 query statements, which the customer cannot tolerate. through research, we found that although directly sending from statements can return objects, but the query efficiency is very slow, it would be much better to add the select statement before the from statement, because after the select statement is added, it will be combined into a join statement, at last, only one day of SQL statement is sent, which significantly improves the efficiency!

Summary:

The solution to a problem is sometimes very simple, and I regret my original design. In fact, this is a process, a fortune, and every problem we encounter, it is all for us to have a better idea in future design. Of course, the more important thing is to use the experience of others as our own experience to improve our ability, this is what companies like to see!