How can i increase the perfomence of jdbc

[siri_suresh]

Hi friends,
I have a que , that que is gettting the data(records) very fastly (updates very fastly).Those records have to be inserted in data base very fastly. prasently it is taking so much time , say for example 3 min per 1000 records. I want with in 20 sec(max). Can we have insert into DB using java that much fastly ? Is there is any technic to improve the performence in jdbc.
Pl suggest me the some way to get this.
Thanking u
Suresh

[Jamie Robertson]

what database/jdbc driver combination are you using?
some huge performance gains can be made using PreparedStatements instead of Statements and Batch updating can also vastly improve performance if utilized correctly.
Jamie

[David O'Meara]

"siri_suresh",
The Java Ranch has thousands of visitors every week, many with surprisingly similar names. To avoid confusion we have a naming convention, described at http://www.javaranch.com/name.jsp .
We require names to have at least two words, separated by a space, and strongly recommend that you use your full real name. Please log in again with a new name which meets the requirements.
Thanks.
Dave.

[siri_suresh]

Hi Jamie , thanks for ur suggision.
I am using Oracle,OCI8 Driver and my table have 60 colomns(max fields are varchar type).
Can u suggist how we can do batch updation.
Thanking u
Suresh Kumar

Originally posted by Jamie Robertson:
what database/jdbc driver combination are you using?
some huge performance gains can be made using PreparedStatements instead of Statements and Batch updating can also vastly improve performance if utilized correctly.
Jamie

[Jamie Robertson]

in general:
http://java.sun.com/docs/books/tutorial/jdbc/jdbc2dot0/batchupdates.html - these can also be done on PreparedStatements and CallableStatements(if driver supported)
For Oracle: http://otn.oracle.com/sample_code/tech/java/sqlj_jdbc/files/advanced/advanced.htm - scroll down to "Batch Update Performance Extension". There is sample code and explanations within the code
In general, Batch Updates work best in some sort of loop:

PreparedStatement psUpdate = con.prepareStatement("insert into...values(?,?)");
PreparedStatement psQuery = con.prepareStatement("select id, name from...");
ResultSet rs = psQuery.executeQuery();
while (some query to get info.next()) //or while (an ArrayList has more items)
{
psUpdate.setString(1,rs.getString(1));
psUpdate.setString(2,rs.getString(2));
psUpdate.addBatch();
}
int[] count = psUpdate.executeBatch();
System.out.println("Number of Rows Inserted: "+ count.length);

or something like that
Jamie

[Ananya Bhushan]

Hi Suresh!
As u told u are using oci8 driver so driver is not the problem with u.Using prepared or callable statemets can improve performance but it will not be of much help .You can use Batch Updates as per JDBC2.1. Batching large no. of statements can help in significant performance gain.Then one more thing which can help ya is to have a connection pool which will increase the performance considerably .Then if you can use JDBC2.0 u will have the option of using Cashed RowSet which will significantly improve the performance as it caches the rowset on client end till that ResultSet is open.