SQL Data import

Hard to say without seeing your code or knowing what database you're using. The first thing that comes to mind is if you're creating new database connections or new Statements for every row then you might be overloading the network stack on the database server, particularly if you're not explicitly closing connections or statements when you're done with them. If you're not already using a PreparedStatement, try making sure you open one connection for the duration of the import, then use a single PreparedStatement for all the inserts. Also, consider using addBatch and executeBatch if your JDBC driver supports it. This lets the driver queue up a bunch of inserts and then submit them to the database in one block. Something like:

open the file

Connection con = connect to the database

PreparedStatement pstmt = con.prepareStatement("insert into xyz(field1, field2, field3) values(?,?,?)");

while( more lines in the file )

{

parse the next line

pstmt.setString(1, data1); // or setInt, setFloat, etc.

pstmt.setString(2, data2);

pstmt.setString(3, data3);

pstmt.executeUpdate();

}

pstmt.close();

con.close();

or

static final int MAX_BATCH = 50;

int batch size = 0;

open the file

Connection con = connect to the database

PreparedStatement pstmt = con.prepareStatement("insert into xyz(field1, field2, field3) values(?,?,?)");

while( more lines in the file )

{

parse the next line

pstmt.setString(1, data1); // or setInt, setFloat, etc.

pstmt.setString(2, data2);

pstmt.setString(3, data3);

pstmt.addBatch();

batchSize ++;

if( batchSize == MAX_BATCH ) {

pstmt.executeBatch();

batchSize = 0;

}

}

if( batchSize != 0 )

pstmt.executeBatch();

pstmt.close();

con.close();

Depending on what you're doing now, these approaches may drastically reduce the network traffic you're generating and might solve your problem.

Jemiah

fishninja007a at 2007-7-13 0:22:46 >