Re: Prepared statements performance

Hi,

Unfortunately these are experimental conditions. The conditions surrounding
the intended application are such that my two options are prepared
statements or many inserts. I put the multi-value inserts in as I was
curious as to why prepared statements would be slower given they only plan
the query once (as also does the multi-value insert, I assume).

It turns out though that the results are skewed by using pgAdmin. Executing
my scripts from the command line gives much more appropriate results.

Thanks,
Dan.

On Thu, May 10, 2012 at 10:16 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>wrote:

> Hello
>
> 2012/5/10 Daniel McGreal <daniel(dot)mcgreal(at)redbite(dot)com>:
> > Hi again,
> >
> > I did a follow up test using 'multi-value' inserts which is three times
> > faster than multiple inserts thusly:
> >
>
> if you need speed, use a COPY statement - it should be 10x faster than
> INSERTS
>
> Pavel
>
> >
> > TRUNCATE test;
> > BEGIN;
> > INSERT INTO test (one, two, three, four, five) VALUES ('2011-01-01',
> true,
> > 'three', 4, 5.5)
> >
> > ,('2011-01-01', true, 'three', 4, 5.5)
> > -- 99'998 more , ('2011-01-01', true, 'three', 4, 5.5) ...;
> > END;
> >
> > This is the kind of speed increase I was hoping for when using prepared
> > statements (which makes sense because in this multi-value insert the
> query
> > is only being planned once?).
> >
> > Thanks,
> > Dan.
> > P.S. Mac OS X 10.7.3 using PostgreSQL 9.1.2.
> >
> >
> >> On Thu, May 10, 2012 at 9:25 AM, Daniel McGreal
> >> <daniel(dot)mcgreal(at)redbite(dot)com> wrote:
> >>>
> >>> Hi!
> >>>
> >>> My reading to date suggests that prepared statements should be faster
> to
> >>> execute than issuing the same statement multiple times. However,
> issuing
> >>> 100'000 INSERTs turned out to be more than ten times faster than
> executing
> >>> the same prepared statement 100'000 times when executed via pgAdmin.
> The
> >>> table was:
> >>>
> >>> CREATE TABLE test
> >>> (
> >>> one date,
> >>> two boolean,
> >>> three character varying,
> >>> four integer,
> >>> five numeric(18,5),
> >>> id serial NOT NULL --note the index here
> >>> )
> >>>
> >>> The prepared statement test lasting ~160 seconds was:
> >>>
> >>> TRUNCATE test;
> >>>
> >>> BEGIN;
> >>> PREPARE foo(date, boolean, varchar, integer, numeric(18,5)) AS
> >>> INSERT INTO test (one, two, three, four, five) VALUES ($1, $2, $3,
> >>> $4, $5);
> >>>
> >>> EXECUTE foo('2011-01-01', true, 'three', 4, 5.5);
> >>> -- 99'999 more executes...
> >>> END;
> >>>
> >>> The insertion test lasting ~12 seconds was:
> >>>
> >>> TRUNCATE test;
> >>>
> >>> BEGIN;
> >>> INSERT INTO test (one, two, three, four, five) VALUES ('2011-01-01',
> >>> true, 'three', 4, 5.5);
> >>> -- 99'999 more inserts...
> >>> END;
> >>>
> >>> I'm assuming then that I've done something mistakenly.
> >>>
> >>> Many thanks,
> >>> Dan.
> >>
> >>
> >
>