Hello 2011/6/27 chester c young <chestercyo...@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.steh...@gmail.com>* wrote: > > > From: Pavel Stehule <pavel.steh...@gmail.com> > Subject: Re: [SQL] best performance for simple dml > To: "chester c young" <chestercyo...@yahoo.com> > Cc: pgsql-sql@postgresql.org > Date: Monday, June 27, 2011, 1:05 AM > > 2011/6/27 chester c young > <chestercyo...@yahoo.com<http://mc/compose?to=chestercyo...@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.steh...@gmail.com<http://mc/compose?to=pavel.steh...@gmail.com>> > wrote: > > > > From: Pavel Stehule > > <pavel.steh...@gmail.com<http://mc/compose?to=pavel.steh...@gmail.com> > > > > Subject: Re: [SQL] best performance for simple dml > > To: "chester c young" > > <chestercyo...@yahoo.com<http://mc/compose?to=chestercyo...@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 > > <chestercyo...@yahoo.com<http://mc/compose?to=chestercyo...@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 > >
