2010/10/28 Divakar Singh <dpsmails(at)yahoo(dot)com>:
> So another question pops up: What method in PostgreSQL does the stored proc
> use when I issue multiple insert (for loop for 100 thousand records) in the
> stored proc?
nothing special - but it run as inprocess inside server backend. The
are no data casting, there are no overhead from communication, there
are no overhead from content switch.
> It takes half the time compared to the consecutive "insert" using libpq.
> In the backend, does it use COPY or prepared statement? or something else?
> Best Regards,
> From: Alex Hunsaker <badalex(at)gmail(dot)com>
> To: Divakar Singh <dpsmails(at)yahoo(dot)com>
> Cc: Steve Singer <ssinger(at)ca(dot)afilias(dot)info>; jd(at)commandprompt(dot)com;
> Sent: Thu, October 28, 2010 1:15:06 AM
> Subject: Re: [PERFORM] Postgres insert performance and storage requirement
> compared to Oracle
> On Wed, Oct 27, 2010 at 08:00, Divakar Singh <dpsmails(at)yahoo(dot)com> wrote:
>> I am attaching my code below.
>> Is any optimization possible in this?
>> Do prepared statements help in cutting down the insert time to half for
>> kind of inserts?
> In half? not for me. Optimization possible? Sure, using the code you
> pasted (time ./a.out 100000 <method>):
> PQexec: 41s
> PQexecPrepared: 36s
> 1 insert statement: 7s
> COPY: 1s
> psql: 256ms
> Basically the above echoes the suggestions of others, use COPY if you can.
> Find the source for the above attached. Its just a very quick
> modified version of what you posted. [ disclaimer the additions I
> added are almost certainly missing some required error checking... ]
> [ psql is fast because the insert is really dumb: insert into aaaa (a,
> b, c, d, e, f, g, h, j, k, l, m, n, p) select 1, 'asdf', 'asdf',
> 'asdf', 'asdf', 'asdf', 'asdf', 'asdf', 'asdf', 'asdf', 'asdf',
> 'asdf', 'asdf', 'asdf' from generate_series(1, 100000); ]
In response to
pgsql-performance by date
|Next:||From: Trenta sis||Date: 2010-10-28 08:16:20|
|Subject: Massive update, memory usage|
|Previous:||From: Alex Hunsaker||Date: 2010-10-28 04:23:44|
|Subject: Re: Postgres insert performance and storage requirement
compared to Oracle|