Two Laptop Bag*
The moose likes JDBC and the fly likes Performance problem in jdbc Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Performance problem in jdbc " Watch "Performance problem in jdbc " New topic
Author

Performance problem in jdbc

Muthu Ponmozhi
Greenhorn

Joined: Dec 17, 2004
Posts: 21
Dear all,
I need your valuable ideas on improving below logic on replicating data fromDB2 to Oracle 9i.We have a huge tables in DB2 to replicate to Oracle side.
For one table this taking lot of time.The whole app' is written in java.
The current logic is Setting soft delete to specific set of records in oracle table and Reading all records from DB2 table to set only these records in oracle table to 'N' so that deleted records got soft deleted in oralce side.The DB2 query is having 3 table join and taking nearly 1minute using this resultset We are updating the oracle table in batch of 100000.For 610275 records update in batch mode is taking 2.25 hours .Combinely ,the first update to all Y' and second update to N' using DB2 query is taking 2.85 hrs.this has to be reduced to <=1hour.The same approach in other table is taking only 1.15 hrs as a whole, but here the corres.DB2 query has only 2 tables join.The 3table join could be the problem in this problematic case??
Do you have any clever idea to reduce this time?? kindly help us.Even new approach in above logic to replicate also welcome..


happy learning....<br>Ponmozhi</br> <br>SCJP 1.5,SCWCD 1.5(preparing)</br>
Rajah Nagur
Ranch Hand

Joined: Nov 06, 2002
Posts: 239
Your question is not clear and a bit confusing. Please make shorter sentences.

Is this just migrating from one DB2 table to Oracle DB or is there sync also involved?


You can't wake a person who is <b><i>pretending</i></b> to be asleep.<br />Like what <b>"it"</b> does not like - <i> Gurdjieff </i>
Muthu Ponmozhi
Greenhorn

Joined: Dec 17, 2004
Posts: 21
Hello,
I am sorry for my large post. here is the short form. We have a java application to replicate data from db2 to oracle db (running weekly).As part of this,we have delete mode run which is making the 2 dbs in sync for the deleted records in db2 but not in oracle.This is taking lot of time for one particular huge table.The logic is like setting all records in oralce table to Y then read ids from db2, to set updated to not deleted in oracle. This is done using batch concept in jdbc executeBatch().DB2 query is taking 1 minute to read records with where clause of 3 tables join and updating in oracle table is involving 6 batches of 6.10 lak records taking 2.25hrs. we would like to reduce this time. I hope I'm little bit clear now.
May be using the resultset directly with this huge table query to update our db is taking time?? Any suggestions welcome.
Rajah Nagur
Ranch Hand

Joined: Nov 06, 2002
Posts: 239
Originally posted by Muthu Ponmozhi:
Hello,
I am sorry for my large post. here is the short form. We have a java application to replicate data from db2 to oracle db (running weekly).As part of this,we have delete mode run which is making the 2 dbs in sync for the deleted records in db2 but not in oracle.This is taking lot of time for one particular huge table.The logic is like setting all records in oralce table to Y then read ids from db2, to set updated to not deleted in oracle. This is done using batch concept in jdbc executeBatch().DB2 query is taking 1 minute to read records with where clause of 3 tables join and updating in oracle table is involving 6 batches of 6.10 lak records taking 2.25hrs. we would like to reduce this time. I hope I'm little bit clear now.
May be using the resultset directly with this huge table query to update our db is taking time?? Any suggestions welcome.



This is what I understand.

You have a batch job that runs weekly which syncs the deleted records from DB2 to Oracle.
The number of records in the Oracle Table is huge ( 6 lakh records).
The number of records in DB2 is relatively less.

The sync is done as follows:
1. Update all the records in the Oracle Table to "Y".
2. Select all the records from the DB2 that needs to be synced (i.e. deleted in Oracle Table)
3. Run a batchUpdate on Oracle to update the records selected from above DB2 query to set the flag to "N".

Is this correct?

Have you considered using Stored Procs.??

Send the DB2 results to a stored proc in Oracle to sync.

If possible paste the sample code.
Muthu Ponmozhi
Greenhorn

Joined: Dec 17, 2004
Posts: 21
Yes! you got the points exactly!The code snippet is as below:i have put only the second part which reads DB2 andupdates oracle side.Coz this only takes much time.Hav a look @this.If we copy the same logic in a stored proc will it make faster.

String resetQuery = "SELECT t3.INSTANCE_ID FROM TOHITYP t1, TZUBZUORDNUNG t2,TZUBPREIS t3 WHERE t1.F_HIERARCHIE_ID = t2.F_BESITZER AND t2.INSTANCE_ID = t3.F_ZUORDNUNG AND t1.produktionsende = '9999-12-31' ";
Statement stmt = oracleConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet oraRs = stmt.executeQuery(resetQuery);-->1 minute
oraRs.setFetchSize(100000);
String initdeleteQuery = "UPDATE TWOIZUBPREIS SET IS_DELETED = 'N' , WHERE ZUBPREIS_ID = ? ";
long totupdatecnt = 0;boolean allrecordsover = true;
int numRecEachBatch = 100000;
//-- going for batch update
{
PreparedStatement pstmt = oracleConnection.prepareStatement(initdeleteQuery);
while(allrecordsover)
{int count=0;
while(count 0)
{pstmt.executeBatch();
pstmt.clearBatch();
}
oracleConnection.commit();
}
pstmt.close();
}--> taken 2.25 hrs.
Muthu Ponmozhi
Greenhorn

Joined: Dec 17, 2004
Posts: 21
Hi,
We got performance improvement! Let me explain how we got it! We just created indexes in the DB2 side for those columns used in the select query and hence we got it by 1.30 hrs which is okie for our problem.
Thanks to Raja and all who tried a lot for my problem.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Performance problem in jdbc
 
Similar Threads
Fetch only 'n' records from the ResultSet
Using JDBC versus Stored Procedures
Performance tuning to select two whole tables for one-to-one mapping
oracle stored proc for tree
Bulk updates vs Sql query to update bulk records in Hibernate