Slow fetch with NCLOBs

cew3 · Post by **cew3** » Thu 16 Jul 2009 05:59

I have a big performance problem with NCLOBS.
My table contains about 11.000 rows (incl. 3 nclobs) and takes about 30 seconds when fetching with an simple:

"select * from myTable"

If i use the same query within the OraDeveloper Studio it just takes 0,3 seconds!!
And I can see the nclob data in the grid!

How is this possible???

What the hell is done in the studio to have such a great performance??
How can I get that in the ado.net query?

I wrote a query just using non-NCLOB columns and the result was as fast as the studio result so it has to be a problem with fetching the nclobs!

Any ideas??

cew3

Shalex · Post by **Shalex** » Thu 16 Jul 2009 09:01

This is a ReadLobMode issue (the ReadLobMode property of the OracleDataTable class). By default, OracleDataTable.ReadLobMode = ReadLobMode.Value - LOB objects are treated as byte arrays or strings, and fetched immediately. Please set OracleDataTable.ReadLobMode = ReadLobMode.Deferred - the actual LOB data will be fetched only when the field value is requested.

For more information, please refer to our online documentation at http://www.devart.com/dotconnect/oracle/docs/ , the Index tab, the ReadLobMode Enumeration section.

cew3 · Post by **cew3** » Thu 16 Jul 2009 09:35

This property is available just for OracleDataTables used in ado.net, right?

In our application we support multiple database systems with ado.net and LinqToEntity support.
For ado.net access we have an abstract database class. This will not allow me to use the OracleDataTable instead of DataTable to set the property.

Is a similar mechanism like ReadLobMode available for the Entity Framework Provider?

Shalex · Post by **Shalex** » Thu 16 Jul 2009 11:40

We have answered to you at http://www.devart.com/forums/viewtopic.php?t=15330 .