Very slow performance??

[Vincent Chow]

Hi, I have this strange performance benchmark. I have a table with 4 columns which 1 of them is auto_incremented, while 2 of the remaining 3 columns are uniquely indexed.

There are about 2K records to insert. When I used prepared statement

connection.createPreparedStatement( "INSERT INTO xxxx VALUES ( null, ?, ?, ? )" );

It takes about 1 minute to insert all of them. But when I combine all the records using single-lined sql statement like this;

StringBuffer buffer = new StringBuffer();
for ( int i = 0; i < #of records; i++ )
buffer.append( x, x, x, x );
statement = connection.createStatement();
statement.executeUpdate( buffer.toString() );

It took less than 10ms to do the insertion.

Why was there such a huge performance difference? Was I doing something wrong?

Any help is greatly appreciated.