Reduce Database Server Round-Trips
Batching Oracle SQL statements together will improve your application's performance.
by Alex Keh

VSLive! San Francisco, February 12, 2003

Note: Alex Keh is presenting "Accessing the Oracle Database From .NET" at VBITS San Francisco, Wednesday, February 12. This tip is from that session.

Batching Oracle SQL statements together will improve your application's performance by reducing the number of database server round-trips. The Oracle Data Provider for .NET (ODP.NET) supports batching SQL statements through anonymous PL/SQL blocks that can embed multiple SQL statements. For example:

string cmdtxt = "BEGIN " +
"OPEN :1 for select * from emp where deptno = 10; " +
"OPEN :2 for select * from emp where deptno = 20; " +
"OPEN :3 for select * from emp where deptno = 30; " +
"END;";

When this command is executed, it will return three REF Cursors in one round-trip, as opposed to making three separate round trips.

For more information about ODP.NET, see "Access Oracle-Specific Features" by Bob Beauchemin in the March 2003 issue of Visual Studio Magazine.

About the Author
Alex Keh is a Windows data access senior product manager at Oracle. He focuses on all aspects of .NET development with the Oracle database, including the Oracle Data Provider for .NET and Visual Studio integration. In the past, Alex has presented on Oracle's integration with .NET at Windows Server DevCon and Oracle OpenWorld. He has worked previously at both Microsoft and IBM on Windows database tools development. Reach Alex at alex.keh@oracle.com.