使用C＃优化将大量数据导入SQLite的性能

Question

我正在使用C#导入一个拥有六到八百万行的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();

- Jon

---

你能从数据库以外的卷读取输入文件吗？ - ralf.w.

Answer 1

这对于600万条记录来说非常快。

看起来你正在正确的方式下进行操作，不久前我在sqlite.org上阅读到过，当插入记录时，需要将这些插入操作放置在事务内部。如果不这样做，你的插入操作将被限制为每秒只有60次！这是因为每次插入都将被视为单独的事务，并且每个事务必须等待磁盘完全旋转。你可以在这里阅读完整的解释：

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

实际上，SQLite 在普通台式计算机上可以轻松执行50000个或更多的INSERT语句每秒钟。但它每秒只能执行几十个事务。事务速度受硬盘驱动器的旋转速度限制。一个事务通常需要磁盘盘片的两次完整旋转，这在7200RPM的磁盘驱动器上限制了每秒约60个事务。

将你的时间与上述平均值进行比较：每秒50000个 => 这应该需要2分00秒，而你的时间只比这快一点。

事务速度受磁盘驱动器速度限制，因为（默认情况下）SQLite在事务完成之前实际上会等待数据真正安全地存储在磁盘表面。这样，如果你突然断电或操作系统崩溃，你的数据仍然是安全的。有关详细信息，请阅读关于SQLite中原子提交的内容。

默认情况下，每个INSERT语句都是其自己的事务。但是，如果你用BEGIN...COMMIT包围多个INSERT语句，则所有插入操作将分组为单个事务。提交事务所需的时间被平均分摊到所有封闭的插入语句上，因此每个插入语句的时间大大缩短。

接下来的段落中提供了一些提示，可以尝试加快插入操作：

另一个选项是运行PRAGMA synchronous=OFF。这个命令会导致SQLite不等待数据到达磁盘表面，从而使写操作看起来更快。但是，如果你在一个事务中断电，你的数据库文件可能会变得损坏。

我一直认为SQLite是为“简单事物”设计的，对于超过600万条记录的数据，我认为使用像MySQL这样的真正数据库服务器才是更好的选择。

在SQLite中计算包含如此多记录的表格数量可能需要很长时间。提供一个提示，您可以使用SELECT MAX（rowid）而不是SELECT COUNT（*），后者速度非常快，但如果您在表格中删除了记录，则不太准确。

编辑。

正如Mike Woodhouse所述，在插入记录后创建索引应该会加速整个过程。这是其他数据库的常见建议，但无法确定它在SQLite中的效果。

- Czarek Tomczak

---

我也遇到了在一个简单的数据库中存储大量数据的问题，但我喜欢它的嵌入式特性，唯一的数据查询将是SELECT * FROM TABLE WHERE Id = 'myID'。目前唯一的问题是，如果我尝试每100毫秒执行一次SELECT，它将如何应对。 - Jon

Answer 2

你可以尝试在数据插入之后创建索引——通常情况下，对于数据库来说，构建索引是一个单独的操作比每次插入或事务后更新索引快得多。

我不能确定它是否一定适用于SQLite，但既然只需要两行代码就可以移动，那么值得一试。

我还想知道是否进行600万行事务可能太过了 - 你能否更改代码以尝试不同的事务大小？比如100、1000、10000、100000？有没有“最佳点”？

- Mike Woodhouse

---

@Jon：奇怪。我刚刚插入了1百万行（两次都是空的数据库）：有索引的情况下需要101秒，没有索引的情况下需要64秒，加上索引需要16秒。所以大约快了20%。但是，虽然插入时间与行数大致成线性关系，但索引创建似乎不是这样。因此，在我的100万行和你的600万行之间，随着索引的增加，成本可能会降低。B树索引在O(n)方面可能更糟糕-或者至少在SQLite中看起来是这样。 :-( - Mike Woodhouse

也许有所不同，取决于您： 1）在“插入”事务内部创建索引，但在插入之后 2）在提交“插入”事务后创建索引。 也许你们两个都是用了另一种方式，因此结果不同。 只是一种理论。 - Czarek Tomczak

我将所有插入操作作为单个事务运行，提交后创建索引，这似乎是最优的。有趣的是，插入时间（至少6m行）似乎是线性的，而索引创建时间则不是。因此，对于大量数据，走这条违反直觉的路线可能更有效率（需要更多数据来证实）。 - Mike Woodhouse

Answer 3

你可以通过以下方式绑定参数来节省时间：

...
string insertText = "INSERT INTO Data (ID,RAW) VALUES( ? , ? )";  // (1)

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

command.CommandText = insertText;

//(2)------
   SQLiteParameter p0 = new SQLiteParameter();
   SQLiteParameter p1 = new SQLiteParameter();
   command.Parameters.Add(p0);
   command.Parameters.Add(p1);
//---------

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)
   {
      //(3)--------
         p0.Value = item.IDData;
         p1.Value = item.RawData;
      //-----------
      command.ExecuteNonQuery();
   }
 }
 trans.Commit();
...

在1、2和3部分进行更改。这样，参数绑定似乎会快得多。特别是当你有很多参数时，这种方法可以节省相当多的时间。

- mvermand

Answer 4

我做了类似的导入，但是我让我的C#代码先将数据写入CSV文件，然后运行SQLite导入工具。这样我能够在大约10分钟内导入超过3亿条记录。

不确定是否可以直接从C#中完成此操作。

- Brady Moritz