Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id EA28FB5DBCB for ; Mon, 27 Jun 2011 04:49:57 -0300 (ADT) 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 80363-03 for ; Mon, 27 Jun 2011 07:49:51 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from nm29-vm0.bullet.mail.bf1.yahoo.com (nm29-vm0.bullet.mail.bf1.yahoo.com [98.139.213.166]) by mail.postgresql.org (Postfix) with SMTP id 08823B5D803 for ; Mon, 27 Jun 2011 04:49:50 -0300 (ADT) Received: from [98.139.212.151] by nm29.bullet.mail.bf1.yahoo.com with NNFMP; 27 Jun 2011 07:49:50 -0000 Received: from [98.139.212.193] by tm8.bullet.mail.bf1.yahoo.com with NNFMP; 27 Jun 2011 07:49:50 -0000 Received: from [127.0.0.1] by omp1002.mail.bf1.yahoo.com with NNFMP; 27 Jun 2011 07:49:50 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 282946.86444.bm@omp1002.mail.bf1.yahoo.com Received: (qmail 77343 invoked by uid 60001); 27 Jun 2011 07:49:50 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1309160990; bh=i1hqRmA2KLhDaejJciIp73Z6qPueiVSfG4+axKKcw0w=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=A9k+GcZK72RIC8p/ih/BPxBL6vD+IUOMSqHp0BW4bCH4oUBwLq6J7hH3wNBU+huyjCBbMXEcl/Gvy1TO70dc1kfiZK9dR8Vg3scmaSHBVx1TGVKg42i94WBL7mgu7Ff1if82G8nNdI2Pxv1w5d2dwaxb4xCpgIUPAjSlSgtSstY= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=rBDJWYvC7DDmlq6KUMxw2RRaDjX8HX7TNhkQZr3RKJc06w+PAmAnRG0iAboimE3jh8hgFzLup8Po6kbvi5DfcQZhxHMeeaExsPvQurgL3FDwgzGGw+qVudzAsKS5YMz/Mnhx7W3dzGa3UVNw1aq7vhNvytfCu5TRJozTFzhcqRw=; Message-ID: <2547.71122.qm@web161421.mail.bf1.yahoo.com> X-YMail-OSG: HhAj9PUVM1nICToW3mJ0CJ8YOciB5bwWF.4Kx_K5ta4B0WZ u1avqNUUApXUJh69xPnQrVdDEDyeL_it.RAqPfj57KLeLj3mxasWH0sfMBme wpTCmPnlgijKKuDRXprg9j4ytT1lyqPwywpGvVOPk9W1bRMsbj0oUkjEppLe 719D1Rb_9r6Wul6q2E3xhEG3RUFY59H2slaV28vdo.Pm1X4NMkFAc566jkPz CsxjUMfQ6aBrkM_2FKrF1jIo49wYUYWV3RUQ58RF6heWIWyQXQ1F0hRiuby0 8xtS0IA60SCPfZoo3hkxTEUgK25wUu.Ed8oWhqYwhv07k3n5kepTLHG.rLlZ AXtAAg16jTJL37BU3nxmBQJSgvEmJzmal9bsFSBz2.a7iYexjbaM574BkOBY 9PEnJQ6tQuFRjLAyu Received: from [124.120.220.3] by web161421.mail.bf1.yahoo.com via HTTP; Mon, 27 Jun 2011 00:49:49 PDT X-Mailer: YahooMailClassic/14.0.3 YahooMailWebService/0.8.111.304355 Date: Mon, 27 Jun 2011 00:49:49 -0700 (PDT) From: chester c young Subject: Re: best performance for simple dml To: Pavel Stehule Cc: pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="-1876451226-1294435204-1309160989=:71122" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.908 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201106/96 X-Sequence-Number: 35562 ---1876451226-1294435204-1309160989=:71122 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable very nice pointers. =A0thank you very much! --- On Mon, 6/27/11, Pavel Stehule wrote: From: Pavel Stehule Subject: Re: [SQL] best performance for simple dml To: "chester c young" Cc: pgsql-sql@postgresql.org Date: Monday, June 27, 2011, 1:05 AM 2011/6/27 chester c young > > two questions: > I thought copy was for multiple rows - is its setup cost effective for on= e row? I expect it will be faster for one row too - it is not sql statement if you want to understand to performance issues you have to understand to a) network communication costs b) SQL parsing and SQL planning costs c) commits costs d) other costs - triggers, referential integrity costs > > copy would also only be good for insert or select, not update - is this r= ight? sure, If you need to call a lot of simple dml statement in cycle, then a) try tu move it to stored function b) if you can't to move it, then ensure, so statements will be executed under outer transaction slow code for(i =3D 0; i < 1000; i++) =A0 exec("insert into foo values($1), itoa(i)); 10x faster code exec('begin'); for(i =3D 0; i < 1000; i++) =A0 exec("insert into foo values($1), itoa(i)); exec('commit'); Regards Pavel Stehule > > --- On Mon, 6/27/11, Pavel Stehule wrote: > > From: Pavel Stehule > Subject: Re: [SQL] best performance for simple dml > To: "chester c young" > Cc: pgsql-sql@postgresql.org > Date: Monday, June 27, 2011, 12:35 AM > > Hello > > try it and you will see. Depends on network speed, hw speed. But the most= fast is using a COPY API > > http://www.postgresql.org/docs/9.0/interactive/libpq-copy.html > > Regards > > Pavel Stehule > > > 2011/6/27 chester c young > > what is the best performance / best practices for frequently-used simple = dml, for example, an insert > 1. fast-interface > 2. prepared statement calling "insert ..." with binary parameters > 3. prepared statement calling "myfunc(..." with binary parameters; myfunc= takes its arguments and performs an insert using them > ---1876451226-1294435204-1309160989=:71122 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
very nice pointers.  thank you very much= !

