pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Greg Sabino Mullane <greg@turnstep.com>
To: pgsql-performance@postgresql.org
Subject: Re: COPY Hacks (WAS: RE: Postgresql vs SQLserver for this application ?)
Date: Fri,  8 Apr 2005 02:03:18 -0000
Message-ID: <067c42864e24cd39b83c335da6adc8b5@biglumber.com> (raw)
In-Reply-To: <1112813199.42542e8f17b4d@webmail.telus.net>


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> Using COPY ... FROM STDIN via the Perl DBI (DBD::Pg) interface,
> I accidentally strung together several \n-terminated input lines,
> and sent them to the server with a single "putline".
...
> So. Is it a feechur? Worth stress-testing? Could be VERY cool.

As explained elsewhere, not really a feature, more of a side-effect.
Keep in mind, however, that any network round-trip time saved has to
be balanced against some additional overhead of constructing the
combined strings in Perl before sending them over. Most times COPY
is used to parse a newline-separated file anyway. If you have a slow
network connection to the database, it *might* be a win, but my
limited testing shows that it is not an advantage for a "normal"
connection: I added 1 million rows via COPY using the normal way
(1 million pg_putline calls), via pg_putline of 1000 rows at a
time, and via 10,000 rows at a time. They all ran in 22 seconds,
with no statistical difference between them. (This was the "real" time,
the system time was actually much lower for the combined calls).

It can't hurt to test things out on your particular system and see
if it makes a real difference: it certainly does no harm as long as
you make sure the string you send always *end* in a newline.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200504072201
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFCVeZrvJuQZxSWSsgRAoP+AJ9jTNetePMwKv9rdyu6Lz+BjSiDOQCguoSU
ie9TaeIxUuvd5fhjFueacvM=
=1hWn
-----END PGP SIGNATURE-----





view thread (6+ messages)

Message-ID: <067c42864e24cd39b83c335da6adc8b5@biglumber.com>
Permalink:  ../067c42864e24cd39b83c335da6adc8b5@biglumber.com/
Also on:    postgresql.org/message-id/067c42864e24cd39b83c335da6adc8b5@biglumber.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: greg@turnstep.com
  Subject: Re: COPY Hacks (WAS: RE: Postgresql vs SQLserver for this application ?)
  In-Reply-To: <067c42864e24cd39b83c335da6adc8b5@biglumber.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox