CODEWITHSUNDEEP
c#sqlperformanceentity-framework
bobblez
bobblez

Reputation: 1370

Seemingly simple query being very slow on Entity Framework

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.

Upvotes: 2

Views: 7579

Answers (1)

Sampath
Sampath

Reputation: 65860

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.

Microsoft has also published an article on EF performance considerations you may want to read.

Upvotes: 5

Related Questions