How to efficiently handle slow CLOB reading from Oracle in .Net?

Question

At my company we have a WPF application, it can connect to different databases (MS SQL, MySQL, SQLite and Oracle). We have numerous tables (sometimes 200+, dont ask why, its very complicated) and our dataaccess layer has several interfaces and virtual/overridden methods to handle data reading in specific database. It is important, as we dont work specifically on one table as most tutorials shows, instead we have dynamically created database-specific commands we need to run. Under MS SQL, MySQL and SQLite everything works well, but in Oracle CLOB reading is very slow. To read 2000 rows of data it takes 40-50 seconds. Unfortunately in most of the cases we cannot assume that we dont need CLOB type as we are storing financial data in xml format and sometimes it exceeds of 4000, 8000 and even 10k+ char-long size. Im using ODP.Net solution to read data, this is what im doing:

Our environment: Oracle: 11.2.0.1.0 VS: 2010, Professional edition Oracle.DataAccess.dll is referenced from my Oracle home install: C:\Oracle\ODP.NET\bin\4\Oracle.DataAccess.dll

And my testcode (from our solution):

Property:

private string ConnectionString
{
   get
   {
      return
         string.Format(
         "User Id={0}; Password={1}; POOLING=true;
         Data Source= (DESCRIPTION=(ADDRESS=
         (PROTOCOL=TCP)(HOST={2})(PORT={3})) (CONNECT_DATA=(SID={4})
         (SERVICE_NAME={5})));",
         "ourUser", "ourPassword", "ourHost", "ourPort", "ourSID",
         "ourDatabaseName");
        }
    }

The command to run:

string sql = "SELECT * FROM OurTable";
//This table contains at least one CLOB column

And our test code:

List<object[]> readerList = new List<object[]>();

using (Oracle.DataAccess.Client.OracleConnection oraConn = new
    Oracle.DataAccess.Client.OracleConnection(ConnectionString))
{
   oraConn.Open();
   Oracle.DataAccess.Client.OracleCommand oraComm = new
      Oracle.DataAccess.Client.OracleCommand(sql);
   oraComm.CommandType = CommandType.Text;
   oraComm.Connection = oraConn;
   Oracle.DataAccess.Client.OracleDataReader oraReader;
   oraReader = oraComm.ExecuteReader();
   oraComm.InitialLOBFetchSize = -1;

   while (oraReader.Read())
   {
      object[] readObjects = new object[oraReader.FieldCount];
      oraReader.GetValues(readObjects);
      readerList.Add(readObjects);
   }

  oraConn.Close();
}

The while iteration runs very slow, unless we dont need to read CLOB as in this it would be fast. Unfortunately i cannot make table specific solutions as not in every case i know on which tables do i have to work on (sometimes there are dynamically created tables).

So, the question: Is there any solution to make it as fast as reading TEXT type objects from MS SQL?

Answer 1

It’s really hard to say exactly what cause the slowness. But I am pretty sure that clob shouldn’t slow like this normally.

It’s good to narrow down whether the C# code (the odp.net access way) or the oracle server is the root cause. There are various ways to identify in oracle side. The first thing I would try is execute your SQL on sql*plus with SET AUTOTRACE ON, this will tell us how this behave on oracle server.

• Also, there are several guesses or recommendations we can make base on your account of. If your data length is always less than 32K and you can upgrade oracle to 12c, than try varchar2 instead of clob or blob, as varchar2 in 12c has been extended to 32K;
• The LOB Storage is an important option for lob. “select dbms_metadata.get_ddl( 'TABLE', 'your table' ) from dual;” will tell the option in your table. For me “ENABLE STORAGE IN ROW” should always be used. Detail about this option, please see http://docs.oracle.com/cd/B28359_01/appdev.111/b28393/adlob_tables.htm#i1012988

Answer 2

Have a look at property FetchSize in Command object, see here: Improve ODP.NET Performance

You can get the size of row like this:

   Public Function GetRowSize(ByVal cmd As OracleCommand) As Integer
      Dim dr As OracleDataReader
         dr = cmd.ExecuteReader()
         Return CInt(dr.GetType.GetField("m_rowSize", Reflection.BindingFlags.Instance Or Reflection.BindingFlags.NonPublic).GetValue(dr))
   End Function

In C# it looks like this:

public int GetRowSize(OracleCommand cmd)
{
   OracleDataReader dr = cmd.ExecuteReader();
   return (int)( dr.GetType().GetField("m_rowSize", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(dr) );
}

You can execute this function once for each distinct query when your application is launched, then you can reuse the value.

I remember I had similar problem some time ago. My solution was to select the table without any CLOB column. In order to get CLOB value, I run an extra SELECT (with OracelDataReader like you) only for single CLOB column and single record.

I also found this documentation: Obtaining LOB Data

Answer 3

I know this is old, but i was having a similar issue. Setting the InitialLOBFetchSize = -1 on the OracleCommand made a huge difference for me. My CLOB columns are of a consistent and reasonable size, so this setting makes sense for me.

By setting InitialLOBFetchSize to -1, it is possible to fetch the entire LOB data from the database for a select query, without requiring a primary key, ROWID, or unique column in the select list. When InitialLOBFetchSize is set to -1, the entire LOB column data is fetched and cached during the Read method invocations on the OracleDataReader object.

More Details Here: https://docs.oracle.com/cd/B28359_01/win.111/b28375/featData.htm#BABFGDGJ