ADO.NET Entity Framework Performance Tips

Over a period of time I have been working with ADO.NET Entity Framework and I am flattered with its amazing usability. I am a big fan of EF and especially with EF4 release a lot more avenues are open. I love to talk about this technology in any forum and given a chance I would love to work on any implementation and migration. While doing all of these stuffs I found one big question which always comes to our mind is performance and it is quite obvious that for any data access technology performance is a big concern.

Below are my take on EF’s performance considerations I found could be helpful. Please feel free to add your thoughts and I would like to keep the option open to enhance the experience better for others.

Use CompiledQuery

CompiledQuery is a great way to fetch data. So if you have a query which you are planning to use again and again with search filter, you should create a compiled query for it. Remember one thing very clearly while defying CompiledQuery is - CompiledQuery takes a while to get generated for the first time and next time onwards it is really fast. So do not expect it to be faster than the normal LINQ.

Please declare CompiledQuery as static (shared in VB), otherwise the benefit of using CompiledQuery is wasted. Below is a small example of using CompiledQuery in EF

CompiledQuery

  1. static readonly Func<TestDBEntities, int, IQueryable<Emp>> myCompiledQuery
  2.     = CompiledQuery.Compile<TestDBEntities, int, IQueryable<Emp>>(
  3.         (ctx, eid) => from e in ctx.Emps
  4.                         where e.EmpId == eid
  5.                         select e);

Implementing CompiledQuery

  1. static void Main(string[] args)
  2. {
  3.     using (TestDBEntities ctx = new TestDBEntities())
  4.     {
  5.         var q = myCompiledQuery.Invoke(ctx, 1);
  6.         foreach (var k in q)
  7.         {
  8.             Console.WriteLine(k.FirstName);
  9.         }
  10.     }

Avoid using DTC Transaction

Avoid the use of Windows Distributed Transaction Coordinator with EF. Because performance pays for DTC. There would be cases where you have to use it. So think twice before you implement System.Transactions.TransactionScope. Please refer my blog on it https://blogs.msdn.com/b/wriju/archive/2011/03/14/ado-net-entity-framework-transaction.aspx 

Use MergeOptions.NoTracking

When simply reading data from database and not modifying it, makes no sense to use the identity management which is default. So you need to switch it off explicitly. So it is always better to set as below while doing read only.

ctx.Emps.MergeOption =

MergeOption.NoTracking;

or

((

ObjectQuery)q).MergeOption = MergeOption.NoTracking;

Shape the Query - as needed

When I mentioned “shape query” - it means you need to choose when to use Lazy Loading or Immediate Loading. In EF Lazy loading is default enabled while using designer. However you can switch it off.

Immediate Loading: If you are using the loading time of your application to load some user settings you should load of some of the data as well. This would avoid multiple round trips. However, this may be valid for thick client applications. The way to use immediate Loading is to use include method.

var

dep = from d in ctx.Depts.Include("EmpDepts") select d;

Lazy Loading: Is default enabled if using designer and useful in most of the cases (mainly for WebSites or shared memory) . But you can always change it by setting ctx.ContextOptions.LazyLoadingEnabled = true;

Manage Connection

You should manually open or close connection if you are using SaveChanges() multiple times under same context. Because SaveChanges automatically open and close connection as and when called. Manually opening and closing connection would help you avoid those situations.

ctx.Connection.Open();

Use Pre-Generated View

Using pre-generated view reduces the loading time. For more detailed discussion and how to please refer https://msdn.microsoft.com/en-us/library/bb896240.aspx

By choosing ORM you make your life simple on the other hand you have to pay for it as it uses extra overhead for being layered.

Namoskar!!!