Hello 2010/10/28 Divakar Singh <dpsmails@xxxxxxxxx>: > 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. Regards Pavel Stehule > 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, > Divakar > > ________________________________ > From: Alex Hunsaker <badalex@xxxxxxxxx> > To: Divakar Singh <dpsmails@xxxxxxxxx> > Cc: Steve Singer <ssinger@xxxxxxxxxxxxxxx>; jd@xxxxxxxxxxxxxxxxx; > pgsql-performance@xxxxxxxxxxxxxx > Sent: Thu, October 28, 2010 1:15:06 AM > Subject: Re: Postgres insert performance and storage requirement > compared to Oracle > > On Wed, Oct 27, 2010 at 08:00, Divakar Singh <dpsmails@xxxxxxxxx> 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 >> this >> 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); ] > > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
