Combining the "insert" statements in a big concatenated string
joined by semicolons - rather than sending each individually
can drastically speed up your inserts; making them much closer
to the speed of copy.
For example, instead of sending them separately, it's much faster
to send a single string like this
"insert into tbl (c1,c2) values (v1,v2);insert into tbl (c1,c2) values (v3,v4);..."
presumably due to the round-trip packets sending each insert takes.
Brian Hurt wrote:
> Inserts, 1,000 per transaction ~5,400 inserts/second
> Copy, 1,000 element blocks ~20,000 inserts/second
When I last measured it it was about a factor of 4 speedup
(3 seconds vs 0.7 seconds) by concatenating the inserts with
sample code shown her .
If the same ratio holds for your test case, these concatenated
inserts would be almost the exact same speed as a copy.
In response to
pgsql-performance by date
|Next:||From: satishchandra999||Date: 2006-06-29 17:00:35|
|Subject: Calling a SP from Curosor loop|
|Previous:||From: Jim C. Nasby||Date: 2006-06-28 15:08:15|
|Subject: Re: Large index scan perfomance and indexCorrelation (PG 8.1.4 Win32)|