7-second EF startup time even for tiny DbContext

Question

I am trying to reduce the startup time of my EF-based application, but I find that I cannot reduce the amount of time taken for an initial read below 7 seconds even for a single-entity context. What's especially strange is that this time is not context-type specific.

Can anyone explain what causes these slow times and/or how I can get things to run faster?

Here's the complete sample code:

In my database, I have a table named se_stores with a primary key column AptId:

    // a sample entity class
public class Apartment
{
    public int AptId { get; set; }
}

    // two identical DbContexts        

public class MyDbContext1 : DbContext
{
    public MyDbContext1(string connectionString) : base(connectionString)
    {           
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        Database.SetInitializer<MyDbContext1>(null);

        var config = new EntityTypeConfiguration<Apartment>();
        config.HasKey(a => a.AptId).ToTable("se_stores");
        modelBuilder.Configurations.Add(config);

        base.OnModelCreating(modelBuilder);
    }
}

public class MyDbContext2 : DbContext
{
    public MyDbContext2(string connectionString)
        : base(connectionString)
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        Database.SetInitializer<MyDbContext2>(null);

        var config = new EntityTypeConfiguration<Apartment>();
        config.HasKey(a => a.AptId).ToTable("apartments");
        modelBuilder.Configurations.Add(config);

        base.OnModelCreating(modelBuilder);
    }
}

    // finally, I run this code using NUnit:

var start = DateTime.Now;
var apt1 = new MyDbContext1(connectionString).Set<Apartment>().FirstOrDefault();
var t1 = DateTime.Now - start;
start = DateTime.Now;
var apt2 = new MyDbContext2(connectionString).Set<Apartment>().FirstOrDefault();
var t2 = DateTime.Now - start;
Console.WriteLine(t1.TotalSeconds + ", " + t2.TotalSeconds);

It reliably prints something like the following: 7.5277527, 0.060006. When I switch the test to use MyDbContext2 first, I get the same result (so it happens for whichever DbContext gets initialized first). I also tried pre-generating views using EF power tools. This reduced the time for the first context to around 6.8 seconds, and thus was only a small win.

I understand that DateTime.Now is a terrible profiling method, but these results have held up while using dotTrace. I'm also aware that running some code for the first time invokes a JITing cost, but 7 seconds seems far too high to attribute to that.

I am using EF 4.3.1 and .NET 4 with VS 2010.

Thanks in advance for your help!

EDIT: It was suggested that opening the SQL connection might be causing the problem.

1. I first tried running a random query using a raw SqlConnection and create command with the same connection string. This took 1 second and did not affect the time of DbContext initialization.
2. I then tried creating a SqlConnection with the connection string and passing it through to DbContext's constructor that takes a connection. I passed contextOwnsConnection=false. This also made no difference in the DbContext initialization time.
3. Finally, I tried connecting through management studio using the same credentials and connection string options. This was nearly instantaneous.
4. In the dotTrace profile, it measures SqlConnectionFactory.CreateConnection(connectionString) as taking 0.7 seconds, which is consistent with the raw SQL time.

EDIT: I wanted to know if the delay was per connection or once only. Thus, I tried having MyDbContext1 and MyDbContext2 connect to entirely different databases on different servers. This DID NOT make a difference regardless of which database was connected to first: the use of a first DbContext took ~7 seconds, while the use of a second context is blazingly fast.

Answer 1

After taking the code your wrote putting it in its own project I found that the project's platform target had a great effect on the start up time of the EF framework.

When targeting an x64 platform, I received results similar to yours (7 second spin up on the first DbContext and <1 second on the second). When targeting x86 the spin up time for the first DbContext gets reduced by about 4 seconds down to 3.34633 seconds while the second DbContext takes a similar amount of time as in the x64 case.

I am not sure why this happens but it must have to do with how the Entity Framework initializes itself in different environments. I have posted a separate question about that here.

Answer 2

As per several comments, the reason the second runs so fast is due to the connection manager caching the connections. Which leads me to believe that getting that initial connection is the problem.

Things to try:

1. Change your connection string to use the IP address of the db server instead of it's name.

2. Connect to the database server using SQL Management Studio using the exact same credentials and mechanism. Do so from the machine that takes 7 seconds. Assuming that takes awhile, investigate to see what protocols are in use and ensure that the one you want is the first in the list.

3. Put a network analyzer on your machine and watch what it does.