Re: best performance for simple dml

Hello

2011/6/27 chester c young <chestercyoung(at)yahoo(dot)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(dot)stehule(at)gmail(dot)com>* wrote:
>
>
> From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> Subject: Re: [SQL] best performance for simple dml
> To: "chester c young" <chestercyoung(at)yahoo(dot)com>
> Cc: pgsql-sql(at)postgresql(dot)org
> Date: Monday, June 27, 2011, 1:05 AM
>
> 2011/6/27 chester c young <chestercyoung(at)yahoo(dot)com<http://mc/compose?to=chestercyoung(at)yahoo(dot)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(dot)stehule(at)gmail(dot)com<http://mc/compose?to=pavel(dot)stehule(at)gmail(dot)com>>
> wrote:
> >
> > From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com<http://mc/compose?to=pavel(dot)stehule(at)gmail(dot)com>
> >
> > Subject: Re: [SQL] best performance for simple dml
> > To: "chester c young" <chestercyoung(at)yahoo(dot)com<http://mc/compose?to=chestercyoung(at)yahoo(dot)com>
> >
> > Cc: pgsql-sql(at)postgresql(dot)org<http://mc/compose?to=pgsql-sql(at)postgresql(dot)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(at)yahoo(dot)com<http://mc/compose?to=chestercyoung(at)yahoo(dot)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(at)postgresql(dot)org<http://mc/compose?to=pgsql-sql(at)postgresql(dot)org>
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
>