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,
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"
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 ]
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