Of course, you could dispense with DBI altogether and use your RDBMS's loader. Oracle and MySQL both support most xSV formats (such as TSV, CSV, PSV, etc.)

Using a mass loader would indeed be the fastest option by far.

If you have to stick with Perl though, you can certainly gain speed. Tie::File has to do a lot of bookkeeping to a) find all the ends of lines b) update the file per your wishes. Maybe File::ReadBackwards is what you need: it is written to handle precisely the case you have efficiently.

How is File::ReadBackwards a substitute for Tie::File for this application?

---

Examine what is said, not who speaks.

"Efficiency is intelligent laziness." -David Dunham
"Think for yourself!" - Abigail
"Memory, processor, disk in that order on the hardware side. Algorithm, algorithm, algorithm on the code side." - tachyon

$sth->prepare( "INSERT INTO Blah ( a, b, c ) VALUES ( ?, ?, ? )" );
tie *TSV, 'File::ReadBackwards', $filename;
while( <TSV> ) {
  my @insert = processRow( $_ );
  $sth->execute( @insert );
  truncate $filename, tell TSV;
}
close TSV;
unlink $filename unless $errors;
[download]

Makeshifts last the longest.

If your using MySQL then look at using LOAD DATA infile. Most other RDBMSs will have a similar syntax.

(update: for that matter, might be faster/easier to put the 50 GB on a USB or firewire disk and fedex it to remote host... )

If you really do need to do all those row inserts over the WAN link, and if you are confident about knowing the difference between success and failure for each insert, then my first notion would be:

• configure the insertion script to read from STDIN
• write a line (anything) to STDOUT after each successful insert
• start it the first time on a given TSV.file like this:

  insertion_script < TSV.file > log.file
  [download]

tail +N TSV.file | insertion_script >> log.file
[download]

(In case you don't know "tail", it's a basic unix util, which could easily be emulated in perl as follows:

#!/usr/bin/perl

if ( @ARGV and $ARGV[0] =~ /^+(\d+)/ ) {
    $bgn = $1;
    shift;
}
while (<>) {
    print if $. >= $bgn;
}
[download]

Trying to do database operations on that scale, over any sort of WAN link (slow or otherwise) is a tad risky and sub-optimal, I think. Can't you find a way to (s)ftp the TSV file(s) to the database host, then use the database server's native bulk-loader tool on that host to finish the job?

(update: for that matter, might be faster/easier to put the 50 GB on a USB or firewire disk and fedex it to remote host... )

Therein lies the problem. This is not a one-time operation, but something done on a regular basis. Also, I am doing quite a bit of data cleaning -- the raw data files are generated by a crappy program over which I have no control; the 'date' fields, for example, don't always have valid 'datetime' data in them, so I must convert before insertion.

As much as I'd love to just mass-load the thing, it cannot be so.

You can simply repeat this as many times as necessary, simply using the appropriate value for N each time (how many lines in the log at present), until the log file has the same line count as the TSV file.

Some of these files are 500,000 lines long. That's a big log to scan just to find a restart point. I'm not sure that would be a good idea, but it's an interesting way to think about the problem.

After reading these replies, I'm considering loading the file into memory a 50MB (roughly) chunk at a time, inserting each chunk line-by-line while recording the current line in a file. Something like:

tie %status, 'Tie::Cfg', READ=>$statfile, WRITE=>$statfile;
$status{current_line} = 0;
do {
   @chunk_lines = load_chunk_from($file);
   for (@chunk_lines) {
     process_line($_);
     $status{current_line}++;
   }
} while (@chunk_lines);
[download]

Then, of course, reading the status file on start to see if I am mid-file and skipping the appropriate number of lines before resuming. What do you all think? Better approach?
^{--
$me = rand($hacker{perl});}

I would write the filename and line number to a log file after a block of lines. 1000 lines is probably a good size. The log file becomes a nice way to measure the progress, and to record what has been done. When there is a failure, find the last line for each file in the log.

Make transactions larger is a big performance boost for bulk inserts. I would make the group for a transaction the same size as the group for writing line numbers. For each group, I would write the log entry and commit.