Why is Entity Framework taking 30 seconds to load records?

[shanewho]

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

The executeTime below is 30 seconds the first time, and 25 seconds the next
time I execute the same set of code. When watching in SQL Profiler, I
immediately see a login, then it just sits there for about 30 seconds. Then
as soon as the select statement is run, the app finishes the ToList command.
When I run the generated query from Management Studio, the database query
only takes about 400ms. It returns 14 rows and 350 columns. It looks like
time it takes transforming the database results to the entities is so small
it is not noticable.

So what is happening in the 30 seconds before the database call is made?

If entity framework is this slow, it is not possible for us to use it. Is
there something I am doing wrong or something I can change to speed this up
dramatically?


using (EntitiesContext context = new EntitiesContext())
{
Stopwatch sw = new Stopwatch();
sw.Start();
var groupQuery = (from g in
context.Groups.Include("DealContract")
.Include("DealContract.Contracts")

..Include("DealContract.Contracts.AdvertiserAccountType1")

..Include("DealContract.Contracts.ContractItemDetails")
.Include("DealContract.Contracts.Brands")
.Include("DealContract.Contracts.Agencies")

..Include("DealContract.Contracts.AdvertiserAccountType2")

..Include("DealContract.Contracts.ContractProductLinks.Products")

..Include("DealContract.Contracts.ContractPersonnelLinks")

..Include("DealContract.Contracts.ContractSpotOrderTypes")

..Include("DealContract.Contracts.Advertisers")
where g.GroupKey == 6
select g).OfType<Deal>();
sw.Stop();
var queryTime = sw.Elapsed;
sw.Reset();
sw.Start();
var groups = groupQuery.ToList();
sw.Stop();
var executeTime = sw.Elapsed;
}

 

[shanewho]

All right, if I use a Compiled query, the first time it take 30 seconds, and
the second time it takes 1/4th of a second. Is there anything I can do to
speed up the first call?

 

[Jeroen Mostert]

All right, if I use a Compiled query, the first time it take 30 seconds, and
the second time it takes 1/4th of a second. Is there anything I can do to
speed up the first call?

Are you running your application under a debugger, perchance? EF uses
reflection and dynamic compilation rather heavily, which is atrociously slow
when debugging. (It's not that fast in release either, but not half as slow.)

Also, take a look at
http://blogs.msdn.com/adonet/archiv...e-of-the-ado-net-entity-framework-part-1.aspx
In particular, this post mentions view generation as a way to reduce startup
time.