If you're loading stuff into a database via DBI, bind variables are generally the fastest way. Avoid Class::DBI or any other wrappers, and look into the execute_array method maybe, to do the insert in batches.

If you can use them, look into the external loader utilities for the various databases - it might well be faster to output SQL strings from Perl and pipe them into mysqlldr, or use the parser language of bcp or sqlldr instead of doing the munging in Perl.

Please show us a small complete program that demonstrates your problem.

per your request, the loop is added to the node.

For example, if you were inserting into Sybase, then all other things being horribly equal, you would be

1) building a huge uncheckpointed transaction log and

2) locking database resources on a grand scale

3) creating a single transaction of gigantic proportions.

Any one of which even taken alone could be causing such symptoms.

If the database isn't Sybase, the same cause may be happening conceptually, but will demonstrate different database-dependent symptoms.

Please may we have the rest of the loop plus the identity and version of the RDBMS.

the rest of the loop is there, as well as the "secret identity" of the RDBMS. ( MySQL 4.1.10 )

In this case, the mysql LOAD DATA syntax command appears to be the way to go as another responder has already suggested. The replies that suggest using perl classes are missing the point that the database just can't take all those inserts - that's why databases come with bulk loading facilities.

One world, one people

I have no idea if this is the actual problem that you are seeing, but long series of inserts are very, very bad for most databases.

If you already have indexes on the table, then every single insert had to modify the table, and the indexes. If you have constraints, then they need to be checked for every record.

If the process can be done completely through the database's builtin loading program (mysql's LOAD DATA syntax, Oracle's SQL*Loader, etc.), then use it directly. This will defer the modifications to the indexes until after everything has completed, and other optimizations for bulk changes.

If the data needs some sort of cleaning before insertion, then I would recommend cleaning the values, and then writing back out a text file ... then using the database's loading utility to slurp in the processed file.

use strict;
my $file="filename";

my $file_size = 30720; #grab file size in MB.
my $chunks = 1226; #How many pieces
my $size = int $file_size / $chunks + 1;

my $counter = 0;

for(0..$chunks){
    my $skip = $size * $counter;
    `dd if=$file of=$file.$counter bs=1M count=$size skip=$skip`;
    $counter++;
}
[download]

If you're going to hand off the work to dd, you might want to use split, as it can act on full lines (so won't break in the middle of a record, given the logic the OP was using.).

You also don't need to recursively call it, as the equivalent to your dd example would be:

split -b 30720m -a 3 $INFILE

I used dd because it can access directly a position in a file using skip, and then sequentialy read its content, without needing to load the whole file and then point to a location.

because i need to reorder the fields (well, i need to identify the MLSNumber (since it's real estate data) and see if the data needs and ins/upd and if the data needs to be inserted into a view ... i don't see the MySQL loader being the right tool for the job ...

What if you made an initial "analyze/index" pass over the big file, to identify the updates vs. inserts, identify the view inserts, etc. In other words, "parse" the big file first, and maybe even split it up into pieces according to what needs to be done with each subset (if you have enough disk to store a second copy of it all -- but if you don't have that much space, writing a set of byte-offset indexes is likely to take a lot less than 334G).

Once you know how to break down the file contents according to what sort of treatment they need, push the insert records into mysqlimport -- that will go remarkably fast with fairly low load on the system. Maybe you'll still need to handle updates via DBI, but that will be easier to optimize and will go a lot quicker if you avoid doing DBI inserts in the same run (and make sure the "where" clause cites an indexed field, and use prepared statements with "?" placeholders, etc).

Of course, anything you do with 334G of data is going to take a while, and the idea of doing your job in multiple passes over the data might seem silly. But think about it: each pass will be easier to code, easier to validate, and relatively efficient and fast at runtime with relatively low system load, compared to a monolithic "one pass to do it all".

In the long run, the time required to do a lot of little tasks in succession could easily end up being less than the time required to do them all at once in a single massive job.

use Tie::File;
tie @array, 'Tie::File', $filename or die $!;

for (@array) {
   # parse
   # insert
}
[download]

after reading the perldoc for Tie::File it sounded like a good idea™ ... but it's not behaving per docs.

I'm trying:

tie my @array, 'Tie::File', $filename, mode => O_RDONLY;
my $recs = @array;
warn "we have $recs records to parse ";
pop @array;

foreach my $element ( @array ) {
   print "ok, we'd be updating $element \n";
}
[download]

the  tie statement is straight out of the perldoc.

i need to skip the first line during processing since it's a "header" line.

the error that comes back is:

we have 280579 records to parse  at ./upd_res_data2.pl line 72, <FH> l
+ine 280579.
Couldn't write record: Bad file descriptor at /usr/lib/perl5/5.8.0/Tie
+/File.pm line 665, <FH> line 280580.
[download]

and i don't think it *should* be writing, because it's (supposedly) opened read-only. any insight?

I tried:

file.txt

# header
1
2
3
4
5
6
[download]

test.pl

use Tie::File;
tie @f, 'Tie::File', "file.txt" or die $!;
foreach (@f[1..$#f]) {
    print $_, "\n";
}
[download]

Result is:

1
2
3
4
5
6
[download]

---

Igor S. Lopes - izut
surrender to perl. your code, your rules.