Category Archives: Entity Framework

Entity Framework and Performance Improvements

Lately I have been looking into some performance problems in one of our projects and it turns out this was closely related to poor performance in Entity Framework (EF) in materalization of list objects.

Use AsNoTracking() attribute

on queries that return read-only data, typically for calculations or lists. By default EF runs WITH change tracking and when your model is fairly big and has many associations this will give a severe performance degrade. It doesn’t even matter if these associations is included in your query!! The AsNoTracking() extension instruct EF to not add the data to context for change tracking and is really powerful.

For demo purposes I used the RawBencher by Frans Bouma (https://github.com/FransBouma/RawDataAccessBencher) and loaded all 31465 records in SalesOrderHeader table.

All performance number below is the average of 10 runs with fastest and slowest time removed. The tests were run using

· WS2008 with SqlServer 2008
· Win7 – Citrix Virtual machine as client
· Running RawBencher – compiled in Release mode without Debugger from VS 2013.

With change tracking:

Code:

using(var ctx = new AWDataContext())
{
    return ctx.SalesOrderHeaders.ToList();
}
Materialization (load) time:

EF 6.0.1:

#1: Entity Framework v6.0.0.0 (v6.0.21010.0) : 4.370,00ms.
#2: Entity Framework v6.0.0.0 (v6.0.21010.0) : 4.387,88ms.

EF 6.0.2:

#1: Entity Framework v6.0.0.0 (v6.0.21211.0) : 4.712,38ms.
#2: Entity Framework v6.0.0.0 (v6.0.21211.0) : 4.683,13ms.

EF 6.1.0-alpha1:

#1: Entity Framework v6.0.0.0 (v6.1.21218.0) : 4.524,00ms.
#2: Entity Framework v6.0.0.0 (v6.1.21218.0) : 4.577,50ms.

LINQ-TO-SQL

#1: Linq to Sql v4.0.0.0 (v4.0.30319.18408)  : 721,00ms.
#2: Linq to Sql v4.0.0.0 (v4.0.30319.18408)  : 717,25ms.

 

Without change tracking:

code:

EF:

using(var ctx = new AWDataContext())
{
    return ctx.SalesOrderHeaders.AsNoTracking().ToList();
}
LINQ to SQL
using(var ctx = new L2SBencherDataContext())
{
    ctx.ObjectTrackingEnabled = false;
    return ctx.SalesOrderHeaders.ToList();
}

Materialization (load) time:

EF 6.0.1:

#1: Entity Framework v6.0.0.0 (v6.0.21010.0) : 659,63ms.
#2: Entity Framework v6.0.0.0 (v6.0.21010.0) : 658,38ms.   

EF 6.0.2:

#1: Entity Framework v6.0.0.0 (v6.0.21211.0) : 461,88ms.
#2: Entity Framework v6.0.0.0 (v6.0.21211.0) : 458,13ms.

EF 6.1.0-alpha1:

#1: Entity Framework v6.0.0.0 (v6.1.21218.0) : 446,38ms.
#2: Entity Framework v6.0.0.0 (v6.1.21218.0) : 452,25ms

LINQ-TO-SQL:

#1: Linq to Sql v4.0.0.0 (v4.0.30319.18408)  : 555,88ms.
#2: Linq to Sql v4.0.0.0 (v4.0.30319.18408)  : 558,63ms.

Worth noting here is that

  • the materialization time with no tracking queries keeps getting improved with the newest versions of EF but the tracking versions is NOT improving.
  • LINQ-To-SQL with change tracking is WAY faster than EF with change tracking on a model with many associations.
  • You can also turn off object tracking with LINQ-TO-SQL
  • You may also see spikes of CPU usage on 100% when running these queries.

You should also be aware that the additional time affects the CPU usage on your client computer, the web server, and not the database – and depending on your transaction isolation level you may be holding transactions and locks on your table during the slower loading and cause bottlenecks in your database too.  See paragraph below.

So what should you do? The challenge is that these entities may end up inn code that expects them to have change tracking – as there is no obvious naming conventions that implies that these are readonly objects. After all – it is regular entities – just without change tracking.

When designing your data access and API you should pay close attention to how you handle read-only objects – typically for lists.
And at least for now – you should apply the AsNoTracking() on sets that load many objects into memory to enhance performance.
If you have a separate business objects layer and only use EF for Data Access then apply add AsNoTracking to fetches into readonly lists.

The AsNoTracking extension was introduced in EF 4.1.

The issue of slow materialization is a know issue in EF – see https://entityframework.codeplex.com/workitem/1829
“The time it takes EF to materialize data is a function of the amount of associations (foreign keys) the entire model has, even when such associations are not part of the query in question.”

If you would like to have this addressed please add your vote on this issue.

Consider using NOLOCK or SNAPSHOT ISOLATION LEVELto avoid transactions on your select statements

The most important part here is that you do not want selects (retrieving) data from the database to block writing of data. With hand coded SQL we would typically use the with (nolock) statement but with Linq 2 Sql and Enitity Framework there are other ways to accomplish this.

Scott Hanselman has a nice post on this issue

http://www.hanselman.com/blog/GettingLINQToSQLAndLINQToEntitiesToUseNOLOCK.aspx

Happy coding!

Advertisements