| Inserting huge amount of data into Oracle database [message #115016] |
Wed, 06 April 2005 16:33  |
joelny
Messages: 4
Registered: April 2005
|
Junior Member |
|
|
Hi all,
I was desperately seeking for help on database and SQL tuning and clicked into this forum.
I am developing an ASP.NET application with Oracle9i Enterprise Edition 9.2.0.4.0 as my back-end. In the application I need to read a flat file (~25Mb), and import all the data into a few tables.
The flat file contains 3000 records, each made up of 5001 characters. In those 5001 characters are many fields that are not separated by any delimiter. I can only use character positions to tell where a field starts and ends.
Currently I am reading the entire file into my application, which handles the parsing of the data, takes out useful data, and make a lot of calls to a few stored procedures I have. Those stored procedures will then perform INSERTs into different tables.
Now for each record I need to call 4 different procedures, and I'm doing the same thing for 3000 records. This has created an extremely heavy load on my database and takes up a lot of resources. Each import is inefficient and currently takes as long as 10 minutes.
I've researched and found out about SQL 's BULK INSERT and BULK BIND features, but I'm not sure whether they are supported by Oracle. BULK INSERT requires a delimiter character which I don't have, since I'm solely depending on character positions.
I'm very new in SQL programming. I don't know much about database/SQL tuning but I'm willing to learn.
Are there any experts here that can give me advice on how to improve efficient on this import?
Thanks a million!
- Joel
|
|
|
|
|
|
|
|
| Re: Inserting huge amount of data into Oracle database [message #115026 is a reply to message #115022] |
Wed, 06 April 2005 16:57  |
joelny
Messages: 4
Registered: April 2005
|
Junior Member |
|
|
Thank you very much for your reply!
I've got one question though. Is sql*loader an application outside of the database, or can I just call it from inside a Stored Procedure?
The users of my web application will be the ones eventually performing the data importing, and it would be best if they can do everything only from the application.
I will keep reading up on the docs you listed. Thanks again for your reply.
|
|
|
|
|
|
| Re: Inserting huge amount of data into Oracle database [message #115028 is a reply to message #115027] |
Wed, 06 April 2005 17:36 |
joelny
Messages: 4
Registered: April 2005
|
Junior Member |
|
|
Mahesh,
Thanks for your reply.
The application I'm developing will only be used by the a certain department in my company, and the application resides on the intranet, so I'm targeting on about 10 users.
The reason why I would like to run everything from my app is that those users aren't computer-proficient, and it would be best to keep everything simple for them. Now I have a few textboxes with browse buttons for them to choose files from their computer (just like how you'll add an attachment to this post), then the application should deliver all the data into the database nicely. The application also allows them to search and create reports on those data.
I've talked to a few more people and they all agreed with you that importing should be handled from the database side, instead of from the application.
|
|
|
|
| Re: Inserting huge amount of data into Oracle database [message #115463 is a reply to message #115016] |
Mon, 11 April 2005 16:14 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Would an option be to:
first upload the data file from their pc to the database server.
second, load the data into a staging table via sql loader or external table
third, process the staging table to put the data into its final resting place
fourth, purge the staging table and the file to clean up for next time
I haven't had to use external tables yet, but wouldn't the basic approach be to call a stored procedure with the name of the file on the server as a parameter, and then use execute immediate to create the table using that name. Might avoid havin to call a program, like sqlloader, that is outside the db.
Other option would be to have whatever process uploads the file to at the end call the sqlloader program with some sort of system call. But either way gotta get the file onto the db server.
If you can't, and must process the file on the client side through your app, then don't do any actually processing. Just read the data (a chunk of rows at a time) into one "wide" structure of some kind (like an array) and then insert it directly into a staging table via your app. Then process the staging table with stored db procedures.
Anyway just some thoughts (ramblings?) on your situation in case it helps.
|
|
|
|
| Re: Inserting huge amount of data into Oracle database [message #115480 is a reply to message #115016] |
Mon, 11 April 2005 19:46 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
With external tables, ideally the file name (or names - there can be a list) is fixed, and you would place the data file in the appropriate directory with the expected filename.
If that is not feasible you can issue an ALTER TABLE to change the external table's LOCATION attribute without invalidating dependant objects. This might take a bit more coding to prevent two sessions changing it at the same time.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
