Updated: Oracle, .NET & Bind Variables

If you use Oracle as the database for your .NET applications then you must use bind variables in your SQL queries, I repeat YOU MUST USE BIND VARIABLES! The performance benefits of using bind variables are HUGE and cannot be underestimated.

 

So instead of doing this:  

string sql = "select * from SOME_TABLE where ID = " + id.ToString();
OracleCommand cmd = new OracleCommand(sql, _conn);
OracleDataReader rdr = cmd.ExecuteReader();

Do this:

string sql = "select * from SOME_TABLE where ID = :p_id";
OracleCommand cmd = new OracleCommand(sql, _conn);
cmd.Parameters.Add("p_id", OracleDbType.Decimal, Convert.ToDecimal(id), ParameterDirection.Input);
OracleDataReader rdr = cmd.ExecuteReader(); 

The project I’m currently working on is a new release of an internally developed app I worked on 2 years ago. It was our first .NET on Oracle project and we didn’t know of the many benefits of using bind variables in our SQL & DML statements. I have spent the last few days refactoring as much of the DAL code as I can to use bind variables and the effects on performance are astonishing. My understanding of the reasons is limited but basically if you use bind variables to pass in variable values to your queries, the Oracle SQL parsing engine can re-use much of the work it does when executing the queries when the values of the variables change. Plainly put, the query never changes, only the values do, and this means that execution plans can be re-used, etc. An added bonus is that it effectively paramaterises your queries, making them immune to SQL injection attacks.

 

I found a handy article today in relation to using bind variable to pass a list of values to a SQL IN statement. I’ve implemented the suggested pattern and it works a treat! Just note that you’ll have to read a fair way down the page to get to the example code that relates to the more recent versions of Oracle (9 & above). Just search the page for "Delimiter  December 29, 2004" to find it.

 

Update: Found another great getting started article today on bind variables on OTN at http://www.oracle.com/technology/oramag/oracle/05-sep/o55odpnet.html. Check it out.

---