Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id D4E3A9FA2CC for ; Mon, 11 Dec 2006 15:22:31 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 27606-03 for ; Mon, 11 Dec 2006 15:22:20 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.186]) by postgresql.org (Postfix) with ESMTP id D936E9F9357 for ; Mon, 11 Dec 2006 15:22:21 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id x4so3644nfb for ; Mon, 11 Dec 2006 11:22:20 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=PpHKNEL7DzQwSQtDLsadY3O9Pci4PfEX2eGJCgrXUf+ka7CBTWZSIXbc/JZrv8Tr90zsh6pui0uYiMpYYnHsvlZzf0r90haQvNnKczYuNpDXj9i3NU0brpbqCoUv6vm5MmWvAI3hCVmfepzCMm+pc+u453o0bPZjtCGx4Zcmwlk= Received: by 10.82.182.8 with SMTP id e8mr1106066buf.1165864939754; Mon, 11 Dec 2006 11:22:19 -0800 (PST) Received: by 10.82.161.3 with HTTP; Mon, 11 Dec 2006 11:22:19 -0800 (PST) Message-ID: <1f30b80c0612111122i347e5f31k39a8193db2d4cd10@mail.gmail.com> Date: Tue, 12 Dec 2006 00:22:19 +0500 From: imad To: "Andreas Kretschmer" Subject: Re: really quick multiple inserts can use COPY? Cc: pgsql-performance@postgresql.org In-Reply-To: <20061211165731.GA11970@KanotixBox> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <20061211165731.GA11970@KanotixBox> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/255 X-Sequence-Number: 22311 On 12/11/06, Andreas Kretschmer wrote: > Jens Schipkowski schrieb: > > > Hello! > > > > In our JAVA application we do multiple inserts to a table by data from a > > Hash Map. Due to poor database access implemention - done by another > > company (we got the job to enhance the software) - we cannot use prepared > > statements. (We are not allowed to change code at database access!) > > First, we tried to fire one INSERT statement per record to insert. This > > costs 3 ms per row which is to slow because normally we insert 10.000 > > records which results in 30.000 ms just for inserts. > > Can you change this from INSERT-Statements to COPY? Copy is *much* > faster than INSERT. > > If no, do you using all INSERTs in one transaction? I believe, a 'BEGIN' > and a 'COMMIT' around all INSERTs may increase the speed. Performance increment can also be gained by disabling constraints in the transaction. These disabled constraints are invoked at the end of the transaction according to the SQL standard, so no worries about data consistency. Hmmm... PG currently supports disabling foreign constraints only. But that can still be significant. --Imad www.EnterpriseDB.com