Home » RDBMS Server » Performance Tuning » Inserting huge amount of data into Oracle database
Inserting huge amount of data into Oracle database [message #115016] Wed, 06 April 2005 16:33 Go to next message
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 #115022 is a reply to message #115016] Wed, 06 April 2005 16:47 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
There are 3 options depending on certain situtations.
You have to lookinto which ever that work for you.
I fear, you may need to spend some time into documentation and get more details.
first bring the data from flat-file into oracle database


1. use sql*loader ( optionally with direct options)
sql*loader direct load bypasses certain RDBMS layer.
the file is READ and contents are loaded by sql*loader.

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/toc.htm


2. Use UTL_FILE built-in package.
read the file ( the file file has to be in serverside) line by line, apply business logics using pl/sql.

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/u_file.htm#998101

3. Making use of external tables.
depends on size of data and what you are going to do with it.
You can treat your flat file as and external oracle table.
and directly query it.!!

again, look into docs as above.
and Jared Still has a greate article here
http://www.dbazine.com/still1.shtml

Re: Inserting huge amount of data into Oracle database [message #115025 is a reply to message #115022] Wed, 06 April 2005 16:51 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>Currently I am reading the entire file into my application,

THis should be the job of database.
First bring in the data.

then, we can find efficient methods to pump the data into different tables ( depending on need).



Re: Inserting huge amount of data into Oracle database [message #115026 is a reply to message #115022] Wed, 06 April 2005 16:57 Go to previous messageGo to next message
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 #115027 is a reply to message #115026] Wed, 06 April 2005 17:08 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
OH!. I dont know how useful it is going to be for you.
But sql*loader can handle position data,
read one file and insert into multiple tables depending on a 'when' clause etc.

it is an OS utility outside the database AND is distributed along with oracle.
You can call it in a stored procedure ( actually a java stored procedure), it is a little more coding.

what i dont know is, how to use this in an web application, where any user can upload data from anywhere!..?

I will look into this...

Re: Inserting huge amount of data into Oracle database [message #115028 is a reply to message #115027] Wed, 06 April 2005 17:36 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
William Robertson
Messages: 1640
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.
icon4.gif  Re: Inserting huge amount of data into Oracle database [message #117675 is a reply to message #115480] Thu, 28 April 2005 16:23 Go to previous messageGo to next message
msingla
Messages: 3
Registered: April 2005
Junior Member
Hi,
I was facing a similar situation. I have a flat comma delimited file that I get by ftp from main frame onto a web server . Now I need to show that file as is either using a PDF format or html format to user..... How can I accomplish this.

Please recommend any solutions as I need to make this happen desperately.

Thanks
Re: Inserting huge amount of data into Oracle database [message #118465 is a reply to message #115016] Thu, 05 May 2005 07:41 Go to previous messageGo to next message
tycoonajayverma
Messages: 17
Registered: May 2005
Location: b'lore
Junior Member

hi,

As the above discussion use the sql*loader or ask ur DBA to do that. upload the data into one table with this utility and after that u can query that data into ur webpage.

thanks
Re: Inserting huge amount of data into Oracle database [message #188128 is a reply to message #118465] Thu, 17 August 2006 03:59 Go to previous messageGo to next message
vrsanaidu
Messages: 2
Registered: August 2006
Location: hyderabad
Junior Member

Hi, i am new to this forum, i am requesting all of u i got same problem..., but i am using sqlserver..., can anyone please suggest me how to insurt large data in csv file to database..., my database server is sqlserver

Thanks, hoping fast response
Naidu
Re: Inserting huge amount of data into Oracle database [message #188143 is a reply to message #188128] Thu, 17 August 2006 04:43 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Naidu,
this is a forum for Oracle users. Look for a SQL Server forum.
Good luck

Re: Inserting huge amount of data into Oracle database [message #188215 is a reply to message #188143] Thu, 17 August 2006 08:14 Go to previous message
vrsanaidu
Messages: 2
Registered: August 2006
Location: hyderabad
Junior Member

okey, thnx
Previous Topic: Staging table from partitioned table
Next Topic: Estimating cache size
Goto Forum:
  


Current Time: Wed Dec 07 04:44:33 CST 2016

Total time taken to generate the page: 0.12700 seconds