Poor performance getting clob field from Oracle in .Net

Question

I am trying to read a clob column from oracle in .Net and observing very poor performance and lots of network traffic.

I have tried ODP+OCI, devArt+OCI ways to access the data with the same results - it takes around 20 seconds to get 1000 rows in a data reader and read the clob value for each row.

Examining wireshark traces, it turns out that every time I try to read the clob field for a single row in the reader, there are additional multiple tcp packets sent between client and server. So for 1000 rows this becomes 1000 times slower than if querying just one row.

At the same time, if I run the same query in SQL Developer (which I believe uses thin jdbc driver instead of oci), I get the results instantly, including the clob values. It does not try to query clob for each row - it gets them all in one go!

But I don't see a thin client for .net. How can I speed things up? Please help!

EDIT: My field type is actually XMLTYPE stored as clob, not a true clob. Using getClobVal over it improves the result by 50% to 10 seconds for 1000 rows. But at the same time, Sql Developer is returning results instantly, not in 10 seconds.

Answer 1

You have correctly observed that Oracle delays the retrieval of LOBs. Thus, the performance your application is limited by the network round-trip time.

With the InitialLOBFetchSize property of the OracleCommand class (in ODP.NET, see documentation), you can tell Oracle to retrieve part of the LOB with the initial retrieval of the row. If your LOBs aren't too long, this can make quite a difference.