Received: from finch-post-11.mail.demon.net (finch-post-11.mail.demon.net [194.217.242.39]) by postgresql.org (Postfix) with ESMTP id 1C689475A86 for ; Thu, 21 Feb 2002 16:06:38 -0500 (EST) Received: from lfix.demon.co.uk ([158.152.59.127] helo=linda.lfix.co.uk) by finch-post-11.mail.demon.net with esmtp (Exim 3.34 #1) id 16e0QE-0008o2-0B; Thu, 21 Feb 2002 21:06:36 +0000 Received: from localhost ([127.0.0.1] helo=localhost.localdomain ident=olly) by linda.lfix.co.uk with esmtp (Exim 3.34 #1 (Debian)) id 16e0TG-0001XC-00; Thu, 21 Feb 2002 21:09:42 +0000 Subject: Re: Help with many updates From: Oliver Elphick To: Glenn MacGregor Cc: pgsql-sql@postgresql.org In-Reply-To: <006901c1baf5$3bce6360$4d00a8c0@catamount> References: <006901c1baf5$3bce6360$4d00a8c0@catamount> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Evolution/1.0.2 Date: 21 Feb 2002 21:09:42 +0000 Message-Id: <1014325782.3217.823.camel@linda> Mime-Version: 1.0 X-Archive-Number: 200202/290 X-Sequence-Number: 6713 On Thu, 2002-02-21 at 16:31, Glenn MacGregor wrote: > What is the preferred way to do many updates. Right now I am just doing > each row at a time, is there a better way? I have about 1000 or so and it > takes a good 45 seconds If you are currently doing: INSERT... INSERT... you can speed it up by enclosing the whole lot in a single transaction: BEGIN INSERT... INSERT... COMMIT You can get it even faster by formatting the input data in a text file and using the COPY command (not in a transaction). -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "My sheep hear my voice, and I know them, and they follow me; And I give unto them eternal life; and they shall never perish, neither shall any man pluck them out of my hand." John 10:27,28