Seemingly simple query being very slow on Entity Framework

c# entity-framework performance sql

Question

The project I'm working on (targeting.NET 4.0, utilizing EF 5 installed via NuGet, database first) has a very simple data model with just two tables, Item and ItemGroup.

Item-table has a foreign key referring to an ItemGroup and a number of fields, both string and numerical.

ItemGroup, in contrast, consists of of Id, Name, and Code (of which the last 2 are strings).

I currently only have 100 ItemGroups and about 50k Items. If I carry out acontext.Items.ToList() The length is about 2-3 seconds while using SQL Profiler, which is perfectly acceptable. However, if I want to simultaneously load the ItemGroups utilizingcontext.Items.Include("ItemGroup").ToList() execution time increases to about 12 seconds. The execution time is also very long if I simply fetch all the ItemGroups after fetching all the Items. This makes me think that the time-consuming process is mapping the Items to the appropriate Groups.

This still doesn't explain, however, why SQL Profiler displays the quite straightforward INNER JOIN query as taking over 10 seconds, as opposed to the exact same query without the JOIN, which only takes a meager 2-3 seconds.

Any advise would be greatly appreciated because I am at a loss and have never encountered this kind of issue before.

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

Accepted Answer

When you use eager loading(include), the entire database tree, immediately from database, is loaded. So it moves extremely slowly.

However, you can pinpoint the areas where you need to improve by utilizing LinqPad, followed by Advisor for Database Engine Tuning on 2008 SQL Server, which will aid in pinpointing the key indexes that you require.

Zzz-39-zzzzzz-43-Zzz is a topic I covered in a blog article.

We'll explain zzz-53 zzzzzz-57 zzz in this article.

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