HOWTO : Understand Why JDBC Autocommit OFF Performs Slower Than Autocommit ON

(Doc ID 746868.1)

Last updated on SEPTEMBER 22, 2016

Applies to:

TimesTen Data Server - Version 7.0.1.0.0 to 7.0.5.0.0 [Release 7.0]
Information in this document applies to any platform.


Symptoms

When inserting many rows (typically 10,000 and more) in a table using a JDBC program, if we use setAutoCommit(false) of Connection object, it takes more time than the time it takes for setAutoCommit(true). This can be verified with a simple JDBC program.

In a test environment when we set autocommit ON by the Java statement
ConnectionObject.setAutoCommit(true);
Insertion of rows took approximately 3 minutes 39 seconds.

When we set autocommit OFF by the Java statement
ConnectionObject.setAutoCommit(false);
and if we commit at the end of the program, insertion of same number of rows took approximately 25 minutes 48 seconds.

This can be observed only when we enable Logging. This can not be observed when we disable Logging.

Note:

Logging=0 is deprecated in TimesTen 11x

connect "dsn=mk112150db;logging=0";
20000: Use of a deprecated feature: Logging=0

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms