473,499 Members | 1,725 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

DB2 Commit vs. autocommit performance problems

JC
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
Nov 12 '05 #1
0 5687

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
5286
by: jy2003 | last post by:
Below is what I got by using MySQL's --log startup option. The original Java(with JDBC driver) program that creates the queries at runtime was coded by other people, and unfortunately, they are not...
0
1721
by: Colm Connolly | last post by:
Hi all, I'm working with tables stored by the InnoDB engine and would like to be able to commit only if there are no errors generated by a group of statements like this. /* -*- sql -*- */...
0
5380
by: Fan Ruo Xin | last post by:
Nothing got wrong with DB2 COMMIT, db2 cfg, ... Even you did an "insert some_id+1 ..." immediately after you did "select max(some_id) from .... " in session1. This will not block the operations...
8
11675
by: Martin Staael | last post by:
When doing a UPDATE on a very large table it can take quite a long time due to the commit/rollback option. Is there any way on the client side to disable the commit/rollback option so that UPDATE...
6
2708
by: vijai kumar via DBMonster.com | last post by:
Hi, I am using UDB v 8.1 on win2000. I have setup the ODBC datasources. My application is connecting to the database and performing DML activities. But I am getting the following error ...
0
1097
by: Vijai Kumar | last post by:
Hi, I am using UDB v 8.1 on win2000. I have setup the ODBC datasources. My application is connecting to the database and performing DML activities. But I am getting the following error ...
7
8733
by: fyi85 | last post by:
I have 8.1.5 on Windows 2003, when I do from CLP with auto commit off: db2 update table set column=something and then db2 terminate and then db2 connect to db db2 select updated column from...
2
2207
by: Ryan | last post by:
I've re-written a stored procedure and when I post the following code into the existing SP in EM, is saves OK. However, when I re-edit the SP, the last line 'Commit Transaction' has been removed. ...
0
604
by: Jim Kennedy | last post by:
ALL DDL does a commit. Hence Drop Table movies; issues a commit. True you don't issue a commit and the driver does not issue a commit, but the server does for all DDL. That is probably where...
0
7128
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
7006
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
7169
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
1
6892
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
5467
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
1
4917
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...
0
3096
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The...
0
3088
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
0
294
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.