针对 SQLite 数据库的 C# 与 Python 查询

Question

我有一个 SQLite 数据库，它有一个包含 1800 万行和 24 列的表。模式是沿着，

Date (VARCHAR)
CompanyName (VARCHAR)
Amount (REAL)
AggCode (VARCHAR)
Level1 ... Level20 (VARCHAR)

我以两种方式查询表 - 首先使用 Python 脚本，然后使用 C# 函数，该函数通过 ExcelDNA 暴露给 Excel(最终我更愿意使用 Excel 运行查询，因为一些查询将返回行需要进一步处理的数据)。

我发现 Python 通常比 Excel 加载项好 3-5 倍，我想知道我的代码是否有问题。下面的示例查询，

query = "SELECT Sum(Amount) FROM my WHERE Level9='STIN_J' AND (AggCode='R_REAL' AND Date='05DEC2016')"

查询通常结合字段 Level9、Level5、AggCode、Date、 运行WHERE 子句中的 CompanyName。所以除了原始表，我还配置了以下四个索引，

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);

这是运行查询的示例 Python 代码，

import sqlite3 as lite
...
con = lite.connect("C:\temp\my.db")
cur = con.cursor()
cur.execute(query)
data = cur.fetchall
for row in data:
    for i in range(len(row)):
        print row[i],
    print "\t",

总的来说这段代码工作得相当好。

这是运行查询的示例 C# 代码，

using System.Data.SQLite;
...
string constr = "Data Source=C:\temp\my.db;Version=3;";
SQLiteConnection conn = new SQLiteConnection(constr);
SQLiteCommand command = new SQLiteCommand(query, conn);                
conn.Open();
SQLiteDataAdapter sda = new SQLiteDataAdapter(command);                
DataTable dt = new DataTable();
sda.Fill(dt);
sda.Dispose();
command.Dispose();
conn.Dispose();

object[,] ret = new object[dt.Rows.Count, dt.Columns.Count];
int rowCount = 0;
int colCount = 0;
foreach (DataRow row in dt.Rows)
{
    foreach (DataColumn col in dt.Columns)
    {
        ret[rowCount, colCount] = col.ColumnName;
        colCount++;
    }
    rowCount++;
}
...
return ret;

Python 或 C# 代码是次优的吗？例如，我应该使用 SQLiteDataReader 而不是 SQLiteDataAdapter 吗？将不胜感激任何想法。

结果集本身非常小，在某些情况下只是一个数字，所以我不会认为 ExcelDNA 会为过程增加空间。示例 Python 查询大约需要 15 秒，而 C# 最多需要 1 分钟。

最后，修改 PRAGMA 设置会如何影响性能？鉴于我的首要任务是查询速度，对某些通用设置有什么建议吗？

此外，对于如何在 Python、C# 中实际实现这些设置或保留它们的任何建议，我们将不胜感激。

Answer 1

我没有正确清除数据表、SQL 命令或连接。一旦我将 Dispose 方法插入到 finally block 中，C# 代码的响应就会急剧增加，并且在大多数情况下都优于 Python。