Question about massive data insert

[mikedaruke]

I am using DBI and I actually have a simple script that reads in a CSV file and inserts the rows into a database table.

My problem is that there is 125,000 rows. The script takes to long.

What can I do? Is there some function to dump massive amounts of data? Or should I not be looking at perl to do this?

 

[travs69]

If you are doing the inserts one at a time I would suggest building bigger inserts. I'm not sure at what point it starts degrading but I went from doing 10K individual inserts to one massive insert statement and it saved me like 2min of processing time.

something like
insert into table (col1, col2, col3) values (data1a,data1b,data1c),(data2a,dasta2b,data2c),(data3a,data3b,data3c)


you will need to reference the DBI documentation though because I might be slightly off on the syntax (cpan isn't working for me right now)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[MacTommy]

What you might also do is have a look at the documentation on LOAD DATA INFILE

It says: The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. The filename must be given as a literal string.

And the idea is that it is a LOT faster than anything else you might try...

 

[mikedaruke]

Not sure that works with Oracle, looks like mysql

 

[MacTommy]

Yes, I am sorry. I wasn't aware you were using Oracle...
Wouldn't be suprised though if Oracle has similar functionality. I mean, you won't be the first one trying to get a lot of data into a table at once...

 

[PaulTEG]

OraCmd might be worth a look

Paul
------------------------------------
Spend an hour a week on CPAN, helps cure all known programming ailments ;-)

 

[MacTommy]

Also, there is something called SQL*Loader that seems to do the trick (I don't have any experience though with either Oracle or this SQL*Loader, I just came across it...)

http://www.orafaq.com/wiki/SQL*Loader_FAQ

 

[muralimk05]

"I have to read each line from a CSV file and insert same data into a DB2 Table" number of lines in CSV may be 100000 or 10000"

if any of you having the script please send me. Thanks in Advance.

muralimk05@gmail.com