[Chicago-talk] writing log files to a DB

Richard Reina richard at rushlogistics.com
Sun Mar 27 18:26:37 PST 2005


Jay,

Thank you very much for your response.

--- Jay Strauss <me at heyjay.com> wrote:

> you shouldn't prepare your statement in every
> iteration of the loop,
> that's a big database killer.

This is obviosly very good advice and something I
should have noticed.  I will make that change
immediately.

> 
> Why don't you sort your input file by timestamp
> decending (hi -> lo),
> then insert while your timestamp from the file is >=
> the max(timestamp)
> in your database.  Sorta like:
> 
> sort -r -n -t, --key=7,7  log_files | perl
> your_file.pl
> 
> #cat your_file.pl
> 
> my $q = "REPLACE INTO master_log VALUES
> (?,?,?,?,?,?,?,?)";
> my $sth = $dbh->prepare($q);
> 
> $TS # Populate this with a select max(timestamp)
> from your db
> 
> while (<>) {
> 
>     my ($src, $dsc, $clid, $chn, $PU, $HU,
> $timestamp,$dur) =
> split(",",$_);
> 
>     exit if $TS > $timestamp;
> 
>     $sth->execute($src, $dsc, $clid, $chn, $PU, $HU,
> $timestamp, $dur);
> 
> }
> 
> Jay
> 

If I understand this correctly, this begins to enter
entries in the file from newest to older until it
comes to an entry that was already in the table and
stops.  Am I understanding it correctly?

Does it then put the entries/records in the table from
oldest newest to oldest? Does this matter? Do I need
to index the file?
> _______________________________________________
> Chicago-talk mailing list
> Chicago-talk at pm.org
> http://mail.pm.org/mailman/listinfo/chicago-talk
> 



More information about the Chicago-talk mailing list