C# and .NET Tips and Tricks

Slow Performance. Is it the Entity Framework or you?

A lot of times, I get claims and concerns from colleagues and partners that EF is slow and it cannot compare to writing your own SQL queries for the database directly. In this post, we will try to clarify to what extent the above statement is true.

Suppose that you have the following simple schema in your database:

Let's examine some EF queries and approaches that affect performance:

Querying the data in the Locations table

Suppose you want to get some data from the Locations table. You may use the following:

using (LocationsContext ctx = new LocationsContext())
{
	var Locations = ctx.Locations.ToList();
}

Several runs of the above query yield approximately 3600ms to retrieve 80000 records. But wait a minute. Since we want only to display those data and not process them we do not need the proxies created for monitoring those objects by EF so we can effectively disable them as follows:

using (LocationsContext ctx = new LocationsContext())
{
	ctx.Configuration.ProxyCreationEnabled = false;
	var Locations = ctx.Locations.ToList();
}

Now the new time for 80000 records is approximately 3300ms that is 300ms faster than the previous case. But wait a minute again. We probably do not need all the fields from the table, just the ones we want to display. So we switch the above to a projection, by selecting only the properties we need:

using (LocationsContext ctx = new LocationsContext())
{
	ctx.Configuration.ProxyCreationEnabled = false;
	var Locations = ctx.Locations.select(x=>new{...}).ToList();
}

Now the new time for 80000 records is approximately 1300ms that is 2000ms faster than the previous case. If this is still to slow for us, now is the time to create a stored procedure in the SQL Database and see the result.

using (LocationsContext ctx = new LocationsContext())
{
	ctx.Configuration.ProxyCreationEnabled = false;
	var Locations = ctx.spDemo().ToList();
}

Now the new time for 80000 records is approximately 700ms that is 600ms faster than the previous case.

The whole idea in the example above is about "iterative refinement". If we would monitor the process with steps it would be as follows:

• First you prototype with just plain LINQ. It is ultra fast, you do not need to change the database in any way and you get the results you want
• Then you disable the proxies to have some performance increase
• As the specs settle and you know which fields you need you create the projection
• If the result is still to slow for you then you may end up creating a stored procedure in the database and using it

In all of the above the "key point" is that the change is localized in the query itself and does not affect any code surrounding it. This means that is can be done in any part of the development process.