About DbContext first query slow problem

[linqingfeng]

hi,

In EF6, DbContext the first query will be very slow, even after spend several ways, still need n seconds, such as:
https://www.fusonic.net/en/blog/3-steps-for-fast-entityframework-6.1-code-first-startup-performance/

Finally, we can only use less elegant way to "warm" DbContext.
That is, before the user when the application starts for the first time queries to "warm-up" DbContext.
However, we project there are many DbContext, so we have been very upset this problem.

In EF7, DbContext the first query a lot faster, but still needs to n seconds. With respect to the need for only a few milliseconds of the second query, it is still very slow.

Is there any way to solve this problem?

thank you very much!

[rowanmiller]

If you have followed the steps in the link you included then there isn't much else that can be done to improve warm up time in EF6.x.

Can you share specifics about what you are seeing in EF7 (i.e. what your code looks like and what sort of numbers you are seeing). It is expected that the first time you run a query it will be slower, since EF needs to translate your LINQ statement into the equivalent SQL. After the first time, the translation is cached and the query will execute faster. We'd need to see specific numbers though to know if what you are seeing is a bigger difference than we would expect.

[anpete]

Also bear in mind that the first query execution will often cause the loading (and jitting) of the assemblies involved in query execution. NGen'ing could help here.

[linqingfeng]

This is our test code:

Stopwatch stopwatch = new Stopwatch();
stopwatch.Restart();
using (var db = new SingleDynamicDbContext<User>())
{
    List<User> users = db.Set<User>().Where(q => q.Id > 0).ToList();
}
stopwatch.Stop();
Console.WriteLine("User table, the time required for the first query: " + stopwatch.ElapsedMilliseconds + " millisecond."); 

stopwatch.Restart();
using (var db = new SingleDynamicDbContext<User>())
{
    List<User> users = db.Set<User>().Where(q => q.Id > 0).ToList();
}
stopwatch.Stop();
Console.WriteLine("User table, the time required for the second query: " + stopwatch.ElapsedMilliseconds + " millisecond."); 

stopwatch.Restart();
using (var db = new SingleDynamicDbContext<Role>())
{
    List<Role> roles = db.Set<Role>().Where(q => q.Id > 0).ToList();
}
stopwatch.Stop();
Console.WriteLine("Role table, the time required for the first query: " + stopwatch.ElapsedMilliseconds + " millisecond.");

stopwatch.Restart();
using (var db = new SingleDynamicDbContext<User>())
{
    List<User> users = db.Set<User>().Where(q => q.Id > 0).ToList();
}
stopwatch.Stop();
Console.WriteLine("User table, the time required for the third query: " + stopwatch.ElapsedMilliseconds + " millisecond."); 

stopwatch.Restart();
using (var db = new SingleDynamicDbContext<Role>())
{
    List<Role> roles = db.Set<Role>().Where(q => q.Id > 0).ToList();
}
stopwatch.Stop();
Console.WriteLine("Role table, the time required for the second query: " + stopwatch.ElapsedMilliseconds + " millisecond.");

stopwatch.Restart();
using (var db = new SingleDynamicDbContext<User>())
{
    List<User> users = db.Set<User>().Where(q => q.Id > 0).ToList();
}
stopwatch.Stop();
Console.WriteLine("User table, the time required for the fourth query: " + stopwatch.ElapsedMilliseconds + " millisecond.");

Console.ReadLine();

...
...
...

SingleDynamicDbContext is defined as follows:

public class SingleDynamicDbContext<TEntity> : DbContext where TEntity : class
{
    ...
    ...
    ...

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<TEntity>();

        base.OnModelCreating(modelBuilder);
    }
}

Test results are as follows:

EF6 result：
User table, the time required for the first query: 1453 millisecond.
User table, the time required for the second query: 16 millisecond.
Role table, the time required for the first query: 832 millisecond.
User table, the time required for the third query: 26 millisecond.
Role table, the time required for the second query: 12 millisecond.
User table, the time required for the fourth query: 8 millisecond.

EF7 result：
User table, the time required for the first query: 728 millisecond.
User table, the time required for the second query: 8 millisecond.
Role table, the time required for the first query: 17 millisecond.
User table, the time required for the third query: 1 millisecond.
Role table, the time required for the second query: 3 millisecond.
User table, the time required for the fourth query: 2 millisecond.

Well obviously, "EF7" The performance was good "EF6" performance ratio. However, as the test can see, still have to "warm-up", the first query is still very slow (this is only one case of an entity, there are multiple entities when slower).

Would that be possible that part of the LINQ turn SQL coding work without time-consuming to cache?
Even without a code generator, as long as the sample template, we can write their own code generator.

thank you very much!

[davidroth]

@linqingfeng Have you measured these results with ngend EF6/7 or do these results include JIT time?
To avoid Jit use one of these commands to ngen the binaries and install them in the native image cache.

