Tom Peplow's Blog

Continuing the theme of my last post, more on Oracle.  I have enjoyed porting a legacy application to Oracle (ok – maybe a bit sarcastic!).  I wish nHibernate existed when this product was first written, it would have been a much easier job.  Actually, in all fairness it’s been pretty painless – the application has it’s own data layer / ORM (object relational mapper) which abstracts away all the SQL so the main application hasn’t had to change that much.  Thank god for separation of concerns otherwise this port would have taken 10 maybe 100 times longer!  Refactoring the application to use nHibernate would have been an even bigger challenge, the app’s been around a bit and there’s no real dependency inversion on the ORM layer (if there was then it would have been a easy job to swap out the ORM to nHibernate!).  True dependency inversion between layers might feel like a waste of time but when you come back to a product 2 years later and need to adapt it for a new customer it can help – maybe I’ll do a post on that later…

Got a bit sidetracked there, the problem we had to solve was to batch SQL statements up into a single command so we can execute it in one go (this is how the SQL Server implementation works).  This is nothing ground breaking and the performance gains are pretty obvious, lots of chatty network calls when calling ExecuteNonQuery() will no doubt increase the time it takes to persist an object graph.  But creating a command containing lots of inserts / updates / deletes / stored proc calls using the OracleCommand (in ODP.Net) isn’t obvious, so I thought I’d share the findings.  The solution is actually pretty simple.

First attempt was the most obvious, cmd.CommandText = “insert into table (values :p1, :p2); insert into table (values :p3, :p4);”.  No joy, get invalid character (didn’t like the semi colon).  At this point I was a bit worried it might not be possible, but I thought it must be possible otherwise some people would have some serious scalability issues (the application we’re working on has some seriously high scalability requirements)!  So, I started playing in the debugger and guessed at cmd.CommandText = “begin insert into table (values :p1, :p2); insert into table (values :p3, :p4); end;” – that worked!  Once I’d figured that out it didn’t take me long to see on Google that using begin … end was the way to go… I also found this article on on optimising performance with ODP.Net…

Few things to watch out for… If you have any carriage returns in your command text you get an error, so when building up your SQL make sure you don’t add any…  You can call stored procedures, out parameters work and so do sys_refcursors.  Only down side is when calling a proc you must specify the parameters in the correct order, you can’t use parameter name binding as you would when using the StoredProcedure CommandType.

You can batch up select statements too: "begin open :p for select * from BulkTesting; open :p1 for select * from BulkTesting; end;" You’ll need to make sure you set your command up with out parameters for each of your cursors, i.e. cmd.Parameters.Add(":p", OracleDbType.RefCursor, ParameterDirection.Output);".  Then you can either use the OracleDataAdapter to fill a data set or ExecuteReader (use NextResult() to move to the next cursor of data).

Now to check it gives me a performance boost (bit of really simple test code here):

   1: using (var connection = new OracleConnection(connectionString))

   2: {

   3:    connection.Open();

   4:  

   5:    var sw = Stopwatch.StartNew();

   6:    using (var cmd = connection.CreateCommand())

   7:    {

   8:       var cmdText = new StringBuilder();

   9:       cmd.BindByName = true;

  10:       cmd.CommandType = CommandType.Text;

  11:  

  12:       cmdText.Append("begin ");

  13:       for (var i = 0; i < 500; i++)

  14:       {

  15:          cmdText.AppendFormat("insert into BulkTesting values (:id{0}, :data{0}); ", i);

  16:          cmd.Parameters.Add(string.Format(":id{0}", i), i);

  17:          cmd.Parameters.Add(string.Format(":data{0}", i), string.Format("bit of data for {0} call", i));

  18:       }

  19:       cmdText.Append(" end;");

  20:  

  21:       cmd.CommandText = cmdText.ToString();

  22:       cmd.ExecuteNonQuery();

  23:    }

  24:    sw.Stop();

  25:    Console.WriteLine("Batch query took {0}", sw.ElapsedMilliseconds);

  26:  

  27:    sw = Stopwatch.StartNew();

  28:    for (var i = 0; i < 500; i++)

  29:    {

  30:       using (var cmd = connection.CreateCommand())

  31:       {

  32:          var cmdText = new StringBuilder();

  33:          cmd.BindByName = true;

  34:          cmd.CommandType = CommandType.Text;

  35:  

  36:          cmdText.AppendFormat("insert into BulkTesting values (:id{0}, :data{0}) ", i);

  37:          cmd.Parameters.Add(string.Format(":id{0}", i), i);

  38:          cmd.Parameters.Add(string.Format(":data{0}", i), string.Format("bit of data for {0} call", i));

  39:  

  40:          cmd.CommandText = cmdText.ToString();

  41:          cmd.ExecuteNonQuery();

  42:       }

  43:    }

  44:    sw.Stop();

  45:    Console.WriteLine("Multiple queries took {0}", sw.ElapsedMilliseconds);

  46: }

Gave these results:

So In summary, it’s not hard once you’ve got begin end; involved :).  If any gurus reading know of an alternative, please let me know!