EF Core and MySql query is too slow

c# ef-core-2.0 entity-framework-core linq mysql

Question

I have 2 tables named markets and exchanges in mysql.
There are approximately 1.5 million records in the exchanges table.
I would like to get records from exchanges table with the highest update date of each market. As a result of the query, I want to return a list with only one recent update for each market.
I created a query like the following with linq. But he responds very slowly.
Approximately 7-8 minutes. How do I fix this problem?

Thanks for your answers.

var query = (from exchange in _context.Exchanges
             join market in _context.Markets on exchange.MarketId equals market.Id
             where market.TypeId == 1
             group exchange by exchange.MarketId into grp

             let maxdate = grp.Max(x => x.LastUpdatedDateTime)

             from exchanges in grp
             where exchanges.LastUpdatedDateTime == maxdate
             select new DtoGetAllMarketsWithLastExchanges
             {
                 Id = exchanges.MarketId,
                 Code = exchanges.Markets.Code,
                 Name = exchanges.Markets.Name,
                 LastBuyPrice = exchanges.LastBuyPrice,
                 LastSellPrice = exchanges.LastSellPrice,
                 SeoUrl = exchanges.Markets.SeoUrl,
                 Icon = exchanges.Markets.Icon,
                 LastUpdateDate = exchanges.LastUpdatedDateTime,
                 Rate = exchanges.Rate
             }).ToList();

Markets Entity Class;

[Table("Markets")]
public partial class Markets : BaseEntity
{
    public int TypeId { get; set; }

    public string Code { get; set; }

    public string Name { get; set; }

    public byte Unit { get; set; }

    public int OrderNumber { get; set; }

    public string Icon { get; set; }

    public string SeoUrl { get; set; }

    public virtual List<Exchanges> Exchanges { get; set; }
}

Exchanges Entity Class;

[Table("Exchanges")]
public partial class Exchanges : BaseEntity
{
    public int MarketId { get; set; }

    public double LastBuyPrice { get; set; }

    public double LastSellPrice { get; set; }

    public double Rate { get; set; }

    public DateTime CreatedDateTime { get; set; }

    public DateTime LastUpdatedDateTime { get; set; }

    [ForeignKey("MarketId")]
    public virtual Markets Markets { get; set; } 

}
1
2
8/18/2019 12:31:56 PM

Accepted Answer

The query is slow because it uses currently unsupported constructs which cause client evaluation. Client evaluation is quite inefficient with big data, which is one of the reasons it will be removed in EF Core 3.0.

So the trick is to find LINQ construct which is supported (translated to SQL and executed server side). Since every EF Core version supports different constructs, I can't say exactly which one is supported in 2.0, but in latest 2.2 and in general, try following the next simple rules:

  • Use navigation properties over manual joins where possible
  • Avoid GroupBy where possible.

And always check for client evaluation warnings. In 3.0+ they will be runtime exceptions anyway, so you have to find a translatable construct.

Here are some of the functionally equivalent LINQ queries which work in 2.2.6:

(1) With Max criteria

from market in _context.Markets
where market.TypeId == 1
from exchange in market.Exchanges
where exchange.LastUpdatedDateTime == market.Exchanges.Max(e => (DateTime?)e.LastUpdatedDateTime)
select new DtoGetAllMarketsWithLastExchanges
{
    Id = market.Id,
    Code = market.Code,
    Name = market.Name,
    LastBuyPrice = exchange.LastBuyPrice,
    LastSellPrice = exchange.LastSellPrice,
    SeoUrl = market.SeoUrl,
    Icon = market.Icon,
    LastUpdateDate = exchange.LastUpdatedDateTime,
    Rate = exchange.Rate
}

(2) Same as (1), but with !Any (SQL NOT EXISTS) criteria:

where !market.Exchanges.Any(e => e.LastUpdatedDateTime < exchange.LastUpdatedDateTime)

(3) Same as (1), but with join to Max subquery:

from market in _context.Markets
where market.TypeId == 1
from exchange in market.Exchanges
join lastExchange in _context.Exchanges
    .GroupBy(e => e.MarketId)
    .Select(g => new { MarketId = g.Key, Date = g.Max(e => e.LastUpdatedDateTime) })
on new { exchange.MarketId, Date = exchange.LastUpdatedDateTime }
equals new { lastExchange.MarketId, lastExchange.Date }
select new DtoGetAllMarketsWithLastExchanges
{
    Id = market.Id,
    Code = market.Code,
    Name = market.Name,
    LastBuyPrice = exchange.LastBuyPrice,
    LastSellPrice = exchange.LastSellPrice,
    SeoUrl = market.SeoUrl,
    Icon = market.Icon,
    LastUpdateDate = exchange.LastUpdatedDateTime,
    Rate = exchange.Rate
}
3
8/18/2019 12:20:29 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow