0

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:

  • Canceled the lazyload from edmx schema
  • Refresh the schema

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

  • and it happens every time client goes to the person page
Leon Barkan
  • 2,676
  • 2
  • 19
  • 43
  • What's the SQL that comes out of it? You can turn on logging/sql debugging (I forget offhand how, but it's a one-liner and should be easy to find) – jleach Aug 13 '20 at 13:17
  • The code has to JIT and the mappings are probably checked as well. The DB could also play a factor if it caches the query plan as the result (you can see that with Profiler). – Igor Aug 13 '20 at 13:17
  • Yes, the first call to the context object will run all the model builders and essentially verify the setup, which is a hit, (but shouldn't be a 7.5s hit..) – jleach Aug 13 '20 at 13:18
  • Possibly related [Entity framework very slow to load for first time after every compilation](https://stackoverflow.com/q/30423838/1260204), https://stackoverflow.com/questions/10757019/, https://stackoverflow.com/q/9261095/270591 and https://stackoverflow.com/q/11543990/270591 – Igor Aug 13 '20 at 13:18
  • @Igor i thought that its some kind of inner cache that sees that it's the same query but how it can take so many time in the first time (tried to refresh the edmx) but nothing.. – Leon Barkan Aug 13 '20 at 13:24

1 Answers1

1

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(...))

Tipx
  • 7,367
  • 4
  • 37
  • 59
  • i using database first approach and in my context the OnModelCreating method not implemented throw new UnintentionalCodeFirstException(); – Leon Barkan Aug 13 '20 at 13:43
  • Well, if you want, you can add a breakpoint before your first request, check the SQL Trace and see that it didn't connect just yet. – Tipx Aug 13 '20 at 17:01