NCLOBs cause slow speed...

cew3 · Post by **cew3** » Wed 15 Jul 2009 15:03

I'm using deth EF Provider for Oracle and have a performance problem with NCLOBS.
My table contains about 11.000 rows (incl. 3 nclobs) and takes about 30 seconds when fetching with an simple:

from c in myTable select c;

For a speed test I tried using a ado.net query into a datatable with the same result.

Ok, the EF provider isn't slower than ado.net.

B u t: If i use the same query "select * from myTable" 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 EF Provider?

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!
But I need that damn nclob data...

Any ideas??

cew3

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

Ok, for OracleDataTables there is a property ReadLobMode which can be set to "Deferred"

Is there a similar mechanism available for the Entity Framework Provider?

AndreyR · Post by **AndreyR** » Thu 16 Jul 2009 11:30

The trick is that OraDeveloper Tools use ReadLobMode property of OracleDataTable.
This mechanism is explained in the reply to your other post.
So, you can achieve the needed perfomance using only ADO.NET.
As for Entity Framework, there is no way not to fetch NCLOB columns without changing a model.
But you can extract NCLOB properties to a separate entity associated with the main by one-to-one relationship and then
execute NclobColumn1Reference.Load() to fetch only necessary values.
The similar approach (with an example with Include, however) is described in the 7th chapter
(Delay Loading Expensive Fields on a Table) of Zeeshan Hirani's book "Entity Framework learning guide",
you can find the book here:
http://cid-245ed00edb4c374e.skydrive.li ... 0guide.pdf
(This book is described in Zeeshan's blog here: http://weblogs.asp.net/zeeshanhirani/).
As an alternative, you can switch to LINQ to Oracle, there is a possibility to set the Delay Loaded property for each column in Entity Set.

cew3 · Post by **cew3** » Fri 17 Jul 2009 11:36

Thank you!

That's an interesting approach.

AndreyR · Post by **AndreyR** » Fri 17 Jul 2009 12:49

Just one more potentially useful link:
http://blogs.gotdotnet.com/adonet/archi ... table.aspx

AndreyR · Post by **AndreyR** » Fri 23 Oct 2009 10:40

We have added the design time support for the Table Splitting scenario in the upcoming Beta build of Entity Developer.