Have a good laugh

Tell my mother that these days are very depressed and in a bad mood.
My mother said, "If I don't give you the money, you'll go out for a few days!"
I immediately laughed and said, "OK, OK!"
Mom: "Happy!"
Me: "Well, Mom, get the money quickly!"
Mom: You're all happy. What else do I take money for?
I... ]

Ask questions

In real projects, batch insertion data performance optimization???

Solve the problem

1. Business scenarios

1.1 Business Description

The database has a table (pm_test case) and the customer has an excel data, which contains about 3 + 10,000 cases. Now we need to parse the contents of Excel and verify each data. For example: name length check, number check, user account existence check and so on. Then it is encapsulated into a List collection and stored in batches.

CREATE TABLE "public"."pm_testcase" (
"id" varchar(32) COLLATE "default" NOT NULL,
"code" varchar(50) COLLATE "default",
"name" varchar(200) COLLATE "default",
//Omit several fields
CONSTRAINT "pk_pm_testcase" PRIMARY KEY ("id")
)
WITH (OIDS=FALSE)
;

1.2 Business Flow Chart

2. Project status

2.1 Question 1

When excel data is imported, List collection is circulated and checked, there are a lot of database connection operations.

//Each line of circular excel
for (int j = sheet.getFirstRowNum() + 1,len = sheet.getLastRowNum(); j <= len; j++) {
    Row row = sheet.getRow(j);
    if (row == null) continue;
    if (StringUtils.isEmpty(getValue(row.getCell(6)))) resultBuffer.append("The case type is empty.");
    //Other checking operations are omitted.
    //Connect to the database
    SysUser sysUser = sysUserService.findById(id);
}

2.2 Question 2

Direct batch storage of more than 30,000 data.

List<PcsTestcase> pcsTestcases = new ArrayList<>();
// ......
//Direct call batch save  
this.batchCreate(pcsTestcases);

2.3 Question 3

When saving in batches, use UUID generation tool to set Id for the primary key. Find out the mechanism triggered by Hibernate's first query and then update, which causes unnecessary query wastage.

List<PcsTestcase> pcsTestcases = new ArrayList<>();
PcsTestcase pcsTestcase = null;
for (int j = sheet.getFirstRowNum() + 1,len = sheet.getLastRowNum(); j <= len;j++) {
    Row row = sheet.getRow(j);
    if (row == null) continue;
    pcsTestcase = new PcsTestcase();
    //Here, it's important: when inserting data, set the primary key Id
    pcsTestcase.setId(UUIDUtils.generate());
    pcsTestcase.setPmMilestoneId(pcsMainTask.getId());
}

3. Solutions

3.1 Problem One Solution

For Problem 1, besides avoiding linking databases in for loops, we can use the caching mechanism of Map collections to load the data needed later into map collections. For example: query all user data in turn, and so on, stored in the Map collection.

3.2 Problem 2 Solutions

For Question 2, we can save all the data in batches every 500 items, which is faster than one-time batches. Specifically as follows:

if(j % 500 == 0 || j == len){
    this.batchCreate(pcsTestcases);
    pcsTestcases = new ArrayList<>();
}

3.3 Problem Three Solutions

For Question 3, Hibernate determines whether the data is inserted or updated when inserting. If the primary key of the model is not empty, after querying the data, update the data, otherwise, insert the data. Therefore, when we do insert operations, we do not set the primary key of the model to avoid unnecessary query consumption.

pcsTestcase.setId(UUIDUtils.generate());

That's the basic idea of optimization, and it's done with great success.~~~

Reading Comprehension

Kou Jun, a famous Prime Minister of the Northern Song Dynasty, taught people to realize repentance as early as possible.

• Six Regrets
  Official private music, regret lost time.
  Rich is not frugal, poor is repentant.
  Art is a lot of learning, outdated regret.
  Never learn when you see things, but regret when you use time.
  Drunk talk, wake up regret.
  If you don't rest, you will regret when you are ill.

Classic Story

A professor and a farmer are sitting opposite each other on a train when they are bored. Professor said: I come up with a problem, if you don't know, give me 5 yuan. If you come up with a problem, if I don't know, how about 500 yuan? The peasants agreed. Professor: How far is the moon from the earth? The farmer didn't say a word and handed it to the professor for 5 yuan. Farmer asked: What kind of animal is it that has three legs up the hill and four legs down the hill? The professor was puzzled and helpless to give the peasants 500 yuan. The peasants took the money and went to bed. Professor asked: What kind of animals are the three legs on the hill and the four legs on the hill? The farmer didn't say a word and handed the professor five yuan, then he went to bed. Low education and high IQ are terrible! That's why many uneducated people can become bosses and richest men. ]

Great God Articles

[1][Java/JDBC.ORM] jdbc optimizes processing when inserting large amounts of data
[2]Hibernate batch operation optimization (batch insertion, update and deletion)
[3]Optimization of inserting large amounts of data into database with Java

Other

If there is a trace of happiness to you, let happiness continue to pass on. Welcome to praise, praise, and leave valuable comments, thank you for your support! ___________