Re: Prepared statements performance

Hi again,

I did a follow up test using 'multi-value' inserts which is three times
faster than multiple inserts thusly:

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.
>>
>
>