The Oracle Data Provider for .NET (ODP.NET), by default, operates in what is known as "auto-commit" mode. What this means is that when a non-query statement such as an insert, an update, or a delete has been processed, a "hidden commit" is automatically and implicitly performed in the database on your behalf. When processing a single row insert statement, for example, this may well be acceptable. However, what if, rather than that single row, you needed to insert several thousand or several hundred thousand (or more!) rows into a table? In pseudo-code, using the default behavior, this would look something like the following:
Begin loop
Insert row
Commit
If more rows continue loop
End loop
The key point in this pseudo-code is that the implicit "commit" is inside the loop and, thus, will be transparently invoked as many times as the loop executes.
In contrast to the "commit each pass through the loop" approach, a guiding practice in regard to Oracle database transactions is to commit when the transaction is complete. In this example, the transaction is not deemed to be complete until all rows have been inserted into the table. In pseudo-code, again, this would resemble:
Begin loop
Insert row
If more rows continue loop
End loop
Commit
Here the "commit" has been moved outside the main processing loop. While the difference may seem subtle and/or trivial, there can be a large performance difference between the two approaches. The TransactionDemo solution illustrates both of these approaches. The demo code contains two "test" methods: one method inserts 5,000 rows into a table ("demo_table") using a commit after each insert (simply employing the default behavior) while the second method explicitly creates a transaction and only commits that transaction once all rows have been inserted into the table.
To explicitly create, and thus begin, a transaction, you use the "BeginTransaction" method exposed by an OracleConnection object (where "con" is an OracleConnection object):
OracleTransaction txn = con.BeginTransaction();
When you are ready to commit the transaction, you invoke the "Commit" method on the transaction object:
txn.Commit();
Note: You may also invoke the "Rollback" method if necessary.
The following table illustrates the results of executing the demo code five times:
Test/Commit Default Explicit
1 11.4688 1.2188
2 11.1406 1.3906
3 11.6406 1.2031
4 11.2500 1.2031
5 12.1094 1.2031
Min 11.1406 1.2031
Avg 11.5219 1.2437
Max 12.1094 1.3906
Table 1: The results, in seconds, of TransactionDemo over 5 executions
As you can see from the results, committing at the end of the transaction, rather than after each row is inserted, allows for code that executes much faster. The average time for the method that commits after each row is inserted is just over 11.5 seconds, while the average time for the method that performs a single commit at the end of the transaction is just over 1.2 seconds - almost a factor of 10 difference!
In order to run the sample code below unchanged, you need to first perform the following tasks:
- Create a "tns alias" called "orademo" in your tnsnames.ora file to connect to the appropriate database in your environment.
- Create a user called "orademo" in the database.
- Grant the "create session" and "create table" privileges to the "orademo" user.
- Create a table called "demo_table" in the "orademo" user's schema.
Create the ORADEMO User
As a DBA user, perform the following tasks in your database to create the user and grant privileges (adjust as needed for your environment):
create user orademo identified by demo
default tablespace users
temporary tablespace temp
quota unlimited on users;
grant create session to orademo;
grant create table to orademo;
Create the Demo Table
Once you have created the "orademo" user, connect to the database as "orademo" and execute the following to create the table:
create table demo_table
(
c1 varchar2(32),
c2 varchar2(32),
c3 varchar2(32)
);
The Sample C# Code:
using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
namespace TransactionDemo
{
class Program
{
// be sure to adjust the connection string for your environment
private const string constr = "User Id=orademo; Password=demo; Data Source=orademo; Enlist=false; Pooling=false";
static void Main(string[] args)
{
Console.WriteLine("Beginning TransactionDemo...\n");
// create and open connection
OracleConnection con = new OracleConnection(constr);
con.Open();
// start with empty table
delete_table(con);
// perform insert with commit after each row
multi_commit(con);
// empty the table
delete_table(con);
// perform insert with a single commit at end
single_commit(con);
// clean up
con.Dispose();
// prevent command window from closing automatically when running from visual studio
Console.WriteLine();
Console.WriteLine("ENTER to continue...");
Console.ReadLine();
}
static void multi_commit(OracleConnection con)
{
Console.WriteLine("Starting multi-commit test...\n");
// used to track execution duration
DateTime timeStart;
DateTime timeEnd;
double totalSeconds;
// create command to insert rows into demo table
string sql = "insert into demo_table (c1, c2, c3) values ('This is column 1', 'This is column 2', 'This is column 3')";
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = sql;
// capture test start time
timeStart = DateTime.Now;
// an implicit commit will occur for each ExecutionNonQuery call
for (int i = 0; i < 5000; i++)
{
cmd.ExecuteNonQuery();
}
// capture test end time
timeEnd = DateTime.Now;
// calculate total seconds for this test
totalSeconds = timeEnd.Subtract(timeStart).TotalSeconds;
// display time used for test
Console.WriteLine(" Multi commit: {0,7:##.0000} total seconds.\n", totalSeconds);
// clean up
cmd.Dispose();
}
static void single_commit(OracleConnection con)
{
Console.WriteLine("Starting single-commit test...\n");
// used to track execution duration
DateTime timeStart;
DateTime timeEnd;
double totalSeconds;
// create command to insert rows into demo table
string sql = "insert into demo_table (c1, c2, c3) values ('This is column 1', 'This is column 2', 'This is column 3')";
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = sql;
// capture test start time
timeStart = DateTime.Now;
// create an explicit transaction
OracleTransaction txn = con.BeginTransaction();
// explicit transaction started, therefore no implicit commit
// for each ExecuteNonQuery call
for (int i = 0; i < 5000; i++)
{
cmd.ExecuteNonQuery();
}
// commit the transaction
txn.Commit();
// capture test end time
timeEnd = DateTime.Now;
// calculate total seconds for this test
totalSeconds = timeEnd.Subtract(timeStart).TotalSeconds;
// display time used for test
Console.WriteLine("Single commit: {0,7:##.0000} total seconds.\n", totalSeconds);
// clean up
cmd.Dispose();
}
static void delete_table(OracleConnection con)
{
Console.WriteLine("Deleting from demo_table...\n");
// delete all rows from the table
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "delete from demo_table";
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
}
Note: This content is based on my HotSos Symposium 2007 paper.
