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

本文关键字：数据 性能 导入 SQLite 使用 | 更新日期: 2023-09-27 18:14:04

我正在使用c#导入一个600 - 800万行的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上处理600万行需要2分55秒;4 gb RAM。这还不算太糟，但我只是想知道是否有人能找到一种更快导入它的方法。

下面是我的代码: 
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();

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

对于600万条记录来说，这是相当快的。

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

http://www.sqlite.org/faq.html问题19

实际上，在一台普通的台式计算机上，SQLite每秒可以轻松地执行50,000条或更多的INSERT语句。但它每秒只能处理几十个事务。交易速度受到磁盘驱动器转速的限制。一个事务通常需要对磁盘盘进行两次完整的旋转，这在一个7200RPM的磁盘驱动器上限制了您每秒大约60个事务。

将您的时间与上面所述的平均值进行比较:50,000/秒=>这应该要花200秒，只比你的时间快一点点。

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

默认情况下，每个INSERT语句都是自己的事务。但是如果用BEGIN包围多个INSERT语句…COMMIT，然后将所有插入分组到单个事务中。提交事务所需的时间平摊在所有包含的插入语句上，因此每个插入语句的时间大大减少。

下一段有一些提示，你可以试着加快插入的速度:

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

我一直认为SQLite是为"简单的事情"设计的，600万条记录对我来说似乎是一些真正的数据库服务器，比如MySQL的工作。

在SQLite中有这么多记录的表中计数记录可能需要很长时间，只是为了您的信息，而不是使用SELECT COUNT(*)，您可以始终使用SELECT MAX(rowid)，这是非常快的，但如果您要删除该表中的记录，则不那么准确。

编辑。

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

您可以尝试在数据插入之后创建索引——通常，数据库在一次操作中构建索引比在每次插入(或事务)之后更新索引要快得多。

我不能说它一定能与SQLite一起工作，但因为它只需要两行来移动，所以值得一试。

我也想知道如果600万行事务可能走得太远-你能改变代码来尝试不同的事务大小吗?比如说100 1000 10000 100000?有没有一个"最佳点"?

通过以下方式绑定参数可以获得相当多的时间:

...
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节中进行更改。这样，参数绑定似乎要快得多。特别是当您有很多参数时，这种方法可以节省相当多的时间。

我做了一个类似的导入，但是我让我的c#代码首先将数据写入csv，然后运行sqlite导入实用程序。通过这种方式，我可以在大约10分钟内导入超过3亿条记录。

不确定这是否可以直接从c#中完成。

相关文章：