Meaningless Drivel is fun!
The moose likes Performance and the fly likes reading and writing large data(200000) from oracle Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Performance
Bookmark "reading and writing large data(200000) from oracle" Watch "reading and writing large data(200000) from oracle" New topic

reading and writing large data(200000) from oracle

vasu mannem
Ranch Hand

Joined: Dec 11, 2003
Posts: 38
Hi Everyone,
here I have one problem ...
read data from three different tables A,B,C total 200000 and process each record and then insert these 200k records in to another oracle table.

currently ,iam using batch program to read and write the data ..

batch program calls java/ejb then call three daos to get the data..then validation of each record and then finally storing the data in to another table..

i tested for 250 records for the same process then it took 5 minutes ..and wondering how much time it will take for 200000 record.
i think 66 hours for complete job with 200000 records...

can any one help me the best process to reduce the time for this kind of job?

here is my current process...

read data from table a (100000 records)

read 1000 ,process ,then insert.

same process from table b and c.

Srikanth Basa
Ranch Hand

Joined: Jun 06, 2005
Posts: 241
Is there any specific reason why you aren't considering PL/SQL procedures for performing validation of the batch of records ?
Ilja Preuss

Joined: Jul 11, 2001
Posts: 14112
The first thing to do would be finding out what's taking so long. Is it validation? Populating the EJBs? Writing them back to the database?

The soul is dyed the color of its thoughts. Think only on those things that are in line with your principles and can bear the light of day. The content of your character is your choice. Day by day, what you do is who you become. Your integrity is your destiny - it is the light that guides your way. - Heraclitus
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 17410

Probably the #1 performance killer when doing a bulk database load is indexes. It's usually advisable to load the tables flat first, and only afterwards build indexes on them. Otherwise the load process spends inordinate amounts of time rebalancing the indexes during the load process.

Unless there's some compelling reason to do so, I'd skip Java for this kind of operation and use the native database utilities. You can load a table from another table in Oracle using a CREATE table from SELECT statement, although this may not be sufficient if your validation process is complex. For more complex variants, I'd generally either build a set of the keys of the good records and use that or else copy everything and tag or delete the bad stuff. Depending on what worked best for the situation.

An IDE is no substitute for an Intelligent Developer.
Indumuni Abeyaratna

Joined: Sep 24, 2008
Posts: 1
if possible break this process into multiple transactions. Single transaction could take time since it had to keep track of all what it had done.
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 17410

Bulk loader utilities often split the difference. Setting up and committing a transaction is a fair amount of overhead, but as Indumuni mentioned, so is keeping a large work-in-progress/rollback queue.

So it's common for the bulk loaders to run in something like 1000 records per transaction.
I agree. Here's the link:
subject: reading and writing large data(200000) from oracle
It's not a secret anymore!