Inserting bulk data using Java

[Halyn]

I have a massive insertion information from a table into a temporary table. The query inserts 280000 lines. If I execute MySQL INSERT command with SELECT it take 11 seconds. If I program in Java (with PreparedStatement) it consumes 10 MIN! Can anyone help me so that it is done in Java but quickly?

[void]

If both tables are on the same server, you can use the syntax:

  INSERT INTO theTableTemp 
  SELECT Column
  FROM theTableSource

Possibly by prefixing the table names with the name of the database if it is not the same.

If they are on two different servers, you can use SELECT INTO OUTFILE / LOAD DATA INFILE that transfers data to a text file and then retrieves the data from text file generated in step.

[Halyn]

Good answer but I want in JAVA as I mentioned in my first post.

Here is my code which I tried but it consumes 10 MIN:

ResultSet result = statement.executeQuery(sql2+ " LIMIT "+ind+","+package);
	String insert = "insert into tablesTemporary.T7951254728407921N207(`clients.wanted`,`clients.name`,`polices.ancpolice`,`polices.police`,`polices.company`,`fleets_principal.id_fleets`,`fleets_principal.type`,`fleets_principal.prim_year_ttc`,`fleets_principal.num_police`,`polices.coderisk`,`polices.guarantees`,`fleets_principal.prim_year_ht`,`fleets_principal.etat`,`clients.numclient`,`clients.clientrelationship`,`polices.id`,`fleets_param.field14`,`fleets_param.field0`,`fleets_param.field1`,`fleets_param.field2`,`fleets_param.field12`,`fleets_param.field15`,`fleets_param.field16`,`fleets_param.field17`,`fleets_param.field56`,`fleets_param.field53`,`fleets_param.field57`,`fleets_param.field55`,`fleets_param.field52`) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	con2.setAutoCommit(false);
	PreparedStatement preparedStatement = con2.prepareStatement(insert);
	while(result.next()){
		preparedStatement.setString(1, result.getString(1));
		preparedStatement.setString(2, result.getString(2));
		preparedStatement.setString(3, result.getString(3));
		preparedStatement.setString(4, result.getString(4));
		preparedStatement.setString(5, result.getString(5));
		preparedStatement.setInt(6, result.getInt(6));
		preparedStatement.setString(7, result.getString(7));
		preparedStatement.setDouble(8, result.getDouble(8));
		preparedStatement.setString(9, result.getString(9));
		preparedStatement.setString(10, result.getString(10));
		preparedStatement.setString(11, result.getString(11));
		preparedStatement.setDouble(12, result.getDouble(12));
		preparedStatement.setString(13, result.getString(13));
		preparedStatement.setLong(14, result.getLong(14));
		preparedStatement.setInt(15, result.getInt(15));
		preparedStatement.setInt(16, result.getInt(16));
		
		preparedStatement.setString(17,"");
		preparedStatement.setString(18,"");
		preparedStatement.setString(19,"");
		preparedStatement.setString(20,"");
		preparedStatement.setString(21,"");
		preparedStatement.setString(22,"");
		preparedStatement.setString(23,"");
		preparedStatement.setString(24,"");
		preparedStatement.setString(25,"");
		preparedStatement.setString(26,"");
		preparedStatement.setString(27,"");
		preparedStatement.setString(28,"");
		preparedStatement.setString(29,"");
		
		preparedStatement.addBatch();
		nb++;
		if((nb % 100) == 0) {
	    	preparedStatement.executeBatch();
	        con2.commit();
		}
		if(nb == package){
			ind += package;
			result = statement.executeQuery(sql2+ " LIMIT "+ind+","+package);
			nb = 0;
		}
	}
    preparedStatement.executeBatch();
    con2.commit();

[CitricAcid]

That's something I understand. Why not simply swinging your "insert into ... select" in your java code, instead of retrieving one to one each line, to recreate one of the requests and send them one by one?

You lose any optimization mysql by doing that, it's not surprising that it takes 10 times more lead, you got so many requests for more.

Instead of having 1 mysql query that can optimize, you:

280000 / 100 = 2800 Query Selection
+ 280000 requests for inclusion
= 282800 petitions.

It's like moving a bag of 300000 seeds, seed by seed.

[opaper]

It is imperative to perform manual commits every x inserts.

I had to script insertion massive or I worked before. I did an extraction of all data from the database and then I reinsert them anonymous. I remember that I made every 2500 commits INSERT, was what took the least time.

But I did not used multi-thread, the fact of separate commits had sufficiently improved performance.