Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 1EF251337BA2 for ; Thu, 16 Dec 2010 08:38:03 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 98247-06 for ; Thu, 16 Dec 2010 12:37:57 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from ibook.office.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by mail.postgresql.org (Postfix) with ESMTP id BBE651337B9A for ; Thu, 16 Dec 2010 08:37:55 -0400 (AST) Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36]) by ibook.office.archonet.com (Postfix) with ESMTP id A90A37C02D; Thu, 16 Dec 2010 12:37:55 +0000 (GMT) Message-ID: <4D0A081E.9010103@archonet.com> Date: Thu, 16 Dec 2010 12:37:50 +0000 From: Richard Huxton User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.2.13) Gecko/20101208 Lightning/1.0b2 Thunderbird/3.1.7 MIME-Version: 1.0 To: Werner Scholtes CC: Divakar Singh , "pgsql-performance@postgresql.org" Subject: Re: performance libpq vs JDBC References: <128DB53227227D43B4AAEED73E89A81901E2C4B584@vm-exchg-2k8.heuboe.hbintern> <209185.49892.qm@web65409.mail.ac4.yahoo.com> <128DB53227227D43B4AAEED73E89A81901E2C4B58B@vm-exchg-2k8.heuboe.hbintern> <4D0A02A8.5050206@archonet.com> <128DB53227227D43B4AAEED73E89A81901E2C4B590@vm-exchg-2k8.heuboe.hbintern> In-Reply-To: <128DB53227227D43B4AAEED73E89A81901E2C4B590@vm-exchg-2k8.heuboe.hbintern> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.918 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RDNS_DYNAMIC=0.982 X-Spam-Level: X-Archive-Number: 201012/193 X-Sequence-Number: 41645 On 16/12/10 12:28, Werner Scholtes wrote: > Thanks a lot for your advice. I found the difference: My Java program > sends one huge SQL string containing 1000 INSERT statements separated > by ';' (without using prepared statements at all!), whereas my C++ > program sends one INSERT statement with parameters to be prepared and > after that 1000 times parameters. Now I refactured my C++ program to > send also 1000 INSERT statements in one call to PQexec and reached > the same performance as my Java program. So - it was the network round-trip overhead. Like Divakar suggested, COPY or VALUES (),(),() would work too. You mention multiple updates/deletes too. Perhaps the cleanest and fastest method would be to build a TEMP table containing IDs/values required and join against that for your updates/deletes. > I just wonder why anyone should use prepared statements at all? Not everything is a simple INSERT. Preparing saves planning-time on repeated SELECTs. It also provides some SQL injection safety since you provide parameters rather than building a SQL string. -- Richard Huxton Archonet Ltd