This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes Performance Issues with database insert !! Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Performance Issues with database insert !!" Watch "Performance Issues with database insert !!" New topic
Author

Performance Issues with database insert !!

Manoj Singh
Ranch Hand

Joined: Aug 10, 2000
Posts: 41
Hi guys !
Problem Statement : I have a flat file containing around 1300 rows with each row having 16 columns. In total all these columns map to 5 tables in my database. I have to write a program which reads this file and loads the data in appropriate columns within the relevant tables.
I tried using createStatement...it seems to be taking forever ...even preparedStatement is not very efficient. Can anyone suggest any other of doing this efficiently. It's kind of urgent.
Thanking you all in advance !
Manoj


Sun Certified Java 2 Programmer<BR>" Only those who will risk going too far can possibly find out how far one can go "
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

what database/driver combo are you using? The best performance can be achieved by using batch updates (if your driver supports it):
http://java.sun.com/docs/books/tutorial/jdbc/jdbc2dot0/batchupdates.html
if you can't use batch updates, then maybe you could post the jdbc code so we can help you "tweak" your code for the best possible performance.
Jamie
Manoj Singh
Ranch Hand

Joined: Aug 10, 2000
Posts: 41
Thanks Jamie !
Actually I am parsing the flat file using a StringTokenizer class and that passing each line to the jdbc code for inserting into the database.
Also I am using MS Access for the time being but will switch to DB2 soon.
Once I have the a single row in a variable called line2 this is what I am doing.


Connection con = null;
Statement cs = null;
//PreparedStatement ps =null;
ResultSet rs = null;
int [] i;

try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String dsn = "jdbc dbc:EZ";
con = DriverManager.getConnection(dsn, user, pwd);

cs = con.createStatement();


// allthe variables used below have been defined (they are tokens from Stringtokenizer class)

cs.addBatch("insert into Constituent values('"+Constituent_Id+"','"+Detail5+"','"+name+"','"+name+"','"+Detail8+"')");
cs.addBatch("insert into Data_Type values('"+File_id+"','"+File_type+"','"+length+"','"+detail+"')");
cs.addBatch("insert into Client_Specific_Data_type values('"+Detail2+"','"+Detail3+"','"+Detail6+"')");
cs.addBatch("insert into Data_Type_Destination values('"+File_Type_Detail+"')");
cs.addBatch("insert into Data_Type_Validation values('"+Detail4+"')");
cs.addBatch("insert into File values('"+path+"','','"+Detail7+"','','')");
cs.addBatch("insert into Transaction_Log values("+Id2+",'','',,'')");

i = cs.executeBatch();








} catch(IOException e ){
e.printStackTrace();

} catch(ClassNotFoundException e) {
System.out.println("Couldn't load database driver " + e);

} catch(SQLException e) {
System.out.println("SQL Exception caught: " + e);

} catch(Exception e){
System.out.println("Some damn exception has occurred "+ e);

} finally {
try {
if (con != null)
con.close();
} catch (SQLException e) {
System.out.println(e);

}
}

}

Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

just make sure that if you have 1300 rows, you don't execute the batch 1300 times.
you should execute the batch only once in your program.
ie.
declare Statement/connection as class variables.
statement= con.createStatement();
open io file
while (more records in flat file)
{
parse the record variables
add the batch statements for the parsed variables
} // end of flat file records
close io file
execute the batch file once
close statement/connection
end
or something like that so that you are not executing the batch for every record in the flat file.
Jamie
Manoj Singh
Ranch Hand

Joined: Aug 10, 2000
Posts: 41
Hi Jamie !
Thanks for the help ! Now it has improved considerably..initially it was taking 6-7 minutes now 50-55 seconds....that's good enough I guess.
ThanX
Manoj
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

you can get some more gains by using PreparedStatements. Let me know how this works out.

Jamie
 
 
subject: Performance Issues with database insert !!
 
Similar Threads
Hibernate Metadata Search
Writing Database output to a file
How to map one class to multiple similar tables with Hibernate?
Reg : two hbm file for one java class...........
automatically populate database from xml