From: chester c young <chestercyoung@yahoo.com>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: best performance for simple dml
Date: Mon, 27 Jun 2011 00:49:49 -0700 (PDT)
Message-ID: <2547.71122.qm@web161421.mail.bf1.yahoo.com> (raw)
In-Reply-To: <BANLkTik90qDGv-9CcHtV6i=abynywUkbNA@mail.gmail.com>
very nice pointers. thank you very much!
--- 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>
>
> 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> 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, 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>
>
> 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
>
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: chestercyoung@yahoo.com, pavel.stehule@gmail.com
Subject: Re: best performance for simple dml
In-Reply-To: <2547.71122.qm@web161421.mail.bf1.yahoo.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