Put Your Arrays in a Bind

Use array binding to reduce network round-trips and increase performance.

September/October 2009

One of the best things about the combination of Oracle Database and Oracle Data Provider for .NET (ODP.NET) is that multiple methods often are available to accomplish the same task, so you have the flexibility to make choices that suit your environment and needs. For example, suppose you have a requirement to first collect some information from your application’s users and then insert that information into a table in Oracle Database. Seems simple enough, right? But what if the data collected corresponds to multiple rows in the table rather than a single row?

One option is for you to insert these multiple rows into the table through a loop in your program. This loop iterates over the data to be inserted and does what is known as a single-row insert , because the application sends one single row of data to the database at a time. This works, but is it your best-performing option?

When the client and the database need to communicate with one another (through underlying Oracle software), an event called a round-trip occurs. You might think of this as the client sending a message to the database and the database sending a response back to the client. In most cases, this communication occurs over the network, because the client usually isn’t located on the same machine as the database. Each round-trip consumes network and CPU resources on both the client and the database server.

You have another option for inserting multiple rows of data into the table—an option that reduces the number of round-trips and improves application performance, database performance, and network resource use. Rather than having the application send a single row of data to the database at a time, it can use array binding to send the data in batches of rows. By sending the data in batches, you reduce the number of round-trips necessary, because you tell the database, “Insert X number of rows” in fewer messages than if you tell the database, “Insert this row, now insert this row, now insert this row” and so on. If it sounds complicated, don’t worry. ODP.NET makes this task easy!

The array binding feature is available with all supported versions of ODP .NET, Oracle Database, and Microsoft Visual Studio. However, to best use the sample application download for this column, I recommend

• Oracle Data Access Components 11.1.0.6 (or higher)
• Microsoft Visual Studio 2008 (any edition, including Express Edition)
• Access to Oracle Database (any version, including Oracle Database 10g Express Edition) with the HR sample schema installed

The sample application for this column, available for download, uses array binding to insert three new rows into the jobs table in the HR schema as a single batch of data sent from the client.

The following are the general steps for using array binding and examples from the sample application:

Create arrays. Create arrays in your host language, using .NET types (such as string or int in the C# language) that contain the values that will be used in the SQL code.

For example, to create a job_id_vals array of type string, the sample application uses the following:

string[] job_id_vals = 
new string[3] { "IT_DBA",
                      "IT_MAN",
                      "IT_VP" };

Create parameter objects. The parameters are instances of the OracleParameter class. Set the OracleDbType property to the appropriate value (such as OracleDbType.Varchar2 or OracleDbType.Int32), and assign the .NET arrays you create to the parameter object’s value property.

For example, the sample application creates a p_job_id parameter object, sets the OracleDbType property to OracleDbType.Varchar2, and assigns the job_id_vals array to the p_job_id.Value property:

OracleParameter p_job_id = 
new OracleParameter();
p_job_id.OracleDbType = 
OracleDbType.Varchar2;
p_job_id.Value = job_id_vals;

Add parameters to the command object. With the arrays created and attached to parameter objects, now add the parameters to an OracleCommand object. To do this, invoke the Add method on the Parameters collection. However, because the parameters are arrays rather than scalar values, also set the ArrayBindCount property to the number of elements that is in the arrays.

For example, the sample application sets the ArrayBindCount property for the job_id_vals array by retrieving the number of elements in the array (exposed by the Length property) and adds the p_job_id parameter object to the command object:

cmd.ArrayBindCount = job_id_vals.Length;
cmd.Parameters.Add(p_job_id);

Execute. With the arrays created and attached to parameter objects and the parameters added to an OracleCommand object, you are ready to use array binding in your application. To do so, invoke the ExecuteNonQuery method on the OracleCommand object, just as you would if you were not using arrays:

cmd.ExecuteNonQuery();

This particular technique applies only to INSERT, UPDATE, and DELETE operations. It is not designed to work with SELECT operations, which is why the ExecuteNonQuery method is invoked.

To appreciate the steps involved in performing this task, step through the sample application code in Visual Studio to see how the pieces fit together. This code doesn’t force multiple round-trips by sending the data in single rows, which is apparent because the ExecuteNonQuery method is invoked a single time to process all the rows:

// create command and set properties
OracleCommand cmd = 
con.CreateCommand();
// the sql text used to insert the 
// rows in the arrays and this 
// necessarily uses bind variables
cmd.CommandText =  "insert into 
  jobs (job_id, " +
        "job_title, " +
        "min_salary, " +
        "max_salary) " +
        "values (:1, :2, :3, :4)";
// set the number of elements 
// in the arrays and all three 
// arrays are the same size
cmd.ArrayBindCount = job_id_vals.Length;
// add parameters to collection
cmd.Parameters.Add(p_job_id);
cmd.Parameters.Add(p_job_title);
cmd.Parameters.Add(p_min_salary);
cmd.Parameters.Add(p_max_salary);
// perform the array insert in 
// a single call
cmd.ExecuteNonQuery();

Contrast this with a single-row INSERT method that would need to be invoked three times, resulting in at least three client-to-server-to-client round-trips to perform the same task. Also note that code that uses array binding frequently can be more compact and easier to maintain than code that uses a single-row approach.

After the new rows have been inserted, the sample application deletes them from the table to clean up after itself. Note that DELETE is also implemented as an array operation:

// delete the new jobs using an array
cmd.CommandText = "delete from jobs " + 
  "where job_id = :1";
// clear parameters from 
// existing collection
cmd.Parameters.Clear();
// add the p_job_id array parameter
// other properties do not need 
// to be adjusted since they are 
// still correct
cmd.Parameters.Add(p_job_id);
// execute the delete for each job_id
cmd.ExecuteNonQuery();

Once you are comfortable with the actions performed in the sample application, try extending the application to perform updates. After that, a good next step would be to implement error handling in your code. ODP.NET will return a separate error for each row in the array binding that generates a database error.

To make error handling easier, review the OracleErrorCollection, OracleParameter, and OracleCommand topics in the ODP.NET documentation. Using OracleErrorCollection, you can pinpoint which data in an array resulted in an error—which can be especially helpful if you are working with larger arrays. With just a few simple additional steps, you can extend the sample application to perform any needed actions in your environment.

To contrast this array binding technique with PL/SQL associative arrays, see “Using PL/SQL Associative Arrays” (Oracle Magazine, January/February 2007). One difference between these two array approaches is the supported parameter types. The array binding technique illustrated here supports more types—including XMLType, LOB, and TimeStamp—than PL/SQL associative arrays.

READ more ODP.NET

DOWNLOAD
sample application code for this column

LEARN more about
bind variables
PL/SQL associative arrays

ODP.NET
.NET Developer Center
Oracle Data Provider for .NET Developer’s Guide
Oracle Database 2 Day + .NET Developer’s Guide

Photography by Nick van den Berg, Unsplash