EF6:
%WINDIR%\Microsoft.NET\Framework\v4.0.30319\ngen install EntityFramework.dll
EF7:
%WINDIR%\Microsoft.NET\Framework\v4.0.30319\ngen install EntityFramework.Core.dll %WINDIR%\Microsoft.NET\Framework\v4.0.30319\ngen install EntityFramework.SqlServer.dll ...

[ErikEJ]

@davidroth I do not think that ngen will install into the GAC (which should be avoided) https://msdn.microsoft.com/en-us/data/dn582034

[davidroth]

@ErikEJ Yep you are absolutely right. I have updated my comment.

[davidroth]

@rowanmiller: Does EFCore have the ability to cache the model into a file and load it during next startup without calling OnModelCreating()? (similar to the cached db model store in EF6).
I know that 1st query perf should be much better in EFCore, but I guess the impact will be still high when using it with very large models.

[linqingfeng]

@davidroth

Use "ngen" after, e.g.

%WINDIR%\Microsoft.NET\Framework64\v4.0.30319\ngen install EntityFramework.Core.dll
%WINDIR%\Microsoft.NET\Framework64\v4.0.30319\ngen install EntityFramework.MicrosoftSqlServer.dll

EF7 test results:
User table, the time required for the first query: 454 millisecond.
User table, the time required for the second query: 34 millisecond.
Role table, the time required for the first query: 32 millisecond.
User table, the time required for the third query: 5 millisecond.
Role table, the time required for the second query: 8 millisecond.
User table, the time required for the fourth query: 6 millisecond.

Some test results are fast, but still very slow.

@rowanmiller EF7 can provide such a function:

The LINQ translated into SQL cache written to a local file, allowing users to set up, read, or to read the file cache to speed up the startup speed?

[rowanmiller]

Just one more thing to point out, the first query in this example not only includes the time to translate to SQL but also the time to build the model (since this is the first operation for the context in the application). If you ran a different query after this one, there would still be some overhead on the first one (for the translation to SQL) but it would be significantly less since the model would not need to be re-built.

Does EFCore have the ability to cache the model into a file and load it during next startup without calling OnModelCreating ()? (similar to the cached db model store in EF6).

Not yet, but the feature is tracked by #1906

The LINQ translated into SQL cache written to a local file, allowing users to set up, read, or to read the file cache to speed up the startup speed?

Really not sure that this will actually be needed. As I mentioned, a large part of the overhead in your example if probably in the model builder rather than SQL translation.

[SenenFernandez]

linqingfeng, are you aware of the time to create TCP socket, firewall verification, database security, etc. is taken between server and client, after that your first query is still slow?

[anpete]

To add to what @SenenFernandez said, it would be great if you could profile your app so that we can really understand where the slowness is on the first query. One thing that stands out is that you have only ngen'd a subset of the assemblies that will be loaded on the first query. You should use something like the Fusion log viewer to understand assembly loading.

[linqingfeng]

@SenenFernandez @anpete This test environment has to be simple, using only local database connection test.

Under the same environment, with pure ADO.NET test code is as follows:

stopwatch.Restart();

String connString = ConfigurationManager.ConnectionStrings["FrameWork"].ConnectionString;

List<User> userList = new List<User>();

using (SqlConnection conn = new SqlConnection(connString))
{
    try
    {
        conn.Open();

        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandText = "select * from [FrameWork].[User] where Id > @Id";
            cmd.CommandType = CommandType.Text;
            cmd.Connection = conn;
            cmd.Parameters.Add("@Id", SqlDbType.Int).Value = 0;

            using (IDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    User user = new User();

                    user.Id = dr["Id"] != System.DBNull.Value ? (int)dr["Id"] : 0;
                    user.Code = dr["Code"] != System.DBNull.Value ? dr["Code"].ToString() : string.Empty;
                    ...
                    ...
                    ...
                    userList.Add(user);
                }
            }
        }
        conn.Close();
    }
    catch (Exception err)
    {
        throw err;
    }
}

stopwatch.Stop();
Console.WriteLine("User table, the time required for the ado.net query: " + stopwatch.ElapsedMilliseconds + " millisecond.");

Test Results:

User table, the time required for the ado.net query: 9 millisecond.

@rowanmiller

As tested, the direct use of pure ADO.NET, the code is extremely ugly, but the performance is best (only with "EF" The first query comparison). Tell the truth, in fact, I have used the "EF" in many projects, there has been no big problem. Recently, however, a need to connect more than one database project, and the project at boot time, it is necessary to connect the database, so the first query slow problem has plagued us. So we hope that the new generation of "EF" to solve this problem, whether it is read from the cache file, or as right as other developers compilation model. We do not pray to be able to achieve the same ADO.NET as fast, but sincerely hoping that a few hundred milliseconds down to less than 100 milliseconds.

thank you very much!

[rowanmiller]

Closing this out as we have #1906 tracking the ability to cache a compiled version of the model and model creation is likely the vast majority of the time seen on the first query in the provided numbers.