调用Dapper的QueryAsync<>()时出现System.OutOfMemoryException问题

压测过程中调用Dapper的QueryAsync<>()方法时出现System.OutOfMemoryException，堆栈信息显示异常发生在List扩容阶段。查询需加载141,846行常规类型数据（无大文本），且必须加载全部数据。

堆栈信息 #

System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.
at System.Collections.Generic.List1.set_Capacity(Int32 value) at System.Collections.Generic.List1.AddWithResize(T item)
at Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command) in C:\projects\dapper\Dapper\SqlMapper.Async.cs:line 442

调用代码 #

const string sql = $@"
  SELECT [Column 1]
        ,[Column 2]
        ,[Column 3]
        ,[Column 4]
        ,[Column 5]
        ,[Column 6]
        ,[Column 7]
        ,[Column 8]
        ,[Column 9]
        ,[Column 10]
        ,[Column 11]
        ,[Column 12]
    FROM [MyDb].[MyTable]
    WHERE co = @CompanyId AND process = @Process
    ";

await using var connection = _dbConnectionProvider.Create(DbKey.MyDb);

var parameters = new { CompanyId = CompanyDbString(companyId), Process = process };
var command = new CommandDefinition(sql, parameters);
var results = await connection.QueryAsync<MyEntity>(command); // 异常触发点

return results;

解决方案 #

你的推测完全正确：List扩容时会申请当前容量2倍的内存，频繁扩容会产生内存碎片，叠加14万条数据的内存占用，最终触发OOM。以下是针对性的解决办法：

1. 预先设置List初始容量（最优解） #

先执行count查询获取准确行数，创建指定初始容量的List，再逐行读取填充，彻底避免扩容操作：

const string countSql = "SELECT COUNT(*) FROM [MyDb].[MyTable] WHERE co = @CompanyId AND process = @Process";
await using var connection = _dbConnectionProvider.Create(DbKey.MyDb);
var parameters = new { CompanyId = CompanyDbString(companyId), Process = process };

// 获取总行数
var totalCount = await connection.ExecuteScalarAsync<int>(countSql, parameters);

// 创建无扩容需求的List
var results = new List<MyEntity>(totalCount);

// 执行查询并映射填充
using var reader = await connection.ExecuteReaderAsync(sql, parameters);
var rowParser = reader.GetRowParser<MyEntity>();
while (await reader.ReadAsync())
{
    results.Add(rowParser(reader));
}

return results;

注意：count查询的过滤条件必须和主查询完全一致，确保行数匹配。

2. 使用内存友好的集合（极端内存优化） #

如果需要极致内存控制，可借助ArrayPool<T>分配数组，避免List的扩容开销：

const string countSql = "SELECT COUNT(*) FROM [MyDb].[MyTable] WHERE co = @CompanyId AND process = @Process";
await using var connection = _dbConnectionProvider.Create(DbKey.MyDb);
var parameters = new { CompanyId = CompanyDbString(companyId), Process = process };

var totalCount = await connection.ExecuteScalarAsync<int>(countSql, parameters);
var arrayPool = ArrayPool<MyEntity>.Shared;
var entityArray = arrayPool.Rent(totalCount);

try
{
    int currentIndex = 0;
    using var reader = await connection.ExecuteReaderAsync(sql, parameters);
    var rowParser = reader.GetRowParser<MyEntity>();
    
    while (await reader.ReadAsync())
    {
        entityArray[currentIndex++] = rowParser(reader);
    }
    
    // 截断数组并转为List返回
    return entityArray.Take(currentIndex).ToList();
}
finally
{
    // 归还数组到池
    arrayPool.Return(entityArray);
}

3. 分页加载合并（内存紧张场景） #

预先创建指定容量的List，分批加载数据并合并，避免一次性申请大内存：

const int pageSize = 10000;
const string countSql = "SELECT COUNT(*) FROM [MyDb].[MyTable] WHERE co = @CompanyId AND process = @Process";
await using var connection = _dbConnectionProvider.Create(DbKey.MyDb);
var parameters = new { CompanyId = CompanyDbString(companyId), Process = process };

var totalCount = await connection.ExecuteScalarAsync<int>(countSql, parameters);
var results = new List<MyEntity>(totalCount);

for (int offset = 0; offset < totalCount; offset += pageSize)
{
    var pageSql = $@"{sql} ORDER BY [Column 1] OFFSET {offset} ROWS FETCH NEXT {pageSize} ROWS ONLY";
    var pageData = await connection.QueryAsync<MyEntity>(pageSql, parameters);
    results.AddRange(pageData);
}

return results;

此方法拆分内存申请为小批次，同时大List无扩容开销，适合内存资源有限的环境。

额外建议 #

• 若为64位应用，可在配置文件中启用gcAllowVeryLargeObjects，允许创建更大的内存对象：

  <configuration>
    <runtime>
      <gcAllowVeryLargeObjects enabled="true" />
    </runtime>
  </configuration>
  

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