Seemingly simple query being very slow on Entity Framework

c# entity-framework performance sql

Question

So I have a very basic Data Model in my project (targeting .NET 4.0, using EF 5 installed by NuGet, database first), with 2 tables, Item and ItemGroup.

Item-table has various fields, both string and numerical, and a foreign key pointing to an ItemGroup.

ItemGroup on the other hand only has Id, Name and Code (of which the last 2 are strings).

Now, I have roughly 50k Items and only 100 ItemGroups. If I execute a context.Items.ToList(), using SQL Profiler, the duration is around 2-3 seconds which is completely acceptable. If, however, I want to load the ItemGroups at the same time using context.Items.Include("ItemGroup").ToList(), the execution time jumps up to around 12 seconds. Also if I simply fetch all the ItemGroups after having fetched all the Items, the execution time is very long as well. This leads me to believe that it's the mapping of the Items to their respective Groups that takes the time.

However, this still doesn't explain why SQL Profiler reports the very simple INNER JOIN -query taking way over 10 seconds as well, compared to the very same query without the JOIN taking a measly 2-3 seconds.

I am at a loss in here, never faced this kind of a problem before, so any advice is more than welcome.

1
2
12/3/2012 3:32:15 PM

Accepted Answer

At the time when you're using eager loading(include) it loads whole db tree at once from database.So it's very slow.

But You can identify the areas which you need to improve by converting your EF query into T-SQL by using LinqPad and then by using Database Engine Tuning Adviser on Sql Server 2008 will help you to identified the index keys what you needed.

I have written blog post about how to convert EF to T-Sql Here

This article will explain more than 10 methods to improve performance of your EF query Here

5
12/3/2012 3:31:10 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow