Dry Goods ~ With multiple threads, 10 million pieces of data will be inserted into the database in one minute.

Dry Goods ~ With multiple threads, 10 million pieces of data will be inserted into the database in one minute.

 

Environment: before preparing for the test, I checked that my company's computer is 4-core (four subwindows)

 

To put it bluntly, directly attach the code and result.

Package tenThreadInsert; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. preparedStatement; import java. SQL. SQLException; import java. util. date; public class MyThread extends Thread {public void run () {String url = "jdbc: mysql: // 127.0.0.1/teacher"; String name = "com. mysql. jdbc. driver "; String user =" root "; String password =" 123456 "; Connection conn = null; try {Class. forName (name); conn = DriverManager. getConnection (url, user, password); // get the connection conn. setAutoCommit (false); // disable automatic submission, otherwise conn. when commit () runs to this sentence, an error is returned.} catch (ClassNotFoundException e1) {e1.printStackTrace ();} catch (SQLException e) {e. printStackTrace ();} // start time Long begin = new Date (). getTime (); // SQL prefix String prefix = "INSERT INTO test_teacher (t_name, t_password, sex, description, pic_url, school_name, regist_date, remark) VALUES "; try {// Save the SQL suffix StringBuffer suffix = new StringBuffer (); // set the transaction to non-automatic commit conn. setAutoCommit (false); // pst is better than st in PreparedStatement pst = (PreparedStatement) conn. prepareStatement (""); // prepare and execute the statement // the outer loop. The total number of transactions committed for (int I = 1; I <= 10; I ++) {suffix = new StringBuffer (); // step for the j-level commit (int j = 1; j <= 100000; j ++) {// construct the SQL suffix. append ("('" + I * j + "', '000000'" + ", 'male'" + ", 'instructor '" + ", 'www .bbb.com '"+", 'java '"+", '"+" 14:43:26 "+"', 'note' "+ "),");} // construct the complete SQLString SQL = prefix + suffix. substring (0, suffix. length ()-1); // Add and execute SQLpst. addBatch (SQL); // execute the operation pst.exe cuteBatch (); // submit the transaction conn. commit (); // clear the last added data suffix = new StringBuffer ();} // first-class connection pst. close (); conn. close ();} catch (SQLException e) {e. printStackTrace ();} // end time Long end = new Date (). getTime (); // time consumed System. out. println ("1 million data insertion time:" + (end-begin)/1000 + "s" + "inserted ");}}

Test code

package tenThreadInsert;public class Test {public static void main(String[] args) {for (int i = 1; i <=10; i++) {      new MyThread().start();}}}

Running result:

Database results:

As you can see, 10 million entries are inserted, which is accurate.

Then I want to try 20 threads. Each thread inserts 0.5 million pieces of data to see how the performance works. The result is as follows:

 

The more threads, the slower it is...

When the number of threads reaches 100, each thread inserts 0.1 million data records, directly causing heap memory overflow.

Now, the test is over. If you want to talk about it, add a group to discuss the technology: 494389786