c# - 使用 C# 提高大数据导入 SQLite 的性能

Question

我正在使用 C# 导入一个包含 6-8 百万行的 CSV。

我的表格是这样的:

CREATE TABLE [Data] ([ID] VARCHAR(100)  NULL,[Raw] VARCHAR(200)  NULL)
CREATE INDEX IDLookup ON Data(ID ASC)

我正在使用 System.Data.SQLite进行导入。

目前在 Windows 7 32 位、Core2Duo 2.8Ghz 和 4GB RAM 上执行 600 万行需要 2 分 55 秒。这还不错，但我只是想知道是否有人能找到更快导入它的方法。

这是我的代码:

public class Data
{
  public string IDData { get; set; }
  public string RawData { get; set; }
}   

string connectionString = @"Data Source=" + Path.GetFullPath(AppDomain.CurrentDomain.BaseDirectory + "\\dbimport");
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(connectionString);
conn.Open();

//Dropping and recreating the table seems to be the quickest way to get old data removed
System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand(conn);
command.CommandText = "DROP TABLE Data";
command.ExecuteNonQuery();
command.CommandText = @"CREATE TABLE [Data] ([ID] VARCHAR(100)  NULL,[Raw] VARCHAR(200)  NULL)";
command.ExecuteNonQuery();
command.CommandText = "CREATE INDEX IDLookup ON Data(ID ASC)";
command.ExecuteNonQuery();

string insertText = "INSERT INTO Data (ID,RAW) VALUES(@P0,@P1)";

SQLiteTransaction trans = conn.BeginTransaction();
command.Transaction = trans;

command.CommandText = insertText;
Stopwatch sw = new Stopwatch();
sw.Start();
using (CsvReader csv = new CsvReader(new StreamReader(@"C:\Data.txt"), false))
{
   var f = csv.Select(x => new Data() { IDData = x[27], RawData = String.Join(",", x.Take(24)) });

   foreach (var item in f)
   {
      command.Parameters.AddWithValue("@P0", item.IDData);
      command.Parameters.AddWithValue("@P1", item.RawData);
      command.ExecuteNonQuery();
   }
 }
 trans.Commit();
 sw.Stop();
 Debug.WriteLine(sw.Elapsed.Minutes + "Min(s) " + sw.Elapsed.Seconds + "Sec(s)");
 conn.Close();

Answer 1

这对于 600 万条记录来说已经相当快了。

看来您的做法是正确的，前段时间我在 sqlite.org 上读到，当插入记录时，您需要将这些插入放入事务中，如果不这样做，您的插入将受到限制每秒只有 60 个!这是因为每个插入都将被视为一个单独的事务，并且每个事务都必须等待磁盘完全旋转。你可以在这里阅读完整的解释:

http://www.sqlite.org/faq.html#q19

Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second. Transaction speed is limited by the rotational speed of your disk drive. A transaction normally requires two complete rotations of the disk platter, which on a 7200RPM disk drive limits you to about 60 transactions per second.

将您的时间与上述平均时间进行比较:每秒 50,000 次 => 这应该需要 2 分钟 00 秒。这只比您的时间快一点点。

Transaction speed is limited by disk drive speed because (by default) SQLite actually waits until the data really is safely stored on the disk surface before the transaction is complete. That way, if you suddenly lose power or if your OS crashes, your data is still safe. For details, read about atomic commit in SQLite..

By default, each INSERT statement is its own transaction. But if you surround multiple INSERT statements with BEGIN...COMMIT then all the inserts are grouped into a single transaction. The time needed to commit the transaction is amortized over all the enclosed insert statements and so the time per insert statement is greatly reduced.

下一段中有一些提示，您可以尝试加快插入速度:

Another option is to run PRAGMA synchronous=OFF. This command will cause SQLite to not wait on data to reach the disk surface, which will make write operations appear to be much faster. But if you lose power in the middle of a transaction, your database file might go corrupt.

我一直认为 SQLite 是为“简单的事情”设计的，600 万条记录在我看来是一些真正的数据库服务器(如 MySQL)的工作。

在 SQLite 中计算一个表中有这么多记录的记录可能需要很长时间，仅供引用，而不是使用 SELECT COUNT(*)，您总是可以使用 SELECT MAX(rowid)，它非常快，但不是如果您要删除该表中的记录，那么准确。

编辑。

正如 Mike Woodhouse 所说，在插入记录后创建索引应该会加快整个过程，这是其他数据库中的常见建议，但不能确定它在 SQLite 中的工作原理。