Christian Shay - Oracle and .NET

Mark Williams wrote a nice article for Oracle Magazine recently pointing out 3 of the most important ways you can get better performance with your ODP.NET application.

To summarize - his 3 tips were:

• Use a Connection Pool - enabled by setting an ODP.NET connection string attribute. Make sure to have a sufficiently high minimum pool size.
• Use the FetchSize and RowSize Properties - properties of the OracleDataReader and OracleCommand object respectively. Again, needs to be tuned depending on how you are using the network and the size of your table data.
• Use Statement Caching - another ODP.NET connection string attribute

I'll add to these the standard "best practices for performance" tips that we always tell customers:

• Use anonymous PL/SQL blocks or stored procedures to execute multiple SQL statements in one round trip
• Use Parameter Array Binding - A SQL statement will execute once for each array value when bound to the array
• Use PL/SQL Associative Arrays - pass in an array to your stored procedure or function
• Use Oracle LOB classes and expecially the InitialLOBFetchSize property of the OracleDataReader when you are accessing LOB data. This helps you tune your application for the size of data that you are expecting.
  

Hope this helps you to get every last bit of performance from your application!