返回小数据表时的 C# OutOfMemory 异常 [英] C# OutOfMemory exception when returning a small DataTable
问题描述
我有一个 SQLite 数据库，它有一个包含 1800 万行和 24 列的表.我用 C# 编写了一个 SQL 查询函数，我用 ExcelDNA 将其公开给 Excel.
I have a SQLite database that has a single table with 18 million rows and 24 columns. I have written a SQL query function in C#, which I expose to Excel with ExcelDNA.
这是完整的代码，
string constr = constr = "Data Source=" + FilePath + ";Version=3;Synchronous=OFF;temp_store=memory;cache_size=700000;count_changes=off;";
DataTable dt = new DataTable();
try
{
SQLiteConnection conn = new SQLiteConnection(constr);
SQLiteCommand command = new SQLiteCommand(SQLStatement,conn);
conn.Open();
SQLiteDataAdapter sda = new SQLiteDataAdapter(command);
sda.Fill(dt);
sda.Dispose();
command.Dispose();
conn.Dispose();
int numRows = (IncludeHeaders ? dt.Rows.Count + 1 : dt.Rows.Count);
object[,] ret = new object[numRows, dt.Columns.Count];
int rowCount = 0;
if (IncludeHeaders)
{
int colCount = 0;
foreach (DataColumn col in dt.Columns)
{
ret[rowCount, colCount] = col.ColumnName;
colCount++;
}
}
rowCount = (IncludeHeaders ? 1 : 0);
foreach (DataRow row in dt.Rows)
{
int colCount = 0;
foreach (DataColumn col in dt.Columns)
{
if (row[col] != DBNull.Value)
ret[rowCount, colCount] = row[col];
else
ret[rowCount, colCount] = "";
colCount++;
}
rowCount++;
}
return ret;
}
catch (Exception ex)
{
object[,] err = new object[1, 1];
err[0, 0] = ex.ToString();
return err;
}
finally
{
dt.Clear();
dt.Dispose();
dt = null;
}
如果我运行查询两次(按两次 Shift+F9)，我会收到 OutOfMemoryException.在任务管理器中，我可以看到 EXCEL.EXE 图像的工作集(内存)在抛出异常之前从 200MB 变为 1500MB.
If I run the query twice (two hits of Shift+F9), I get an OutOfMemoryException. In the Task Manager I can see the Working Set(Memory) of the EXCEL.EXE image go from 200MB to 1500MB before the exception is thrown.
然而，这种行为并不完全一致.我返回最多 5 列和 1100 行的其他查询工作正常.我看到任务管理器中的内存使用量增加，一旦结果返回到 Excel，我看到内存又回来了.
However, this behavior isn't entirely consistent. Other queries where I return upto 5 columns and 1100 rows work just fine. I see the memory usage tick up in the Task Manager and once the results are returned to Excel I see the memory come back down.
调试上面的应用程序显示它在 sda.Fill(dt) 行跳闸.
Debugging the application above shows that it trips up at the sda.Fill(dt) line.
会欣赏任何想法吗?我最好使用 SQLiteDataReader 代替吗?或者我可以使用其他任何提示或技巧吗?谢谢.
Would appreciate any thoughts? Would I be better off using SQLiteDataReader instead? Or are there any other tips or tricks I can use? Thank you.
顺便说一句，如果我通过 Python 运行确切的查询，我不会遇到这个问题，所以我认为这与 C# 中的垃圾收集有关.
Incidentally if I run the exact query via Python I don't get this problem, so I'd assume it's something to do with the garbage collection in C#.
以下是有关数据库和查询的一些详细信息.架构是沿着，
Here are some details on the database and query. The schema is along the lines of,
Date (VARCHAR)
CompanyName (VARCHAR)
Amount (REAL)
AggCode (VARCHAR)
Level1 ... Level20 (VARCHAR)
查询通常结合字段Level9、Level5、AggCode、Date、 运行.所以除了原始表，我还配置了以下四个索引，WHERE 子句中的 CompanyName
The queries are usually run combining the fields Level9, Level5, AggCode, Date, CompanyName in the WHERE clause. So apart from the raw table, I have also configured the following four indices,
CREATE INDEX idx1 on my(Level09, AggCode);
CREATE INDEX idx2 on my(Level05, AggCode);
CREATE INDEX idx3 on my(CompanyName, AggCode);
CREATE INDEX idx4 on my(Date, AggCode);
成功返回1100行2列的查询，
The query that returns 1100 rows and 2 columns successfully,
SELECT CompanyName, SUM(Amount) FROM my where Level09="T_EU" and AggCode = "R_S_V" GROUP BY CompanyName ORDER BY SUM(Amount) DESC
抛出内存异常的查询，
SELECT Date, CompanyName, Sum(Amount) FROM my WHERE Level05 ="M_TO" AND AggCode = "C_DTA" GROUP BY Date, CompanyName
第二个查询在 Python 中返回 163 行和 3 列.
The second query returns 163 rows and 3 columns in Python.
异常的完整堆栈跟踪如下，
The full stack trace of the exception is below,
System.Data.SQLite.SQLiteException (0x80004005): out of memory
out of memory
at System.Data.SQLite.SQLite3.Reset(SQLiteStatement stmt)
at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
at System.Data.SQLite.SQLiteDataReader.NextResult()
at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at UtilXL.Utils.UtilsSQLite.RunQueryCSLite(String SQLStatement, String FilePath, Boolean IncludeHeaders) in h:\Projects\UtilXL\UtilXL\Utils\UtilsSQLite.cs:line 37
上面引用的第 37 行是 sda.Fill() 调用.
Line 37 in referenced above is the sda.Fill() call.
如果我使用 SqlDataReader 那么它会在 ExecuteReader() 命令处失败，
If I use SqlDataReader then it falls over at the ExecuteReader() command,
System.Data.SQLite.SQLiteException (0x80004005): out of memory
at System.Data.SQLite.SQLite3.Reset(SQLiteStatement stmt)
at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
at System.Data.SQLite.SQLiteDataReader.NextResult()
at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SQLite.SQLiteCommand.ExecuteReader()
at UtilXL.Utils.UtilsSQLite.RunQueryCSReader(String SQLStatement, String FilePath, Boolean IncludeHeaders) in h:\Projects\UtilXL\UtilXL\Utils\UtilsSQLite.cs:line 111
推荐答案
您的代码没有任何可能导致 OutOfMemoryException 的内容.所以可能性是巨大的数据表和内存不能立即恢复.一种可能性是-当您正在阅读 excel.. 它可能在末尾有空行，这可能会增加 dt 大小.....
Your code does not have anything which can cause OutOfMemoryException. So possibility is huge datatable and memory not getting recovered immediately. One possibility is - As you are reading excel.. it may have blank rows at the end, which may potentially can increase dt size.....
由于数据表是托管对象，调用 dispose 不会真正有帮助..但至少每次使用结束后您都可以清除 dt..
As datatable is managed object calling dispose will not really help.. But at lease you can clear dt everytime once it use is over..
这篇关于返回小数据表时的 C# OutOfMemory 异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
