>
> A few thoughts come to mind...
>
> DBD::Pg isn't really doing any "preparing," so only for the case of Pg, it
> might speed things up to just generate the SQL yourself rather than have
> DBI/DBD do it for you. (At the expense of performance if you switch to
> some other database at some later time, of course.)
>
> Similar to what Javier Dussaillant pointed out, perhaps you should try
> doing something with the output of pg_dump. Consider running pg_dump,
> modifying its output, and then reloading the modified file. That's what I
> might do under similar circumstances.
>
> As long as we're looking at actual code, I have a couple of suggestions
> that, while probably won't improve the performance significantly, certainly
> cannot hurt.
>
> ($file=$file)=~s/\.tlr/\.dta/g;
>
> should probably be $file =~ s/\.tlr$/\.dta/; Because you already know
> (from the glob) that the file name ends in 'tlr', you could also just do
> substr($file, -3) = 'dta';
cool thanks.
>
> chop($line);
>
> chop is semi-deprecated, chomp is probably what you wanted here.
old habits.
>
> >
> >$ins_sum->execute($fld[4],$fld[5],$fld[6],$fld[7],$fld[8],$fld[9],
> > $fld[10],$fld[11],$fld[12],$fld[13])
>
> I am too lazy to study whether under what conditions you would reap
> performance benefits, but even if there were no benefit to doing so, I
> recommend coding such lines as $ins_sum->execute(@fld[4 .. 13])
>
I recoded these two different ways and used the $dbh->do method.
the two ways
first:
my $upd_sum = "update tqossum set trm = ?, inspdt = ?, svc = ?, mfg = ?, pltcd =
?, crtrk = ?, nbrinsp = ?, clninsps = ?, drtyinsps = ? where tqossumrwd =?";
$dbh->do($upd_sum,undef,$fld[5],$fld[6],$fld[7],$fld[8],$fld[9],
$fld[10],$fld[11],$fld[12],$fld[13],$fld[4]) || die cleanup("execute upd_sum");
second:
my $upd_sum = "update tqossum set trm = $fld[5], inspdt = '$fld[6]', svc = '$fld[7]',
mfg = '$fld[8]', pltcd = '$fld[9]', crtrk = '$fld[10]', nbrinsp = $fld[11], clninsps =
$fld[12], drtyinsps = $fld[13] where tqossumrwd = $fld[4]";
$dbh->do($upd_sum) || die cleanup("execute upd_sum");;
Between these two ways there are virtually no difference between them speedwise.
And the "do" method is only slightly faster than prepare/execute.
It is amazing I can load the data on a little SMP linux box faster than
our HP N4000 machine running Informix. But, the N4000 can run this
script using Informix DBD in minutes. I guess I will take a look
at what postgres functions can do for me. I am basically trying to
replicate these two tables on a secure system. So, I am applying
basically transaction logs. My tables are large 300000+ and 440000+
but mostly stable. Although a current record may change 100 times
in a hour. So, if I "pull-off" transactions every 10 minutes I will
have multiple updates to some records. I guess I could dump the tables
and put into a hash and apply changes. But, that could to be a memory
hog with one the tables being 50+ meg. And I don't think increasing
the buffers Postgres has will help.
--
Internet: | Paul J. Falbe |
[EMAIL PROTECTED] | Cassens Transport | Std disclaimers apply.
Voice: | 145 N. Kansas Str. | (But you knew that!)
618-656-3006 | Edwardsville, IL 62025 |
