[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