2

I'm porting my Java application to C# and discovered that Entity Framework seems to be really slow when instantiating objects.

First, some details about the versions in use:

  • VS 2019
  • .NET Framework 4.7.2
  • EF 6
  • MySQL 8.0.20

Since I'm porting my application, I used DB First approach.

Now, I've a table with roughly 2000 entries, completely self-contained, nothing special about it. In Java, I manually read in the table and instantiated classes which took around 30ms - still super slow, but good enough. In EF, this takes over a second...

The line in question is:

// _baat2db is my DBContext
_baatc2db.dbexchanges.ToList();

This line always takes over a second to complete.

Using Interceptors, I logged the generated SQL:

SELECT
`Extent1`.`exchangeId`, 
`Extent1`.`mic`, 
`Extent1`.`country`, 
`Extent1`.`city`, 
`Extent1`.`timeZoneId`, 
`Extent1`.`countryIsoCode`, 
`Extent1`.`operatingMic`, 
`Extent1`.`institution`, 
`Extent1`.`acronym`, 
`Extent1`.`website`, 
`Extent1`.`statusDate`, 
`Extent1`.`creationDate`, 
`Extent1`.`comment`, 
`Extent1`.`isOperating`, 
`Extent1`.`isActive`, 
`Extent1`.`calendarId`
FROM `dbexchange` AS `Extent1`
-- Executing at 27.05.2020 14:16:42 +02:00
-- Completed in 3 ms with result: EFMySqlDataReader

So it takes 3ms to read the table data, which is what one would expect. This also leads me to the conclusion that whatever takes so long is not related to the database or the database connection, but rather in the process of processing the returned data, probably when instantiating the objects.

The table fields and their mappings are INT -> int, VARCHAR -> string and BIT -> bool. One is Nullable, two have default values that get set in the generated constructor.

Any ideas what's wrong here? Anything that I should take a look into? What can I do, to further debug the issue?

Thanks!

Update:

As D Stanley suggested, it performs well when calling it a second time (20-25ms).

It's still hard to understand why it takes the eternity of 1 second the first time, since I open the connection before. E.g. following test:

_baatc2db.Database.Connection.Open();

// now, after after StateChange to ConnectionState.Open:

Thread.Sleep(5000);
_baatc2db.dbexchanges.ToList(); // 1+ second

Thread.Sleep(5000);
_baatc2db.dbexchanges.ToList(); // 20-25ms
grasbueschel
  • 879
  • 2
  • 8
  • 24
  • 2
    Does it take over a second _every time_? Often, processes like this have significant startup costs (setting up connections, etc.) that can skew the results for a single run.e What if you loop and create the list, say, 10 times? – D Stanley May 27 '20 at 13:12
  • @DStanley Very good point! Yes, the 2nd time it runs as expected (20ms)!. It's still a bit hard to understand (1sec is really long), since even if I do a `Thread.Sleep` of 5 seconds before to ensure that everything else in the program is idle, it takes over 1sec. I also open the connection manually before, so in this test the connection is up for 5secs already as well. – grasbueschel May 27 '20 at 13:26
  • Far better to use .NET Core and EF Core, since .NET Framework is deprecated and EF 6 is no longer maintained. – Ian Kemp May 27 '20 at 14:28
  • It may not just be the connection - it may be other setup within the EF classes (building metadata, etc.). – D Stanley May 27 '20 at 15:40
  • @IanKemp Thank you! Yes, these steps really helped and therefore answer my question. First, the [EFInteractiveViews](https://github.com/moozzyk/EFInteractiveViews) cache reduced loading time down to 500ms, then [using ngen to precompile Entity Framework](https://learn.microsoft.com/en-us/ef/ef6/fundamentals/performance/ngen) brought loading time down to around 90ms. – grasbueschel May 27 '20 at 20:05
  • Now your question look like this: https://stackoverflow.com/questions/30423838/entity-framework-very-slow-to-load-for-first-time-after-every-compilation – Dmitry May 28 '20 at 05:24

1 Answers1

1

As D Stanley suggested, it performs well when calling it a second time (20-25ms). It's still hard to understand why [...]

You're comparing this with Java, so I'll assume you have experience with it.

The biggest difference with C# is that at run-time it actually compiles the code to native code, which is a non-trivial step. And EF adds an additional chunk of code on top of it, since it compiles the expressions you send to ILASM, then to native code, which is doubly expensive.

The upside is that once the compilation is done, it will run as fast as native code -- even better in fact, since the JIT can take advantage of the machine it's running on to emit assembler instructions other machines might not support (AVX, SSE, etc).

Blindy
  • 65,249
  • 10
  • 91
  • 131