CODEWITHSUNDEEP
c#sql-serverasp.net-mvc-5entity-framework-6
Leon Barkan
Leon Barkan

Reputation: 2703

Entity Framework first query is much slower than second

In my website, I am using ASP.NET MVC 5 with EF6.

I experience slow performance with only 20K records for the first call.

For example, I need to get all rows from the Person table.

First invocation: 7500 ms (after that in the second row it takes only 1000ms)

List<Person> persons = await _context.Person.ToListAsync(); // Time : 7500ms
List<Person> persons2 = await _context.Person.ToListAsync(); // Time : 1000ms

What I tried:

The same query in SQL Server Management Studio takes 400 ms (and it's a really simple query without joins and conditions)

Upvotes: 0

Views: 979

Answers (1)

Tipx
Tipx

Reputation: 7505

I would have posted this in a comment, but it's too long.

There are many things that can factor into that time difference, in order of less likely/impactful to more likely/impactful:

  • The first query, once in SQL Server (if that's the underlying engine) has to "Warm Up" SQL sometimes. I doubt that this is the actual problem since SQL Server probably hasn't enough time to go "Down" between your tries. Also, the execution plan shouldn't be too problematic for that query.
  • The first query has to open the communication channel. For example, if it has to route through VPNs, or simply open an SQL connection, it adds up a delay.
  • Migrations: Unless you manually force migrations, when you create the DbContext, EF6 doesn't run the migrations (and Seeding) at that moment. It waits for the first time it actually has to query, then builds the configurations and execute migrations.

If you want to investigate, put a breakpoint in the OnModelCreating method and see when it's called. You can also add another query before these two queries to an unrelated entity and you'll see that it's not because of caching (AFAIK, the Caching is only used when using DbSet<T>.Find(...))

Upvotes: 1

Related Questions