Introduction
In a typical multi-tier application, one of the biggest performance bottlenecks is the overhead of making round-trips to
the database. Minimizing these round-trips is often the first area you should look at during performance tuning. Fortunately,
the Oracle Data Provider for .NET (ODP.NET) makes it fairly easy to do this by providing several built-in methods to write
and read data in bulk.
To run the code samples in this article, you will need to have:
- ODP.NET 2.0 (download from here).
- A table named "BULK_TEST".
Here's the script that creates the table BULK_TEST:
Bulk Inserts Using Array Binding
The Array Binding feature in ODP.NET allows you to insert multiple records in one database call. To use Array Binding, you
simply set OracleCommand.ArrayBindCount to the number of records to be inserted, and pass arrays of values as
parameters instead of single values:
As you can see, the code does not look that much different from doing a regular single-record insert. However,
the performance improvement is quite drastic, depending on the number of records involved. The more records you have to
insert, the bigger the performance gain. On my development PC, inserting 1,000 records using Array Binding is 90 times faster
than inserting the records one at a time. Yes, you read that right: 90 times faster! Your results will vary, depending on the
record size and network speed/bandwidth to the database server.
A bit of investigative work reveals that the SQL is considered to be "executed" multiple times on the server side. The
evidence comes from V$SQL (look at the EXECUTIONS column). However, from the .NET point of view, everything was
done in one call.
Bulk Inserts Using PL/SQL Associative Arrays
PL/SQL Associative Arrays (formerly PL/SQL Index-By Tables) allow .NET code to pass arrays as parameters to PL/SQL code
(stored procedure or anonymous PL/SQL blocks). Once the arrays are in PL/SQL, you are free to use them in whichever way you
wish, including turning around and inserting them into a table using the "forall" bulk bind syntax.
Why would you want to insert bulk records using PL/SQL Associative Arrays instead of the simple syntax of Array Binding?
Here are a few possible reasons:
- You need to perform additional work in PL/SQL, in addition to the bulk insert.
- The application login does not have permission to perform the work, but you can grant the necessary privilege to
a stored procedure.
The major drawback with using Associative Arrays is that you have to write PL/SQL code. I have nothing against
PL/SQL, but it's not part of the skill set of the typical .NET developer. To most .NET developers, PL/SQL will be harder to
write and maintain, so you will have to weigh this drawback against the potential gain in performance.
In the following example, we use PL/SQL Associative Arrays to insert 1,000 records, and returning a Ref Cursor at the end.
As you can see, there's quite a bit of more code to write:
Ref Cursors
ODP.NET Ref Cursors are objects that point to Oracle server-side cursors (or result sets). The important thing to .NET
developers is that a Ref Cursor can be converted to the familiar OracleDataReader. With Ref Cursors, the logic to open result
sets can be written entirely in PL/SQL and the results returned to .NET via Ref Cursors.
Why would you want to use Ref Cursors, instead of just doing an ExecuteReader with a SELECT statement? Here
are some possible reasons:
- You need to perform additional work in PL/SQL before returning the result set(s).
- The user does not have direct access to the table(s) in question.
Here is a real world example. Say you need to update a record in the Orders table and insert a new record into the
OrdersAudit table at the same time. Instead of executing two database calls, you can wrap everything into an anonymous PL/SQL
block and make one database call.
See the previous section for code example using a Ref Cursor.
Controlling FetchSize
Controlling the FetchSize property is another method to minimize server round-trips. When you read data from
the server using the OracleDataReader object, ODP.NET retrieves the data for you in chunks behind the scene. By default, a
64K chunk of data is retrieved each time. However, you can change the chunk size by setting the FetchSize
property. By increasing FetchSize, you will lower the number of data retrieval round-trips and increase the
overall retrieval operation.
It's typical to set the FetchSize property is to the RowSize (of the OracleCommand object)
multiplied by the number of records expected:
When working with a Ref Cursor, the OracleCommand.RowSize property is always zero. You either have
to calculate the row size at design time or use reflection at run-time to determine the RowSize by looking at the instance
variable m_rowSize on the OracleDataReader object:
On my development PC, when reading 10,000 records from BULK_TEST, setting FetchSize =
RowSize * <total number of records>
improves the total elapsed time by a factor of two over leaving FetchSize at the default value (985 vs. 407
milliseconds).
Notes
For clarity purposes, the example code in this article does not use the "using" pattern for IDisposable objects. It's
recommended that you always use "using" with IDisposable objects such as OracleConnection or OracleCommand.
Summary
In this article, we looked at how various bulk operations in ODP.NET 2.0 can help you improve the performance of your
ODP.NET application. Being familiar with these techniques can help you plan, design and implement applications that meet
performance goals.
References
Top Articles in this category
SqlBulkCopy in ADO.NET 2.0
SqlBulkCopy is a new feature in ADO.NET 2.0 that lets you to perform copy operation for a large amount of data between a source data store and a destination data table. In this article Keyvan introduces this new feature in .NET 2.0 and shows a sample application in action.
Managing Transactions using TransactionScope
A transaction is a unit of work. You use transactions to ensure the
consistency and integrity of a database. If a transaction is successful, all of
the data modifications performed during the transaction are committed and made
permanent. If an error occurs during a transaction, you can roll back the
transaction to undo the data modifications that occurred during the transaction.
Two-way data binding in 3-Tier web application
In this article I would like to examine two-way data-binding in 3-tier web application and how using XLib library can substantially decrease the amount of data-binding code without compromising the amount of control you have over it. I start by describing the ways data flow has been handled in 3-tier applications and how XLib improves upon it.
Mapping DataSet to XML and backwards
This article illustrates the translation to and from an ADO .NET DataSet and XML.
Managing Entity Framework ObjectContext lifespan and scope in n-layered ASP.NET applications
Jordan van Gogh describes a way to manage ObjectContext lifespan and scope in the Business Logic Layer (BLL) of a small to medium sized ASP.NET web application.
|
|
Please login to rate or to leave a comment.