C# / EF Core: How can the performance of this code / queries be improved?

user_19667292 0
Viewed 86

I am trying to implement a auto-trading bot and want to backtest its performance on historical market data. This function mocks the real one and presents the "current" prices:

public DateTime Current = new DateTime(2022, 08, 05, 8, 0, 0);
public async Task<List<Quote>> GetLatestQuotes(List<string> isins)
{
    using var context = _factory.CreateDbContext();
    List<Quote> quotes = new List<Quote>();
    foreach (var isin in isins)
    {
        var stockPrice = context.HistoricalMinutePrices.Where(x => x.Stock.Isin == isin && x.Time <= Current).OrderByDescending(x => x.Time).FirstOrDefault();
        if (stockPrice is null)
            continue;
        var quote = new Quote { Time = stockPrice.Time, Ask = stockPrice.Ask, Bid = stockPrice.Bid, Isin = isin };
        quotes.Add(quote);   
    }
    Current = Current.AddMinutes(1);
    Console.WriteLine($"[{Current:g}]");
    return quotes;
}

It gets the "latest" price entry for each stock in the database. "Latest" refers to the DateTime that moves by 1 minute each iteration.

Now the problem is, that I use a SQLite DB and it contains a lot of entries even for only a day of data: 100 stocks * 60 minutes * 24 hours = 144.000 entries a day. I think the OrderBy takes a lot of performance and the fact that it fires 100 single sqls. How this can be improved?

The Database is available local on the machine

entity-framework-core query-optimization entity-framework linq c#
2 Answers

I would propose to use linq2db.EntityFrameworkCore (note that I'm one of the creators)

The you can use Window Functions:

public async Task<List<Quote>> GetLatestQuotes(List<string> isins)
{
    using var context = _factory.CreateDbContext();

    var query = context.HistoricalMinutePrices
        .Where(x => isins.Contains(x.Stock.Isin) && x.Time <= Current)
        .Select(x => new 
        {
            RN = Sql.Ext.RowNumber().Over()
                .PartitionBy(x.Stock.Isin)
                .OrderByDesc(x.Time)
                .ToValue(),
            Record = x
        })
        .Where(x => x.RN == 1)
        .Select(x => x.Record);
    
    var quotes = await query.ToListAsyncLinqToDB();

    Current = Current.AddMinutes(1);
    Console.WriteLine($"[{Current:g}]");
    return quotes;
}

Also ensure that you have index on column Time

user_106463160

I found a solution that works quite well for now:

public DateTime Current = new DateTime(2022, 08, 05, 8, 0, 0);
private List<Quote> _quotesInDay;
private List<Quote> _quotesInHour;
public async Task<List<Quote>> GetLatestQuotes(List<string> isins)
{
    if (Current.Hour == 22)
        Current = Current.AddHours(10);
    if (_quotesInDay is null || _quotesInDay.First().Time.Day != Current.Day)
    {
        using var context = _factory.CreateDbContext();
        _quotesInHour = null;
        _quotesInDay = context.HistoricalMinutePrices.Where(x => x.Time.Day == Current.Day).Select(x => new Quote { Time = x.Time, Ask = x.Ask, Bid = x.Bid, Isin = x.Stock.Isin }).ToList();
    }
    if (_quotesInHour is null || _quotesInHour.First().Time.Hour != Current.Hour)
        _quotesInHour = _quotesInDay.Where(x => x.Time >= Current.AddMinutes(-10) && x.Time < Current.AddMinutes(60)).OrderByDescending(x => x.Time).ToList();

    List<Quote> quotes = new List<Quote>();
    foreach (var isin in isins)
    {
        var quote = _quotesInHour.FirstOrDefault(x => x.Isin == isin && x.Time <= Current);
        if (quote is null)
            continue;
        quotes.Add(quote);
    }
    Current = Current.AddMinutes(1);
    Console.WriteLine($"[{Current:g}]");
    return quotes;
}

I load the whole data for one day at the beginning of each day in memory at once. Then I chunk this data into groups of one hour. This hourly data is sorted only once by time.

This way only one sql is fired for each day (compared to ~100k before) and way less sorting happens.

user_196672920
Related