C#/EF Core性能优化：如何提升自动交易回测行情查询效率？

一、数据库索引优化 #

索引是解决SQLite查询性能问题的核心，针对你的查询场景，需创建以下索引：

• 给Stock表的Isin字段添加唯一索引，加速关联查询的匹配速度：

  CREATE UNIQUE INDEX IX_Stock_Isin ON Stock(Isin);
  

• 给HistoricalMinutePrices表创建复合索引，覆盖查询过滤和排序需求，避免全表扫描：
  若保留关联查询逻辑，创建包含StockId和Time的复合索引：

  CREATE INDEX IX_HistoricalMinutePrices_StockId_Time ON HistoricalMinutePrices(StockId, Time DESC);
  

  更优方案是将Isin字段冗余到HistoricalMinutePrices表（彻底消除关联查询开销），然后创建：

  CREATE INDEX IX_HistoricalMinutePrices_Isin_Time ON HistoricalMinutePrices(Isin, Time DESC);
  

  冗余字段虽增加存储空间，但能大幅降低查询时的IO开销，对回测场景的性能提升尤为显著。

二、EF Core查询优化：批量替代循环单查 #

将循环中每个ISIN单独查询的逻辑，改为一次性批量获取所有ISIN的最新记录，利用SQLite的窗口函数实现分组取最新数据：

方案1：EF Core LINQ原生实现（EF Core 3.0+支持） #

public DateTime Current = new DateTime(2022, 08, 05, 8, 0, 0);
private readonly YourDbContext _context;

// 回测全程复用DbContext，避免频繁创建销毁的开销
public QuoteService(IDbContextFactory<YourDbContext> factory)
{
    _context = factory.CreateDbContext();
    _context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking; // 关闭跟踪，提升查询速度
}

public async Task<List<Quote>> GetLatestQuotes(List<string> isins)
{
    var quotes = await _context.HistoricalMinutePrices
        .Where(hmp => isins.Contains(hmp.Stock.Isin) && hmp.Time <= Current)
        // 按ISIN分组，每组取时间最新的一条记录
        .GroupBy(hmp => hmp.Stock.Isin)
        .Select(g => g.OrderByDescending(hmp => hmp.Time).FirstOrDefault())
        .Where(hmp => hmp != null)
        .Select(hmp => new Quote 
        { 
            Time = hmp.Time, 
            Ask = hmp.Ask, 
            Bid = hmp.Bid, 
            Isin = hmp.Stock.Isin 
        })
        .ToListAsync();

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

方案2：原生SQL窗口函数（性能最优） #

若EF Core的LINQ转换效率不足，直接使用原生SQL查询（自动参数化，避免SQL注入）：

public async Task<List<Quote>> GetLatestQuotes(List<string> isins)
{
    // 构造参数化的ISIN列表
    var paramNames = isins.Select((_, idx) => $"@isin{idx}").ToList();
    var sql = @"
        SELECT hmp.Time, hmp.Ask, hmp.Bid, s.Isin
        FROM (
            SELECT *, 
                   ROW_NUMBER() OVER (PARTITION BY StockId ORDER BY Time DESC) AS RowNum
            FROM HistoricalMinutePrices
            WHERE Time <= @Current AND StockId IN (
                SELECT Id FROM Stock WHERE Isin IN ({0})
            )
        ) hmp
        JOIN Stock s ON hmp.StockId = s.Id
        WHERE hmp.RowNum = 1";

    // 组装参数
    var parameters = new List<SqliteParameter>
    {
        new SqliteParameter("@Current", Current)
    };
    parameters.AddRange(isins.Select((isin, idx) => new SqliteParameter($"@isin{idx}", isin)));

    var quotes = await _context.Set<Quote>()
        .FromSqlRaw(string.Format(sql, string.Join(", ", paramNames)), parameters.ToArray())
        .ToListAsync();

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

若已将Isin冗余到HistoricalMinutePrices表，可简化SQL，无需关联Stock表：

SELECT Time, Ask, Bid, Isin
FROM (
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY Isin ORDER BY Time DESC) AS RowNum
    FROM HistoricalMinutePrices
    WHERE Time <= @Current AND Isin IN ({0})
) hmp
WHERE RowNum = 1

三、代码逻辑优化 #

• 复用DbContext：回测全程仅创建一次DbContext，关闭实体跟踪（QueryTrackingBehavior.NoTracking），减少上下文初始化和跟踪的开销。
• 禁用延迟加载：在DbContext配置中设置LazyLoadingEnabled = false，避免查询时触发N+1关联查询。
• 内存预加载（终极优化）：若回测数据量在内存可承受范围内，提前将所有需要的行情数据加载到内存结构中，彻底规避数据库IO：

  private Dictionary<string, SortedList<DateTime, HistoricalMinutePrice>> _cachedPrices;
  
  // 初始化时预加载指定时间段的行情数据
  public async Task InitializeCache(List<string> isins, DateTime start, DateTime end)
  {
      var prices = await _context.HistoricalMinutePrices
          .AsNoTracking()
          .Where(hmp => isins.Contains(hmp.Stock.Isin) && hmp.Time >= start && hmp.Time <= end)
          .ToListAsync();
  
      _cachedPrices = prices.GroupBy(hmp => hmp.Stock.Isin)
          .ToDictionary(
              g => g.Key,
              g => new SortedList<DateTime, HistoricalMinutePrice>(g.ToDictionary(p => p.Time, p => p))
          );
  }
  
  // 内存查询版本
  public List<Quote> GetLatestQuotes(List<string> isins)
  {
      var quotes = new List<Quote>();
      foreach (var isin in isins)
      {
          if (_cachedPrices.TryGetValue(isin, out var sortedPrices))
          {
              // 二分查找找到小于等于Current的最新时间点
              int index = sortedPrices.Keys.BinarySearch(Current);
              if (index < 0) index = ~index - 1;
              if (index >= 0)
              {
                  var price = sortedPrices.Values[index];
                  quotes.Add(new Quote 
                  { 
                      Time = price.Time, 
                      Ask = price.Ask, 
                      Bid = price.Bid, 
                      Isin = isin 
                  });
              }
          }
      }
      Current = Current.AddMinutes(1);
      Console.WriteLine($"[{Current:g}]");
      return quotes;
  }
  

四、SQLite配置优化 #

调整连接字符串参数，提升SQLite的读写性能：

// 示例连接字符串
"Data Source=yourdb.db;Cache Size=100000;Journal Mode=WAL;Synchronous=Normal"

• Cache Size：增大缓存页数量，减少磁盘IO
• Journal Mode=WAL：启用预写日志，提升并发读写性能
• Synchronous=Normal：降低同步级别，平衡性能与数据安全性

内容的提问来源于stack exchange，提问作者tailoxyn