2

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.

Improve this question
4
  • 2-3 seconds for 100 rows? That seems bad to me for a start, what sort of hardware are you running on? Commented Dec 3, 2012 at 14:35
  • If I first query only the groups, the time drops to milliseconds. I guess it's the mapping of the entities or something of the sort that takes the time. Commented Dec 3, 2012 at 14:43
  • 1
    Are you sure the inner join is performed on index'ed fields? Try using the output from SQL profiler in the database tuning wizard. Commented Dec 3, 2012 at 14:50
  • I checked the execution plan on management studio and it suggested adding an index, which I did. But it didn't seem to have any effect. Where can I find this database tuning wizard? Commented Dec 3, 2012 at 15:29

1 Answer 1

Reset to default
5

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks, tho I do already have the T-sql of the query and as mentioned, it is just a very simple SELECT <this and that> FROM Items INNER JOIN ItemGroup -kind of a query, but SQL Profiler still reports the said 12 seconds as the duration.
Do you have a relevant index for that query,According to the suggestions of Database Engine Tuning Adviser ?
Yes, I tried running the Tuning Adviser on the query but it provided no recommendations for new indexes.
That means you have created necessary indexes.Then you have to Check whether your slowness only for the first query run.If so you can get more details about that by using 2 link I have mentioned above under point 2 - Cold vs. Warm Query Execution.Check that also.
Actually simply adding a AsNoTracking() to my query solved my problems. This obviously requires a manual attach if I want to commit changes made to existing entities, but for my use case this works well. And the solution can be found on the msdn article you linked to, I'll accept your answer, thanks.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.