A slow solution for inserting large amounts of data: BULK INSERT

Scene:

Items need to be imported into 2,200 cases. There are approximately 15 contact information on each row case that is imported into Excel. So the total contact method is about 30,000

The speed before the optimization here is 16-20 minutes between. It's too slow. Looking for a long time to optimize the method. Finally, we determine the use of MySQL Bulk Insert method to insert operation

the reason of slow and solution thought:

The reason for this slow is this project:

The insertion method inserts the program traversal loop. The speed of inserting one on MySQL is detected between 0.02s and 0.04s.

So the speed of the insertion of the 0.03*30000 bar is about 15 minutes.

So the insertion is impossible to optimize. Then go to the query optimization method. It is found that the method of mass insertion can improve the speed.

Increase the insertion speed of 30,000 data to about 1-2 minutes

here is a test demo example: Time to Insert 1000 data with JUnit test loop and insert 10,000 with BULK INSERT
1 Normal loop Insert 1000 data time Test

	@Test public
	void Insertcuiji () {
		 
		     list<cuiji> list= new arraylist<cuiji> ();
		     
		     Cuiji Cuiji = new Cuiji ();
		     Cuiji.setbankcode ("Jylh");
		     Cuiji.setcompanycode ("Qixin");
		     
		     for (int i = 0; i < 1000 i++) {
		    	  
		    	 Cuijimapper.insertselectiveo (Cuiji, "Jylh");
			}
		     
		     
			
			
	

The speed of execution is 38s, which is the speed of 0.038*1000. It's a little bit slow.
2 time used to insert 10,000 in bulk inserts

	@Test public
	void Insertcuiji () {
		 
		     list<cuiji> list= new arraylist<cuiji> ();
		     for (int i = 0; i < 1000 i++) {
		    	 Cuiji Cuiji = new Cuiji ();
			     Cuiji.setbankcode ("Jylh");
			     Cuiji.setcompanycode ("Qixin");
			     List.add (Cuiji);
		    	 
			}
		    Cuijimapper.insertlistcuiji (list);
	

Bulk inserts use 0.0356s This is the same as the speed of inserting a piece of data, so using BULK insert will greatly increase the data insertion speed, when there is a large data insert operation is to use BULK INSERT optimization

How to BULK insert:

DAO definition Layer Method

Integer Insertlistcuiji (list<cuiji> Cuiji);

MyBatis's SQL notation

<insert id= "Insertlistcuiji" parametertype= "java.util.List" >
 	 insert INTO ' case '. ' T_cuiji '
			(' Doccode ' ,
			  ' Companycode ',
			  ' bankcode ', '
			  notes ',
			  ' type ') 
		 values
		<foreach collection= "list" item= "Item" separator= "," index= "index" >
			(#{item.doccode},
			#{item.companycode},
			#{item.bankcode},
			#{item.notes},
			#{item.type})
		</foreach>

	</insert>

This allows you to bulk INSERT operations:

Note: When bulk operation data volume is large. For example I insert 3w data to operate the packet more than 1M will report Mysql you can change this value on the server by setting the Max_allowed_packet ' variable. Exception at this time to modify the MySQL max_allowed_packet size, the detailed solution please see:

http://blog.csdn.net/alice_qixin/article/details/78522600

Or divide the list into several pieces to insert.

Thanks for watching.