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.
This is the complete code,
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;
}
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.
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.
Debugging the application above shows that it trips up at the
`sda.Fill(dt)` line.
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.
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)
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);
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
The query that throws the memory exception,
SELECT Date, CompanyName, Sum(Amount) FROM my WHERE Level05 ="M_TO" AND
AggCode = "C_DTA" GROUP BY Date, CompanyName
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
Line 37 in referenced above is the `sda.Fill()` call.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
