Why is Entity Framework taking 30 seconds to load records when the generated query only takes 1/2 of a second?

I had this exact same problem, my query was taking 40 seconds I found the problem was with the . Include("table_name") functions. The more of these I had, the worse it was.

Instead I changed my code to Lazy Load all the data I needed right after the query, this knocked the total time down to about 1.5 seconds from 40 seconds. As far as I know, this accomplishes the exact same thing So for your code it would be something like this: var groupQuery = (from g in context. Groups where g.

GroupKey == 6 select g).OfType(); var groups = groupQuery.ToList(); foreach (var g in groups) { // Assuming Dealcontract is an Object, not a Collection of Objects g. DealContractReference.Load(); if (g. DealContract!

= null) { foreach (var d in g. DealContract) { // If the Reference is to a collection, you can just to a Straight ". Load" // if it is an object, you call ".

Load" on the refence instead like with "g. DealContractReference" above d.Contracts.Load(); foreach (var c in d. Contracts) { c.

AdvertiserAccountType1Reference.Load(); // etc.... } } } } Incidentally, if you were to add this line of code above the query in your current code, it would knock the time down to about 4-5 seconds (still too ling in my option) From what I understand, the "MergeOption. NoTracking" option disables a lot of the tracking overhead for updating and inserting stuff back into the database: context.groups. MergeOption = MergeOption.NoTracking.

I had this exact same problem, my query was taking 40 seconds. I found the problem was with the . Include("table_name") functions.

The more of these I had, the worse it was. Instead I changed my code to Lazy Load all the data I needed right after the query, this knocked the total time down to about 1.5 seconds from 40 seconds. As far as I know, this accomplishes the exact same thing.So for your code it would be something like this: var groupQuery = (from g in context.

Groups where g. GroupKey == 6 select g).OfType(); var groups = groupQuery.ToList(); foreach (var g in groups) { // Assuming Dealcontract is an Object, not a Collection of Objects g. DealContractReference.Load(); if (g.

DealContract! = null) { foreach (var d in g. DealContract) { // If the Reference is to a collection, you can just to a Straight ".

Load" // if it is an object, you call ". Load" on the refence instead like with "g. DealContractReference" above d.Contracts.Load(); foreach (var c in d.

Contracts) { c. AdvertiserAccountType1Reference.Load(); // etc.... } } } } Incidentally, if you were to add this line of code above the query in your current code, it would knock the time down to about 4-5 seconds (still too ling in my option) From what I understand, the "MergeOption. NoTracking" option disables a lot of the tracking overhead for updating and inserting stuff back into the database: context.groups.

MergeOption = MergeOption.NoTracking.

1 This seems so counter intuitive. Making multiple SQL requests and loading the same number of objects is quicker than making one sql request? – toxaq Jun 8 at 5:40.

It is because of the Include. My guess is that you are eager loading a lot of objects into the memory. It takes much time to build the c# objects that corresponds to your db entities.My recommendation for you is to try to lazy load only the data you need.

Lazy Loading wouldnt help because we need all of the objects included. It really only returns about 40 objects total(1 Deal, 3 DealContracts, 3 Contracts, 3 contractitemdetails on each contract, and 1 of each of the other properties on each Contract), so I wouldnt think it would be too intensive...? – NotDan Mar 26 '09 at 17:26 I don't agree that needing all the objects means that lazy loading wouldn't help. See my answer for the reason.

Sometimes it is faster to execute a simpler query and then let the details with other simpler queries. – Craig Stuntz Mar 27 '09 at 12:03.

The only way to make the initial compilation of the query faster that I know of is to make the query less complex. The MSDN documentation on performance considerations for the Entity Framework and Compiled Queries don't indicate that there is any way to save a compiled query for use in a different application execution session. I would add that we have found that having lots of Includes can make query execution slower than having fewer Includes and doing more Loads on related entities later.

Some trial and error is required to find the right medium. However, I have to ask if you really need every property of every entity you are including here. It seems to me that there is a large number of different entity types in this query, so materializing them could well be quite expensive.

If you are just trying to get tabular results which you don't intend to update, projecting the (relatively) fewer number of fields that you actually need into a flat, anonymous type should be significantly faster for various reasons. Also, this frees you from having to worry about eager loading, calling Load/IsLoaded, etc. You can certainly speed up the initial view generation by precompiling the entity views. There is documentation on MSDN for this.

But since you pay that cost at the time the first query is executed, your test with a simple query shows that this is running in the neighborhood of 2 seconds for you. It's nice to say that 2 seconds, but it won't save anything else.

Unfortunatlly, I really do need all properties on all objects. – NotDan Mar 26 '09 at 21:19.

EF takes a while to start up. It needs build metadata from xml and probably generates objects used for mapping. So it takes a few sec to start up, I don't think there is a way to get around that, except never restarting your program.

That seems excessive. – billb Mar 26 '09 at 19:05 I haven't really used it with any complicated models, and it takes around 5 sec to start up most of the time. You objects look a lot more complex that those I have tho.

Try doing a smaller query and see how long that takes. – AndreasN Mar 26 '09 at 19:09 Adding a simple query (1 record, no includes) before the complicated query saves 2 seconds.So it seems it takes EF 2 seconds to start up. It is still taking 28 seconds for the first complicated query which is too long.

– NotDan Mar 26 '09 at 19:29.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.