How do you minimize the performance hit when upgra

Question

I recently updated an app with LINQ to SQL and SQL Server CE 3.5 to Entity Framework 4.1 Code First and SQL Server CE 4.0, and it's now running noticeably slower. I did some before vs. after stopwatch testing, and most major operations of my app appear to be running about 40% slower on average.

I'm using all default strategies and configurations for EF Code First except for disabling cascading deletes.

When I originally posted this question, I was focused on one query that seemed to be taking particularly long, but I've since realized that it was only particularly slow on first run (see the comment thread below).

What I now think I'm seeing is that most queries are running slower--not dramatically slower, but slow enough to quickly add up as most operations the app performs involve several queries.

This app has a very small database. The SQL CE (.sdf) file is only 458 KB, and the largest table has less than 250 records.

Here's an example POCO class:

public class Target
{
    public int Id { get; set; }
    public int TrialDefinitionId { get; set; }
    public int Number { get; set; }
    public int X { get; set; }
    public int Y { get; set; }
    public string Phase { get; set; }
    public virtual TrialDefinition TrialDefinition { get; set; }
}

All my classes follow this basic pattern (simple types + virtual properties to obtain objects linked by foreign keys). I have one class that uses an ICollection to obtain a listing for a many-to-one relationship.

Final note: I'm using a repository pattern as a mediator, and each usage of a repository is placed in a using block. For "get" operations, this results in entities becoming detached once I've obtained the data I need from the database.

Does anyone have any specific strategies for improving the performance of my EF Code First app? Please keep in mind that I haven't had a chance yet to read up on EF in much detail. I'm mostly just trying to migrate as quickly and painlessly as possible from LINQ to SQL to EF. The most useful answer for me would be one that consists of changing specific strategies or configurations or other settings.

Answer 1

http://blogs.msdn.com/b/wriju/archive/2011/03/15/ado-net-entity-framework-performance-tips.aspx

Answer 2

Final note: I'm using a repository pattern as a mediator, and each usage of a repository is placed in a using block. For "get" operations, this results in entities becoming detached once I've obtained the data I need from the database.

Well this is not required...

1. Entity Framework's default architecture already implements repository pattern.
2. Keeping ObjectContext alive does not mean you are keeping your connection to database alive.
3. Only when you are loading from or saving changes to database, a new connection from connection pool is grabbed and operation is performed.

Ofcourse, using block will slow down because each using block will do following,

1. Initialize Context (requires loading metadata from resources)
2. Validate few things
3. Open connection to DB
4. Perform your tasks
5. Clean up and close DB

Now first two steps sure will take lot of time, and you will have multiple objects of same type living longer in your app because each new context will create a new copy of same object for every query.

Entity Framework already implements Identity Map, that means that it will keep the object alive and only one copy of object for same primary key throughout the lifetime of context, that will not only save memory but will also perform faster.

I would advise to not use Using blocks for every query or smaller steps but rather, you should keep your ObjectContext alive throughout lifetime of your application. And you do not need to implement caching or repository at all.

Answer 3

Read here and here about internal working of Entity framework. It is related to EFv4 and ObjectContext API but EFv4.1 with DbContext API is just wrapper around EFv4.

If you feel that your query is slow, try to execute it twice on the same context and twice on different instances of the context. The first test will check if the problem is in object materialization because objects will be materialized only for the first query and the second test will check if there is any problem with context initialization (this should not happen if you are using standard context creation with connection string).

It would be also interesting to compare execution with compiled query but I have a feeling that compiled queries are not part of DbContext API.

Answer 4

When you have changed to a Code First approach, has this changed the structure of the database?

My guess is yes and this is what is causing the change in performance.

I also noticed on thing in your class, you have:

public int TrialDefinitionId { get; set; }

and:

public virtual TrialDefinition TrialDefinition { get; set; }

Are both of these required?