Horribly slow on large database

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Posts: 7
Joined: Mon 20 Dec 2010 09:33

Horribly slow on large database

Post by javed » Thu 08 Mar 2012 07:31


We are using Devart DotConnect for Oracle to generate the entity classes for oracle.

we are querying data from a large oracle database having 9 million records using LINQ to Entities in a silverlight project.

we have a complex view created in the database which joins 7 tables to retrieve the records. When we run the oracle sql queries in the oracle sql developer to retrieve first 100 records it returns within 1 second but when we run the similar query using LINQ it did not return even after one hour. I am using Take(100) to retrieve the initial 100 records.

My query looks like this

Code: Select all

Entities1 bookingEntities = new CMSDataService(new Uri(((App)Application.Current).m_strServiceBaseUri + ClientDefs.strsvcCMSMultitableService));
query = (from c in bookinEntities.V_MYVIEW select c).Take(100);

var dsQuery = (System.Data.Services.Client.DataServiceQuery<V_MYVIEW>)query;
dsQuery.BeginExecute(new AsyncCallback(c =>
IEnumerable<V_MYVIEW> result = dsQuery.EndExecute(c);
((App)Application.Current).ListBookingRecords = result.ToList();
}), dsQuery);         
The oracle query which gives instantaneous result in oracle Sql developer is

"select * from V_MYVIEW where rownum < 101"

Do I need to change some setting in the entity class. Or else what we are doing wrong?

Posts: 7
Joined: Mon 20 Dec 2010 09:33

Post by javed » Mon 12 Mar 2012 07:34

No reply, does it mean devart has no solution???

Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 12 Mar 2012 16:33

Recommendations to improve Entity Framework performance:
1. Check the SSDL part of your model and make sure there are no any defining queries (http://msdn.microsoft.com/en-us/library/bb738450.aspx). Otherwise, remove them.
2. A big model leads to very time-consuming first query in AppDomain because of view generation (http://msdn.microsoft.com/en-us/library/cc853327.aspx). This problem can be solved partially with view pregeneration (http://www.devart.com/entitydeveloper/large-models.html).
3. Please refer to the Performance Considerations (Entity Framework) article (http://msdn.microsoft.com/en-us/library/cc853327.aspx).

Post Reply