CODEWITHSUNDEEP
entity-frameworkentity-framework-4entity-framework-4.1
swirlobt
swirlobt

Reputation: 301

Entity Framework 4.1 query takes too long (5 seconds) to complete

I have DbContext (called "MyContext") with about 100 DbSets within it.

Among the domain classes, I have a Document class with 10 direct subclasses (like PurchaseOrder, RequestForQuotation etc). The heirarchy is mapped with a TPT strategy. That is, in my database, there is a Document table, with other tables like PurchaseOrder, RequestForQuotation for the subclasses.

When I do a query like:

Document document = myContext.Documents.First();

the query took 5 seconds, no matter whether it's the first time I run it or subsequently.

A query like:

Document document = myContext.Documents.Where(o => o.ID == 2);

also took as long.

Is this an issue with EF4.1 (if so, will EF4.2 help) or is this an issue with the query codes?

Upvotes: 4

Views: 988

Answers (4)

Mivoat
Mivoat

Reputation: 11

I've just had a 5 sec delay in ExecuteFunction, on a stored procedure that runs instantaneously when called from SQL Management Studio. I fixed it by re-writing the procedure.

It appears that EF (and SSRS BTW) tries to do something like a "prepare" on the stored proc and for some (usually complex) procs that can take a very long time.

A quick and dirty solution is to duplicate and then replace your SP parameters with internal variables:

create proc ListOrders(@CountryID int = 3, @MaxOrderCount int = 20)
as
declare @CountryID1 int, @MaxOrderCount1 int
set @CountryID1 = @CountryID
set @MaxOrderCount1 = @MaxOrderCount

select top (@MaxOrderCount1) *
from Orders
where CountryID = @CountryID1

Upvotes: 1

J. Tihon
J. Tihon

Reputation: 4459

I'm not certain that the DbSet exposed by code-first actually using ObjectQuery but you can try to invoke the .ToTraceString() method on them to see what SQL is generated, like so:

var query = myContext.Documents.Where(o => o.ID == 2);
Debug.WriteLine(query.ToTraceString());

Once you get the SQL you can determine whether it's the query or EF which is causing the delay. Depending on the complexity of your base class the query might include a lot of additional columns, which could be avoided using projection. With using projections, you can perform a query like this:

var query = from d in myContext.Documents
            where d.ID == 2
            select new
            {
                o.Id
            };

This should basically perform a SELECT ID FROM Documents WHERE ID = 2 query and you can measure how long this takes to gain further information. Of course the projected query might not fit your needs but it might get you on the right track. If this still takes up to 5 seconds you should look into performance problems with the database itself rather than EF.

Update Apparently with code-first you can use .ToString() instead of .ToTraceString(), thanks Slauma for noticing.

Upvotes: 3

Nelson Reis
Nelson Reis

Reputation: 4810

As you can read here, there are some performance issues regarding TPT in EF.

The EF Team annouced several fixes in the June 2011 CTP, including TPT queries optimization, but they are not included in EF 4.2, as you can read in the comments to this answer.

In the worst case, these fixes will only be released with .NET 4.5. I'm hoping it will be sooner...

Upvotes: 3

Claiton Lovato
Claiton Lovato

Reputation: 2382

Did you try using SQL Profile to see what is actually sent to the DB? It could be that you have too many joins on your Document that are not set to lazy load, and so the query has to do all the joins in one go, bringing back too many columns. Try to send a simple query with just one return column.

Upvotes: 3

Related Questions