Last post Mar 02, 2016 06:34 AM by Yohann Lu
Mar 01, 2016 05:59 AM|Harry US11|LINK
I am using EF6 as ORM with OData Web API. The database is in oracle.
Database is huge in size, i have more than 200 tables and then major tables (around 10 +) have more than 10 million records. I am using code first approach (though i have database already in place). But running into issues with performance being very slow
Once I am sending odata filters from UI, it takes around 120 seconds to get just around 5000 records from main table (JSON format on UI). I don't have paging option to filter more records as well.
When i am analysing the query that EF is generating, sometimes it is generating quite complex query which takes more time to execute on DB itself. At other times, query getting small time, but then may be more time is consumed when the model is being populated
and JSON is being sent to UI.
But i must say, my application is dying because of EF. I am thinking of moving to older ADO.Net and then populate models by myself. But then i am not feeling comfortable in going to old technology ( as i want to use EF features). This stuff makes me difficult
to generate queries using oDAta filters and then creating SQL queries using them myself. It really makes no sense to me.
Thoughts please????? Tried some solutions after googling on EF performance but of no use.
Expert advise please!!!!!!!!!
Mar 02, 2016 06:34 AM|Yohann Lu|LINK
Hi Harry US11,
The following suggestions for your reference. As far as I know, if you keep these things in mind EF should give an almost similar performance as plain ADO.NET.
1: Call to database made only when the actual records are required. all the operations are just used to make the query (SQL) so try to fetch only a piece of data rather than requesting a large number of records.
2: You can use stored procedures where necessary. EF performs slower when it has to populate an Entity with deep hierarchy.
3: Sometimes when you are requesting records and you are not required to modify them you should tell EF not to watch the property changes (AutoDetectChanges). that way record retrieval is much faster.
4: Indexing of the database is good, but in case of EF it becomes very important. The columns you use for retrieval and sorting should be properly indexed.
5: There is a limitation that the Entities from different models cannot be shared even though they may be pointing to the same table in the database.
6: When you have to make changes in the same entity at different places, use the same entity, make changes and save it only once. The point is to AVOID retrieving the same record, make changes & save it multiple times.
7: You may need to cache some frequently used data in your application also.
*Second Level cache The 2nd level caching is a query cache. The results of SQL commands are stored in the cache, so that the same SQL commands retrieve their data from the Cache rather than executing the query again against the underlying provider. Entity
Framework - Second Level Caching with DbContext:
8: Avoid using a large database transaction, try to control when there is demand for open, timely closed when not needed, it will lock the resource;
The following information for your reference:
Entity Framework For Large Databases: