Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 09-11-2009
Member
 
Join Date: Jun 2009
Posts: 96
Inserting bulk data using Java

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?
Reply With Quote
  #2  
Old 09-11-2009
Member
 
Join Date: Nov 2008
Posts: 1,192
Re: Inserting bulk data using Java

If both tables are on the same server, you can use the syntax:
Code:
  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.
Reply With Quote
  #3  
Old 09-11-2009
Member
 
Join Date: Jun 2009
Posts: 96
Re: Inserting bulk data using Java

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:

Code:
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();
Reply With Quote
  #4  
Old 09-11-2009
Member
 
Join Date: Nov 2008
Posts: 1,022
Re: Inserting bulk data using Java

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.
Reply With Quote
  #5  
Old 09-11-2009
Member
 
Join Date: May 2008
Posts: 2,383
Re: Inserting bulk data using Java

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.
__________________
The FIFA Manager 2009 PC Game
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Inserting bulk data using Java"
Thread Thread Starter Forum Replies Last Post
Problem while inserting data in MS Access Cheng-Gong Windows Software 6 28-02-2011 08:27 PM
Validating Data in Java Ucchal Software Development 5 10-08-2010 10:42 AM
Data format in java Logan 2 Software Development 5 05-02-2010 02:16 AM
Lots of errors when inserting data into table MADGE25 Software Development 5 13-11-2009 05:49 PM
How to refresh data of Jtable in java sarmad_iu Software Development 1 28-05-2009 02:45 PM


All times are GMT +5.5. The time now is 03:13 AM.