On 02/23/2012 01:07 PM, Alessandro Gagliardi wrote:
> The second one (a bunch of insert statements within a single
> connection). As I mentioned above, I was going to try the temp table
> thing, but that wasn't fast enough. COPY might be my next attempt.
insert into...;
insert into...;
insert into...;
... is really (ignoring statement preparation time):
begin;
insert into...;
commit;
begin;
insert into...;
commit;
begin;
insert into...;
commit;
It's possible that you might get a nice boost by wrapping the inserts
into a transaction:
begin;
insert into...;
insert into...;
insert into...;
...
commit;
This only requires all that disk-intensive stuff that protects your data
once at the end instead of 1000 times for you batch of 1000.
COPY is even better. I just ran a quick test by restoring a table on my
desktop hacking db (untuned, few years old PC, single SATA disk, modest
RAM and lots of resource competition). The 22+ million rows restored in
282 seconds which is a rate somewhat north of 78,000 records/second or
about 0.13ms/record.
You may want to eliminate that trigger, which only seems to exist to
silence errors from uniqueness violations, and copy the incoming data
into a temp table then move the data with a variant of:
INSERT INTO main_table (SELECT ... FROM incoming_table WHERE NOT EXISTS
((SELECT 1 from main_table WHERE ...))
Cheers,
Steve