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.