How to optimize performance of .net 5 application when working with mysql using Entity Framework Сore

Question

My .net 5 application uses mysql database. The work is done using the ORM Entity framework Core 5.0.11 and Pomelo.EntityFrameworkCore 5.0.2 Recently, I've been thinking about query performance. The same query sent with applications and directly with mysql client (used workbench) has different execution time. As a rule, requests from the workbench are executed two / three times faster.

Example:

_dbContext.SharepointFile.Create(
    new SharepointFile
    { 
        SharepointFileId = driveItem.Id,
        EmbedUrl = model.Link,
        Hash = hash,
    });
await _dbContext.SaveAsync();

Converted to a query like:

INSERT INTO `SharepointFiles` (`EmbedUrl`, `Hash`, `SharepointFileId`)
      VALUES (@p0, @p1, @p2);
      SELECT `Id`
      FROM `SharepointFiles`
      WHERE ROW_COUNT() = 1 AND `Id` = LAST_INSERT_ID();
  

This request from the application completes in ~0.58 sec on average. The same request sent from the Workbench client completes in ~0.2 sec.

Another example of a simple data fetch:

SELECT `s`.`Id`, `s`.`EmbedUrl`, `s`.`Hash`, `s`.`SharepointFileId`
      FROM `SharepointFiles` AS `s`
      WHERE `s`.`Hash` = @__hash_0
      LIMIT 1
  

Runs from application in ~0.25 sec. From workbench in ~0.065sec.

As you can see, these are simple, ordinary queries. Tell me what path to look for to optimize our application. Perhaps there are thoughts about setting up a connection to mysql. I will provide details if needed.

Answer 1

When running a query in workbench will issue query against RDBMS and execute it there, so you're comparing after all running on the server to being apart, there will be difference.

That mentioned, when you do select, keep in mind if you need to modify data, and when not always use what Sebastian suggested with turning tracking off, see this performance piece on this, https://www.c-sharpcorner.com/UploadFile/ff2f08/entity-framework-and-asnotracking/

.AsNoTracking()

you really should be doing this a lot and I'd suggest doing that and then see where you are, remember to measure first and I bet you can document already huge improvements, and You will have achieved a lot if asked to improve performance.

If wanting to do more, consider making procedures, they can be called from EF.Core even if that will work very well in terms of using the effeciency of one with databinding capabilites of the other e.g.

_context.Set<Model>().FromSqlRaw("CALL spProcedure({0});", parametervalue).ToList<Model>();