Java uses JDBC to insert 10W pieces of data into MySQL database batch (test efficiency)

How to improve the efficiency when using JDBC to connect to MySQL database for data insertion, especially the continuous insertion of large quantities of data (100000)?

In the JDBC programming interface, there are two methods of statement that deserve special attention:

Batch processing of data can be achieved by using the pair of methods addbatch() and executebatch().

However, it is worth noting that first, you need to set manual submission in the database link, connection Setautocommit (false), and then execute connection after executing statement commit()。

Test results:

Quantity = 100001 run time = 4725

A total of 10 W and a total execution time of 47 seconds

This efficiency is still not high and does not seem to achieve the desired effect, which needs further improvement.

You can also add parameters to the MySQL JDBC connection string,

MySQL turns off batch processing by default and opens it through this parameter, which can rewrite the SQL statement submitted to the database

If it is not enabled (useserverprepstmts = false), use COM MysqL. jdbc. The Preparedstatement is assembled with local SQL and finally sent to DB, which has been replaced? Final SQL after

The following statement is added to the connection string (the comment is removed from the code construction method):

The retest results are as follows:

Quantity = 100001 run time = 1213

With the same amount of data, this execution took only 12 seconds, which shows that the processing efficiency is greatly improved, ha ha

The above is what Xiaobian introduced to you. Java uses JDBC to insert 10W pieces of data into the MySQL database batch to test the efficiency. I hope it will be helpful to you. If you have any questions, please leave me a message, and Xiaobian will reply to you in time. Thank you very much for your support for the programming tips website!