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.
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