email article printer friendly get the code more resources

Optimize Oracle Data Access
Use anonymous blocks and multiple parameter sets with the ODP.NET data provider to reduce round trips between client and database.
by Bob Beauchemin

November 2003 Issue

Technology Toolbox: VB.NET, C#, Oracle

You have two basic ways to improve data-access code performance in your database applications. The most straightforward way is to optimize the SQL statements that execute on the database server. A more subtle way, from a database client programming perspective, is to decrease the number of round trips from the client to the database server and do more database work in each round trip. I'll explain two techniques you can use to achieve this goal.

One way to decrease round trips is to use batches of statements known in Oracle as anonymous blocks (see Figure 1). All sets of PL/SQL statements are called blocks. You can catalog blocks to the database by name (for example, stored procedures or user-defined functions), or execute them directly from a client program without cataloging them. These directly executed blocks have no name in the database; that is, they're anonymous.

Another way to decrease round trips is to use parameterized queries or stored procedures with multiple sets of parameters (see Figure 2). Take this simple SQL statement:

SELECT * from emp 
   WHERE deptno = :deptno 
      AND sal > :sal;

You can execute the preceding statement with one instance of the parameters—for example, :deptno 10 and :sal 50000. However, you can also pass multiple instances of the parameters to the database—say, (10,50000), (20,40000), and (40,60000). Passing multiple parameter sets causes the same query to execute three times on the database server in a single round trip. Both the ODP.NET data provider and the DataDirect Technologies data provider for Oracle support multiple parameter sets; ODP.NET, DataDirect's provider, and Microsoft OracleClient support anonymous blocks. I'll show you how to use anonymous blocks and multiple parameter sets with ODP.NET version 1.0 in C#.

Suppose you want to add a new department and its employees in as few round trips as possible. This is easy to test because the Oracle test schema (named scott, after one of Oracle's first employees) is set up already. Another example of the same problem is adding an order and a number of line items. The "add 1-n items" problem pops up often in use cases for relational databases. You can solve this problem in two round trips by using multiple parameter sets to add the employees after you add the department. Suppose you have two packaged stored procedures: adddept and addemp (see Listing 1). You start using multiple parameter sets with ODP.NET by defining and adding the parameters to the collection as though you were using a "normal" OracleParameterCollection (see Listing 2).

The trick to using multiple parameter sets is to set the Parameter.Value for each parameter to a .NET array of the appropriate data type. Create an array for each parameter value instead of using a single parameter value for each parameter. The array's size is the number of employees you want to add:

Decimal[] empno = new Decimal[3];
String[] ename = new String[3];
// set the values into the arrays

Then, set each parameter value to the array rather than setting it to the single values:

insert_emp_cmd.Parameters[0].Value = 
   empno;
insert_emp_cmd.Parameters[1].Value = 
   ename;