This week's giveaway is in the JDBC forum.
We're giving away four copies of Java Database Connections & Transactions (e-book only) and have Marco Behler on-line!
See this thread for details.
Win a copy of Java Database Connections & Transactions (e-book only) this week in the JDBC forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Devaka Cooray
  • Knute Snortum
  • Paul Clapham
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Bear Bibeault
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Ron McLeod
  • Piet Souris
  • Frits Walraven
Bartenders:
  • Ganesh Patekar
  • Tim Holloway
  • salvin francis

inserting 2.5 million records.....  RSS feed

 
Ranch Hand
Posts: 96
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi just wanted to know that what are the few, most efficient, methods to insert approx 2.5 million records (in a flat file with 30 columns) in Oracle 8i.

Currently we use a java program which reads the file and inserts it in the table but it takes around 4 - 5 hours to perform this task.Moreover we have to to this activity twice so in total 10 hours.

I think there are ways (procedures reading the flat file) through which it can be done quickly.

Please suggest.

himanshu
 
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is there a reason why you wrote your own application rather than just use SQL Loader? That's what I'd use. You might also try disabling indices before performing a bulk insert.
 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You may also want to check direct path insert (providing

append

hint) in case you have to use custom code for this. Pls. verify the pros and cons of the direct path insert however...
 
Himanshu Bisht
Ranch Hand
Posts: 96
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
well the main idea is to automate the whole process.

we tried the SQLLoader but it take the same time.

Want to know what will be the time taken by a fast application to insert 2.5 million records in an Oracle 8i table having 30 fields.

thanks
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
SQL Loader (using a direct path load, as Ajit Chandak recommends) is the quickest way I know of getting data into Oracle. SQL Loader allows you to have multiple concurrent sessions loading data too. If you have tried SQL Loader with a concurrent direct path load and is is no faster than you own application, then there is not much you can do to speed things up I'm afraid.


Want to know what will be the time taken by a fast application to insert 2.5 million records in an Oracle 8i table having 30 fields.


Thats pretty much impossible to say without knowing more about your environment. SQL Loader is usually your benchmark though.
 
Himanshu Bisht
Ranch Hand
Posts: 96
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks Paul,

Well my environment is Windowds/jdk 1.5/Oracle 8i.

I know you are a busy man,still whenever you get time please just check these links and let me know wheather they are discussing the same thing or not.

http://home.clara.net/dwotton/dba/java_insert.htm

http://home.clara.net/dwotton/dba/big_insert.htm

thanks,
Himanshu
[ March 03, 2006: Message edited by: Himanshu Bisht ]
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


Well my environment is Windowds/jdk 1.5/Oracle 8i.


Performance differences between OS probably exist, but when I mentioned environment I was more interested in memory, number/speed of processors, how your RDMBS is configured (character sets, indices, triggers etc.), if there is a network involved (and how fast it is if there is) etc. My point was the sort of question you asked:


Want to know what will be the time taken by a fast application to insert 2.5 million records in an Oracle 8i table having 30 fields.


is pretty much unanswerable by anyone but yourself. Test it and see. My point that SQL Loader is the quickest way I know to get data into Oracle, so if you want to know how your own application performs, compare it against the same data being loaded by SQL Loader.

A cursory glance suggests both those links seem to be talking about performance of bulk inserts via Java and PL/SQL, so they are pretty much in the same area as our discussion. Not sure how useful the Java programs are for your case though, since they perform all inserts within a single transaction. 2.5 million inserts in one transaction is going to be slow (since Oracle will need to keep track to what it has to potentially roll back). However, neither compare against SQL Loader.

The interesting line is right at the top of the PL/SQL article:


The intention was not to show how to load the maximum number of rows in the minimum amount of time (eg. using parallel loads etc.)


so I don't think its what you are looking for. And again "parallel loads" will probably mean SQL Loader (or a bespoke equivalent application, if you could be bothered writing it).

In my experience the only reason I'd write my own bulk loading application is if I needed to apply business rules to the data that cannot be easily done with SQL Loader. For example, if a "valid" record cannot be represented by DB constraints alone, I'd add some validation in my own loading app.
[ March 03, 2006: Message edited by: Paul Sturrock ]
 
He was giving me directions and I was powerless to resist. I cannot resist this tiny ad:
how do I do my own kindle-like thing - without amazon
https://coderanch.com/t/711421/engineering/kindle-amazon
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!