--- On Mon, 6/27/11, Pavel Stehule <pavel.stehule@gmail.c= om> wrote:

From: Pavel Stehule &= lt;pavel.stehule@gmail.com>
Subject: Re: [SQL] best performance for s= imple dml
To: "chester c young" <chestercyoung@yahoo.com>
Cc: p= gsql-sql@postgresql.org
Date: Monday, June 27, 2011, 1:05 AM

2011/6/27 chester c young <che= stercyoung@yahoo.com>
>
> two questions:
> I thoug= ht copy was for multiple rows - is its setup cost effective for one row?
I expect it will be faster for one row too - it is not sql statement
if you want to understand to performance issues you have to understand to

= a) network communication costs
b) SQL parsing and SQL planning costs
= c) commits costs
d) other costs - triggers, referential integrity costs<= br>
>
> copy would also only be good for insert or select, not = update - is this right?

sure,

If you need to call a lot of si= mple dml statement in cycle, then

a) try tu move it to stored functi= on
b) if you can't to move it, then ensure, so statements will be
exe= cuted under outer transaction

slow code

for(i =3D 0; i < 1= 000; i++)
  exec("insert into foo values($1), itoa(i));

10x = faster code

exec('begin');
for(i =3D 0; i < 1000; i++)
&nbs= p; exec("insert into foo values($1), itoa(i));
exec('commit');

Re= gards

Pavel Stehule

>
> --- On Mon, 6/27/11, Pavel S= tehule <pavel.stehule@gmail.com<= /a>> wrote:
>
> From: Pavel Stehule <pavel.stehule@gmail.com>
> Subject: Re: [SQL] best performanc= e for simple dml
> To: "chester c young" <che= stercyoung@yahoo.com>
> Cc: pgsql-sql@p= ostgresql.org
> Date: Monday, June 27, 2011, 12:35 AM
>
= > Hello
>
> try it and you will see. Depends on network spee= d, hw speed. But the most fast is using a COPY API
>
> http://www.postgresql.org/docs/9.0/interactive/libpq-copy.html<= /a>
>
> Regards
>
> Pavel Stehule
>
>
> 2011/6/27 ch= ester c young <chestercyoung@yahoo.com>
&= gt;
> what is the best performance / best practices for frequently-us= ed simple dml, for example, an insert
> 1. fast-interface
> 2. = prepared statement calling "insert ..." with binary parameters
> 3. p= repared statement calling "myfunc(..." with binary parameters; myfunc takes= its arguments and performs an insert using them
>
---1876451226-1294435204-1309160989=:71122--