X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 62B9153314 for ; Fri, 8 Apr 2005 03:03:23 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 89593-03 for ; Fri, 8 Apr 2005 02:03:14 +0000 (GMT) Received: from biglumber.com (biglumber.com [207.228.252.42]) by svr1.postgresql.org (Postfix) with SMTP id 416C55399D for ; Fri, 8 Apr 2005 03:03:13 +0100 (BST) Received: (qmail 21523 invoked from network); 8 Apr 2005 02:03:18 -0000 Received: from unknown (HELO localhost) (207.228.252.42) by 0 with SMTP; 8 Apr 2005 02:03:18 -0000 From: "Greg Sabino Mullane" To: pgsql-performance@postgresql.org Subject: Re: COPY Hacks (WAS: RE: Postgresql vs SQLserver for this application ?) X-PGP-Key: 2529 DF6A B8F7 9407 E944 45B4 BC9B 9067 1496 4AC8 X-Request-PGP: http://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 In-Reply-To: <1112813199.42542e8f17b4d@webmail.telus.net> Date: Fri, 8 Apr 2005 02:03:18 -0000 X-Mailer: JoyMail 1.48 Message-ID: <067c42864e24cd39b83c335da6adc8b5@biglumber.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.145 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/176 X-Sequence-Number: 11573 -----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-----