CODEWITHSUNDEEP
c#mysqlentity-frameworklinqentity-framework-6
Bob Tway
Bob Tway

Reputation: 9603

Entity Framework 6 MySQL - performance difference vs MySQL engine

So there's a few posts around complaining about the performance of the MySQL plugin for Entity Framework 6. Most of these, however, seem to come down to it generating bad SQL. I'm suffering with this issue but it seems to be due to a performance lag due to the plugin itself.

Here's my query in LINQ:

List<Address> matches = _rep.GetAddresses(s => s.AddressKey == cleanAddress).ToList();

And in the repository (_rep) I have this:

public IQueryable<Address> GetAddresses(Expression<Func<Address, bool>> query)
{
    //var foo = Addresses.AsNoTracking().Where(query);
    //var bar = foo.ToString();

    return Addresses.AsNoTracking().Where(query);
}

So I'm already using AsNoTracking to try and improve performance. The commented out lines are there so I can see the SQL that's being generated, which turns out to be:

 SELECT
`Extent1`.`AddressId`, 
`Extent1`.`AddressKey`, 
`Extent1`.`NameKey`, 
`Extent1`.`Title`, 
`Extent1`.`Forename`, 
`Extent1`.`Surname`, 
FROM `Addresses` AS `Extent1`
 WHERE (`Extent1`.`AddressKey` = @p__linq__0) 
 OR ((`Extent1`.`AddressKey` IS  NULL) AND (@p__linq__0 IS  NULL))

Simple enough. Worth noting that AddressKey is a varchar(255) column with an index.

Now, here's the thing. If I stick that query into MySQL workbench and run it (with a varied value of @p__linq__0) it doesn't even register the run time. It lists the duration as 0.000 seconds.

However putting a Stopwatch around my query and logging the time taken to execute the Linq works out around 0.004 seconds. Not a big difference you might think, but this is part of a speed-critical application that runs this code millions of times over. It soon adds up.

I have the same issue with a later block of upsert code. Run natively in workbench and it's under a milisecond. Again, via EF, it takes 3-4 miliseconds.

Am I right in thinking this is down to weak design in the EF MySQL plugin? If so, can I presume that I'll run into the same problem if I try and change this to run via a stored procedure or submitting SQL directly with ExecuteSqlCommand()?

Is there anything else I can try to clear up this performance lag?

Upvotes: 2

Views: 1031

Answers (1)

Jonathan Magnan
Jonathan Magnan

Reputation: 11347

There is a big difference between running a generated query and a LINQ expression.

Let look what do Entity Framework

Convert the LINQ Expression to a DbExpression

This part can sometimes take more time than running the query itself. In some case, with multiple includes, I have seen performance as bad as a few hundreds of milliseconds.

Generate the Query or Take it from cache

It can take some time to generate the SQL Query the first time, but subsequence call will take the query generated from the cache.

The cache uses the DbExpression generated previously to create the cache key.

Execute the Query

Server Latency + Time to run the query

Object Materialization

Time to create entities. Normally very fast since you used AsNoTracking so needed to track them.

I may be wrong, but my guess is most of your time is taken when the LINQ Expression is converted to the DbExpression.

You can easily verify it by verifying the time taken to generate your Query without executing it via ToTraceString method. Be careful, when using directly to TraceString, it doesn't count the time taken by the MySQL Interceptor but you can a rough idea of the time at least.

Here is an example of ToTraceString extension method (I have not tested it): Obtain ToTraceString

Upvotes: 2

Related Questions