aspose file tools*
The moose likes JDBC and the fly likes When to commit Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "When to commit" Watch "When to commit" New topic
Author

When to commit

Dushyant Bhardwaj
Greenhorn

Joined: Apr 18, 2004
Posts: 28
Dear All,
I am working on a requirement where I read a flat file
containing 10,000+ records. I am reading it line by line
and on each iteration i.e. for each row inserting in DB.
At the end I am issuing commit , but the problem is till this
time My oracle server has to keep the records in buffer and that
becomes a bottleneck.
I can't commit after each iteration also because that will
de-grade the performance.
What would be the best solution for this.
Thanks & Regards,
Dushyant Bhardwaj
Stefan Krompass
Ranch Hand

Joined: Apr 29, 2004
Posts: 75
Originally posted by Dushyant Bhardwaj:
At the end I am issuing commit , but the problem is till this
time My oracle server has to keep the records in buffer and that
becomes a bottleneck.

Hi,
I cannot imagine that it is a performance problem with your Oracle database.
If you can do without Java, you can use the SQL*Loader for inserting your data into the database.
cu
Stefan
Blake Minghelli
Ranch Hand

Joined: Sep 13, 2002
Posts: 331
Shouldn't your decision of when to commit be based on what is a logical unit of work rather than performance? I admit that I am not a transaction expert, but it seems that you should ask yourself this:
If the procesing of a single row in your file fails, should that really rollback all of the changes (possibly 9,999)? Is the integrity of the data secured only if all 10,000 records are successfully inserted?
If you are even considering commiting after every row, then my guess is that the answer to those questions is "no".
I think the other thing you should consider is how long it takes to process all 10,000 records. If it takes X minutes and you don't commit until the end then the db will keep a lock on the first record for X minutes, correct?
Other than that, have you done any performance comparisons between the 2 approaches?


Blake Minghelli<br />SCWCD<br /> <br />"I'd put a quote here but I'm a non-conformist"
Sushil Srivastava
Greenhorn

Joined: Oct 21, 2003
Posts: 1
Dushyant,
Using Java to load data from flat file into an Oracle Database seems using wrong set of tools for solving your problem.The correct technlogy to use would be Oracle's SQL Loader which uses batch commit and you can also use the direct path insert option which is even more efficent for large amounts of data.Using Java you will end up writing overly complicated code which will have to handle data complexity perhaps using StringTokenizer's , loads of JDBC code etc for no good reason and will be very inefficent.
If you still insist on doing it then commit it in batches of say 100 or 1000 etc.you would need a counter as well in that case which commits afte the set size and resets counter.
If I were you I would use SQL Loader for efficency as much simpler and better and the right tool for right problem

PS: The problem on Oracle Server of buffer you are talking of, I think you are getting problems with Rollback segment running out of space and needing extension.This happens when large amounts of changes are made on database without committing.Either you commit more often or have a much bigger rollback segment for handling the records.Especially for loading large amounts of data Oracle's direct path load is most efficent
[ May 01, 2004: Message edited by: Sushil Srivastava ]

SCWCD SCJP OCP OCA
Dushyant Bhardwaj
Greenhorn

Joined: Apr 18, 2004
Posts: 28
Dear All,
Thanks for your valuable suggestions.
But as suggested to use SQL Loader , I can't do that because
the file is uploaded on the WEB (using JSP+Servlet) by client.
I m doing it using batch commit of a batch size of 100.
My question is what is the capacity of a batch statment - i.e the
batch size with optimized performance.
Thanks & Regards
Dushyant Bhardwaj
Piyush Mattoo
Ranch Hand

Joined: Mar 12, 2007
Posts: 30
How about calling the Oracle SQL Loader .exe file from your Java code!!!


SCWCD-1.4, SCJP-1.4, CSM.
John Kimball
Ranch Hand

Joined: Apr 13, 2009
Posts: 96
Are you sure the degradation is in JDBC?

How much of the file are you reading in at a given time?
John Kimball
Ranch Hand

Joined: Apr 13, 2009
Posts: 96
Wow, I didn't realize the previous poster revived a long-dead thread.

Wth...

Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38902
    
  23
John Kimball is right: read this.
 
 
subject: When to commit