Entity Framework on SQL Server CE - lazy vs eager loading, performance considerations

Question

I have an application which is built with Entity Framework. The entities have virtual navigation properties, which means lazy loading. Everything is working, however, I have noticed a performance issue.

The lazy loading on some of the properties causes multiple queries to the database. e.g. if I .Where(...) on a collection of 10 items - it will generate 10 additional calls to the database. Let's say that the SQL time on those 10 calls is a total of 20ms. But the overall time it takes to complete the query is much higher.

If I eager-load with .Include(...), I see a similar SQL time (i.e. 20ms), but the operation completes much faster.

I haven't ran Profiler yet, but I suspect that the bottleneck is opening and closing the SQL Server CE database, or some other similar 'infrastructure' operation.

I really want to use the lazy loading, it make my code much simpler. Is there any way I can optimize the SQL Server CE connection or is there anything I could do at all to increase the performance with SQL Server CE?

My connection string right now

<add name="dataRepositoryConnection" 
     connectionString="Data Source=|DataDirectory|XXX.sdf"  
     providerName="System.Data.SqlServerCe.4.0" />

I have really narrowed down the issue to SQL CE, because when ran against SQL Server, the two scenarios (eager vs lazy loading) give the same performance.

Answer 1

Open a connection to the database in your application startup code, and leave it open for the lifetime of your app. Do not use this connection for any data access. That will open the SQL Compact file and load the SQL Compact dll files at startup (and only at startup).

It is unclear if your application is a web app or desktop app, but you can use code similar to this and call it from Application_Start / App_Startup etc.:

public static class ContextHelper
{
  private static ChinookEntities context ;
  private static object objLock = new object();

  public static void Open()
  {
    lock (objLock)
    {
        if (context != null)
            throw new InvalidOperationException("Already opened");
        context = new ChinookEntities();
        context.Connection.Open();
    }
  }
}

See the deployment section in my blog post here: http://erikej.blogspot.dk/2011/01/entity-framework-with-sql-server.html