Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id D978EB5DBC4 for ; Mon, 27 Jun 2011 10:02:51 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 76759-08 for ; Mon, 27 Jun 2011 13:02:45 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from nm30.bullet.mail.bf1.yahoo.com (nm30.bullet.mail.bf1.yahoo.com [98.139.212.189]) by mail.postgresql.org (Postfix) with SMTP id 060A7B5D803 for ; Mon, 27 Jun 2011 10:02:44 -0300 (ADT) Received: from [98.139.212.146] by nm30.bullet.mail.bf1.yahoo.com with NNFMP; 27 Jun 2011 13:02:44 -0000 Received: from [98.139.212.204] by tm3.bullet.mail.bf1.yahoo.com with NNFMP; 27 Jun 2011 13:02:44 -0000 Received: from [127.0.0.1] by omp1013.mail.bf1.yahoo.com with NNFMP; 27 Jun 2011 13:02:44 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 848572.75667.bm@omp1013.mail.bf1.yahoo.com Received: (qmail 36754 invoked by uid 60001); 27 Jun 2011 13:02:44 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1309179764; bh=cwuMn5a8bsw6sjn18hynJfjiaMwyUfvEfgzxh1GoHa4=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=HlarkWF6ut7IDGszW2i1zqInr7ASrbVL6ucTWOgAvsrRpVJmW7yj4UvmPAhLhKTV5C5z79crS0RC6EDVoZOXkbRzbvUTUEBDhbiN1S8Rg2zUqZdHkh9XlTEgqhSLNRyGPY6m1dAnnXovYYnEtIkfAS/jHstOuv2I8t2LoZbVCo0= 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=yz6iieZ+3rlgpxQY6It/2huaKKGfnwkNuyAFQVbawb/IzZGE2Zmkiou8nXdtrTmV+7CoApx5KNLS2BoQ6T2bFeRw4XVpDbYE695rpOcDuQ2nUQLHkd41geuBPVxVv0eG68W4HSoY19BCo82PAFIAU99C3tSUb81dvkOHvozuquc=; Message-ID: <733155.36648.qm@web161424.mail.bf1.yahoo.com> X-YMail-OSG: IxB8ZWwVM1msoNOqv6BmUlAiVVOdv44rY5C7pOl0eo_UHUm re9bNAWO_kA9LtDBgEAtpTPJ4ib.jX9yoLjTuhVX65fsFpPTxkuds9c0K3bh ouP4ioSFQlnGJU3HATpFuM4AKdE2UD4tZEXS2Fjhc57_UH1PpIBj73VR4CEf ygXYwPnzQ9IVYmxse6NpK_PtBqKcWYX3_7fHEkvbfOfdoPtCNf5vIvbzNPix 5zQI_1AJSUEB5nl4T32Tg_wgyq3PRZk6g6aT9Dj.CMK_dlrFDw_8o9nbqCRC Z.mn7O6GDU2DxXmKT8mp7kFThdjHdHZFPHuf6xBBjqhM8j6azO0nC5XkIoa. SWpmEUS1VSq4.LnRSh.A7iXfgQFRdeoj8KLH79Heb7_3tsQbcZvdyd3_gyXf XBtu5slFP.ao64.df Received: from [124.120.220.3] by web161424.mail.bf1.yahoo.com via HTTP; Mon, 27 Jun 2011 06:02:44 PDT X-Mailer: YahooMailClassic/14.0.3 YahooMailWebService/0.8.111.304355 Date: Mon, 27 Jun 2011 06:02:44 -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="-1181573717-449051971-1309179764=:36648" 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/98 X-Sequence-Number: 35564 ---1181573717-449051971-1309179764=:36648 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable forgive me for brain storming a little re copy: if there are a limited number of tables you're inserting, would there be an= ything wrong with the app opening a copy connection? =A0ie, a connection in= itiates the copy and then stays open like a pipe for any inserts coming thr= ough it. =A0visually it's a very cool paradigm, but is it actually a good i= dea? --- 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 > --=20 Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql ---1181573717-449051971-1309179764=:36648 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
forgive me for brain storming a little re cop= y:

if there are a limited number of tables you're insert= ing, would there be anything wrong with the app opening a copy connection? =  ie, a connection initiates the copy and then stays open like a pipe f= or any inserts coming through it.  visually it's a very cool paradigm,= but is it actually a good idea?

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

From: Pavel Stehule <pavel.stehule@gmail.com>Subject: Re: [SQL] best performance for simple dml
To: "chester c youn= g" <chestercyoung@yahoo.com>
Cc: pgsql-sql@postgresql.org
Date:= Monday, June 27, 2011, 1:05 AM

2011/6/27 c= hester c young <chestercyoung@yahoo.com>
>> two questions:
> I thought copy was for multiple rows - is its= setup cost effective for one row?

I expect it will be faster for on= e row too - it is not sql statement

if you want to understand to per= formance issues you have to understand to

a) network communication c= osts
b) SQL parsing and SQL planning costs
c) commits costs
d) oth= er costs - triggers, referential integrity costs

>
> copy w= ould also only be good for insert or select, not update - is this right?
sure,

If you need to call a lot of simple dml statement in cycl= e, then

a) try tu move it to stored function
b) if you can't to m= ove it, then ensure, so statements will be
executed under outer transact= ion

slow code

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

10x faster code

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

Regards

Pavel = Stehule

>
> --- On Mon, 6/27/11, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
> From: P= avel Stehule <pavel.stehule@gmail.com>
&g= t; Subject: Re: [SQL] best performance for simple dml
> To: "chester = c young" <chestercyoung@yahoo.com>
> C= c: pgsql-sql@postgresql.org
> Date: Monday= , June 27, 2011, 12:35 AM
>
> Hello
>
> try it and you will see. De= pends on network speed, hw speed. But the most fast is using a COPY API
= >
> http://www.postgresql.org/docs/9.0/interacti= ve/libpq-copy.html
>
> Regards
>
> Pavel Stehul= e
>
>
> 2011/6/27 chester c young <chestercyoung@yahoo.com>
>
> what is the best performa= nce / 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
>

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to yo= ur subscription:
http://www.postgresql.org/mailpref/pgsql-sql
---1181573717-449051971-1309179764=:36648--