From: Pavel Stehule <pavel.stehule@gmail.com>
To: chester c young <chestercyoung@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: best performance for simple dml
Date: Mon, 27 Jun 2011 15:40:35 +0200
Message-ID: <BANLkTinfROAifi4kr0fmi8JSien7BFYAcg@mail.gmail.com> (raw)
In-Reply-To: <733155.36648.qm@web161424.mail.bf1.yahoo.com>
References: <BANLkTik90qDGv-9CcHtV6i=abynywUkbNA@mail.gmail.com>
<733155.36648.qm@web161424.mail.bf1.yahoo.com>
Hello
2011/6/27 chester c young <chestercyoung@yahoo.com>
> forgive me for brain storming a little re copy:
>
> if there are a limited number of tables you're inserting, 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 for any inserts coming
> through it. visually it's a very cool paradigm, but is it actually a good
> idea?
>
depends on application. Usually you can use a connection better than just
"insert connection". I am thinking, so it doesn't carry some special - it
remove a connection cost, but nothing more. You can use a more connections
to do paralel inserts - it has a sense.
look on pgpool or other similar sw for connection pooling
Pavel
>
> --- 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 young" <chestercyoung@yahoo.com>
> Cc: pgsql-sql@postgresql.org
> Date: Monday, June 27, 2011, 1:05 AM
>
> 2011/6/27 chester c young <chestercyoung@yahoo.com<http://mc/compose?to=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 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
> right?
>
> 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 = 0; i < 1000; i++)
> exec("insert into foo values($1), itoa(i));
>
> 10x faster code
>
> exec('begin');
> for(i = 0; i < 1000; i++)
> exec("insert into foo values($1), itoa(i));
> exec('commit');
>
> Regards
>
> Pavel Stehule
>
> >
> > --- On Mon, 6/27/11, Pavel Stehule <pavel.stehule@gmail.com<http://mc/compose?to=pavel.stehule@gmail.com>;
> wrote:
> >
> > From: Pavel Stehule <pavel.stehule@gmail.com<http://mc/compose?to=pavel.stehule@gmail.com;
> >
> > Subject: Re: [SQL] best performance for simple dml
> > To: "chester c young" <chestercyoung@yahoo.com<http://mc/compose?to=chestercyoung@yahoo.com;
> >
> > Cc: pgsql-sql@postgresql.org<http://mc/compose?to=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 <chestercyoung@yahoo.com<http://mc/compose?to=chestercyoung@yahoo.com;
> >
> >
> > 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
> >
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org<http://mc/compose?to=pgsql-sql@postgresql.org;
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pavel.stehule@gmail.com, chestercyoung@yahoo.com
Subject: Re: best performance for simple dml
In-Reply-To: <BANLkTinfROAifi4kr0fmi8JSien7BFYAcg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox