Array load from remote site through Perl/DBI

Gauthier, Dave wrote:

I have a perl/dbi app that loads my DB with sequential and discrete
insert statements. Runs very fast and I�m satisfied with it. Now I
have to run the same app from a different site, but loading my local
DB. The �one at a time� inserts take too long, probably because of the
client/server delays incurred with the remote DB attach.

I was thinking of pooling all the data for the insert into arrays and
then doing a single array insert, therby cutting down on all the
back/forth. But there may be other approaches.

I suggest using COPY instead of INSERT, if all you're doing is adding
bulk data to a database.

Again, Perl/DBI, remote attach, Running v8.2.0 on Linux

Version 8.2.6 is the latest release in the 8.2 branch. You really
shouldn't be running 8.2.0 anymore.

Colin

On Tue, Mar 11, 2008 at 1:09 PM, Gauthier, Dave <dave.gauthier@intel.com> wrote:

I have a perl/dbi app that loads my DB with sequential and discrete insert
statements. Runs very fast and I'm satisfied with it. Now I have to run
the same app from a different site, but loading my local DB. The "one at a
time" inserts take too long, probably because of the client/server delays
incurred with the remote DB attach.

You can either wrap all the inserts in a begin; end; pair or you can
use copy from stdin. pg_dump uses copy from stdin for an example.

I was thinking of pooling all the data for the insert into arrays and then
doing a single array insert, therby cutting down on all the back/forth. But
there may be other approaches.

Hmmm. I think as long as you're moving things in a transaction you'll
probably be ok.

On Tue, Mar 11, 2008 at 1:09 PM, Gauthier, Dave <dave.gauthier@intel.com> wrote:

Again, Perl/DBI, remote attach, Running v8.2.0 on Linux

Oh, and update NOW. schedule a 5 minute maintenance window and update
to 8.2.6 (or if 8.2.7 comes out while this is in transit, that...)