I have surprising performance results running a simple Java program
which inserts 1000 rows into the following simple table:
create table TestTable (id int not null, comments varchar(255) not
null);
With group commit, the test runs in 3 seconds (one commit after the
loop)
With auto commit, the test runs in 15 seconds (default JDBC setting)
I ran the same test with Oracle and MSSQL. Although we cannot compare
the performance results (different machines and setup), Oracle and
MSSQL do not show so much difference between group-commit and
auto-commit.
Group-Commit Auto-Commit
Oracle 1704 ms 2578 ms
MSSQL 528 ms 1311 ms
DB2 3656 ms 15443 ms
The table has no index, no primary key. The test drops the table when
it starts, creates the table and loop on insert, then prints the
average time spent on inserts. The server is a DB2 UDB v.8.1 on
Solaris, the JDBC client on Windows using the JDBC drivers shipped
with DB2 UDB.
So far, I tentatively tried to tune my DB2 instance (such as using DMS
instead of SMS, changing the log MINCOMMIT value) but without any
improvement.
I must be doing something just plainly wrong with DB2, but because my
test program is so simple, I don't see what I would have missed.
Any help? Thanks much.
JC