I presume you are using a relatively new jdbc driver. Make sure you have added prepareThreshold=1 to the url to that it will use a named server side prepared statement

You could also use your mod 100 code block to implement batch processing of the inserts.

see addBatch, in  jdbc specs


On 23-Sep-05, at 2:49 AM, Joost Kraaijeveld wrote:


I must convert an old table into a new table. The conversion goes at ~
100 records per second. Given the fact that I must convert 40 million
records, it takes too much time: more hours than the 48 hour weekend I
have for the conversion;-).

The tables are rather simple: both tables only have a primary key
constraint (of type text) and no other indexes. I only copy 3 columns. I
use Java for the conversion. For the exact code see below.

During the conversion my processor load is almost non existant. The
harddisk throughput is ~ 6 megabyte/second max (measured with iostat).

My platform is Debian Sarge AMD64. My hardware is a Tyan Thunder K8W
2885 motherboard, 2 Opteron 248 processors, 2 GB RAM, a SATA bootdisk
with / and swap, and a 3Ware 9500S-8 RAID-5 controller with 5 attached
SATA disks with /home and /var. /var contains *all* PostgreSQL log and
database files (default Debian installation).

Output of hdparm -Tt /dev/sdb (sdb is the RAID opartition)

 Timing cached reads:   1696 MB in  2.00 seconds = 846.86 MB/sec
 Timing buffered disk reads:  246 MB in  3.01 seconds =  81.79 MB/sec

I want to determine the cause of my performance problem (if it is one).

1. Is this a performance I can expect?
2. If not, how can I determine the cause?
3. Can I anyhow improve the performance without replacing my hardware,
e.g. by tweaking the software?
4. Is there a Linux (Debian) tool that I can use to benchmark write


Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416

The Java code I use for the conversion  :

//////////////// ....
ResultSet resultSet = selectStatement.executeQuery(
"select ordernummer, orderdatum, klantnummer from odbc.orders order by


PreparedStatement ordersInsertStatement =
connection.prepareStatement("insert into prototype.orders
(objectid,ordernumber,orderdate,customernumber) values (?,?,?,?)");

while( )

if( (++record % 100) == 0){
    System.err.println( "handling record: " + record);

// the next line can do > 1.000.000 objectId/sec
String orderObjectId = ObjectIdGenerator.newObjectId();




---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Reply via email to