The below are some tips towards optimizing the performance of applications that use Entity Framework.
- To disable AutoDetectChanges, when not needed (https://msdn.microsoft.com/en-us/data/jj556205.aspx / https://blog.oneunicorn.com/2012/03/12/secrets-of-detectchanges-part-3-switching-off-automatic-detectchanges/)
- Even though the DBContext object is a lightweight one, its the number of entities that affects the performance. For instance, after you are done creating a new DbContext, if you perform repeated .Add(..) and .SaveChanges(), at one point you will see a downgrade in performance and if investigated, it will point to the number of Entities that incrementally got included resulting in degrading the performance of the DBContext object. A solution is to dispose the DBContext and create new objects for a set of operations (for instance every 1000 objects), however from a “Data Integrity / Atomicity” (ACID) perspective, every DbContext will use its own connection and multiple connections will require MSDTC, which at times can be a trouble to live with in the long run. So, in such cases, we could resort to the option of using a single Connection and TransactionScope as outlined in the MSDN link. (https://msdn.microsoft.com/en-us/data/dn456843.aspx#existing).
- As a rule of thumb, while retrieving data from the database, retrieve only those fields are required. This is not just for including a where condition aimed at filtering the records, but also the fields that are required.
- Use Take & Skip in EF to query data based on “pages” (http://stackoverflow.com/questions/3870091/entity-framework-linq-to-sql-skip-take)
- Avoid calling SaveChanges() for every transaction, but instead try to call the SaveChanges() for every 500 iterations and dispose the old dbcontext and generate a new one. This approach eliminates the need to perform the change detection for each record (when saved individually) and as well removes network latency involved in persisting the changes to the data base. However, regeneration of the context object would essentially mean a SQLConnection is assigned to the db context, which would result in MSDTC (as there are multiple connections involved – one per context).
- Use SQLBulkCopy if it involves inserting multiple records (thousands of rows) http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx
- If you do not want to disable the AutoDetectChanges, another option is to detach the entities that are already committed through the SaveChanges() operation. This would reduce the number of entities being accumulated in the dbContext as we progress through the loop/iterations and help eliminate the lag that gets induced when the entities count in the db context crosses a threshold (which could vary depending on how relational the entities are).
1,288 total views, 4 views today