File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes fast performance in jdbc insert Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "fast performance in jdbc insert" Watch "fast performance in jdbc insert" New topic
Author

fast performance in jdbc insert

monu garg
Greenhorn

Joined: Nov 24, 2004
Posts: 3
Hi,
I want to insert some 1 to 10 million records in oracle database.I am using
connection pool and prepared statement for this purpose.I have 3 different types of objects and data from them is inserted to 3 tables in database.I am making a batch of 200 for each query(3 total) and i am getting 4000 ms as total time to execute one batch.2500 ms is taken to commit batch to database and 1500 ms for constructing prepare statement for 3 batch.
Please tell me how i can improve my performance.

Thanks
Monu G
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30116
    
150

Monu,
Welcome to JavaRanch!

Do you have any indexes on the table? If so, I would consider dropping them during the large data insert. Also, look for contention on the database.

The prepared statement part should only be done once, since subsequent batches are of the same size. For the other part, I would suggest looking at the db statistics to see what is taking all the time. 2.5seconds to insert 200 rows sounds like a lot.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
monu garg
Greenhorn

Joined: Nov 24, 2004
Posts: 3
Thanks ,Jeanne.
I was printing jdbc log which was taking much of time.


Monu G
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: fast performance in jdbc insert
 
Similar Threads
Database Tuning for Performance
How to insert all the records except the failed one.
Clarification regarding Drivers
maximum open cursors exceeded
hibernate property order_insert / order_update