Why a first request take more time?

.net-core c# ef-core-2.1 entity-framework-core sql-server

Question

Today in some experiment I noticed an interesting thing:

var dbContextOptionsBuilder = new DbContextOptionsBuilder<MyContext>();
dbContextOptionsBuilder.UseSqlServer(@"Data Source=LAPTOP-HBBAKRHO\SQLEXPRESS;Initial Catalog=myDb;Integrated Security=True");
var context = new MyContext(dbContextOptionsBuilder.Options);

Stopwatch stopWatch;

stopWatch = Stopwatch.StartNew();
context.Projects.AsNoTracking().SingleOrDefault(p => p.Id.Equals(12345));
stopWatch.Stop();
Debug.WriteLine($"AsNoTracking().SingleOrDefaultAsync, by ID: {stopWatch.ElapsedMilliseconds}");

stopWatch = Stopwatch.StartNew();
context.Projects.AsNoTracking().SingleOrDefault(p => p.Id.Equals(12345));
stopWatch.Stop();
Debug.WriteLine($"AsNoTracking().SingleOrDefaultAsync, by ID: {stopWatch.ElapsedMilliseconds}");

stopWatch = Stopwatch.StartNew();
context.Projects.AsNoTracking().SingleOrDefault(p => p.Id.Equals(12345));
stopWatch.Stop();
Debug.WriteLine($"AsNoTracking().SingleOrDefaultAsync, by ID: {stopWatch.ElapsedMilliseconds}");

// CLOSE.
context.Dispose();

Results:

  • AsNoTracking().SingleOrDefaultAsync, by ID: 2457
  • AsNoTracking().SingleOrDefaultAsync, by ID: 51
  • AsNoTracking().SingleOrDefaultAsync, by ID: 29

As you can see, the first request always takes more time. Why does this happen?

I thought ORM open/close database connection for each request, maybe it is not that and EF Core open connection only for first time and use it for all next requests until DbContext dispose of?

1
1
4/1/2019 4:40:32 AM

Accepted Answer

You are getting data by the same parameter from the same context instance each time, therefore second and 3rd get request will not go to SQL server at all. EF has it's own first-level cache of all the entities its loads from the database. That's why second and 3rd times are much faster. If you change parameter 12345 for second query to any other, it will be faster, than first one, but not so fast, because it will request data from the server.

I would advice you to read these topics on EF executions and performance:

managing ef in a right way

the query plan cache story

1
4/1/2019 5:43:13 AM

Popular Answer

The first request is slower because Entity Framework has to create the mapping views from your model. This is called "compiling your model". This happens when you execute your first query.

So in your experiment the first query should be slow even if it doesn't return any records.

So if you try something like this:

stopWatch = Stopwatch.StartNew();
context.Projects.AsNoTracking().SingleOrDefault(p => p == -1); // record does not exist
stopWatch.Stop();
Debug.WriteLine($"AsNoTracking().SingleOrDefaultAsync, by ID: {stopWatch.ElapsedMilliseconds}");

stopWatch = Stopwatch.StartNew();
context.Projects.AsNoTracking().SingleOrDefault(p => p == -2); // record does not exist
stopWatch.Stop();
Debug.WriteLine($"AsNoTracking().SingleOrDefaultAsync, by ID: {stopWatch.ElapsedMilliseconds}");

The first query will be much slower than the second one even though it doesn't return any records.

When you now take another entity from your context (let's imagine you have a dbset called employees in your context) and execute the first query against this dbset and execute the queries against projects after that, you'll see that the projects queries will run much faster.

stopWatch = Stopwatch.StartNew();
context.Employees.AsNoTracking().SingleOrDefault(e => e == -1); // record does not exist
stopWatch.Stop();
Debug.WriteLine($"AsNoTracking().SingleOrDefaultAsync, by ID: {stopWatch.ElapsedMilliseconds}");


stopWatch = Stopwatch.StartNew();
context.Projects.AsNoTracking().SingleOrDefault(p => p == -1); // record does not exist
stopWatch.Stop();
Debug.WriteLine($"AsNoTracking().SingleOrDefaultAsync, by ID: {stopWatch.ElapsedMilliseconds}");

stopWatch = Stopwatch.StartNew();
context.Projects.AsNoTracking().SingleOrDefault(p => p == -2); // record does not exist
stopWatch.Stop();
Debug.WriteLine($"AsNoTracking().SingleOrDefaultAsync, by ID: {stopWatch.ElapsedMilliseconds}");

One way to get around this issue is to execute a "fake" query during startup so that the first user executed query won't be slow



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow