title: Improve large data import performance into SQLite with C#
body: <p>I am using C# to import a CSV with 6-8million rows.</p>
  
  <p>My table looks like this:</p>
  
  <pre><code>CREATE TABLE [Data] ([ID] VARCHAR(100)  NULL,[Raw] VARCHAR(200)  NULL)
  CREATE INDEX IDLookup ON Data(ID ASC)
  </code></pre>
  
  <p>I am using <a href="http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki" rel="noreferrer">System.Data.SQLite</a> to do the import.</p>
  
  <p>Currently to do 6 millions rows its taking 2min 55 secs on a Windows 7 32bit, Core2Duo 2.8Ghz &amp; 4GB RAM.  That's not too bad but I was just wondering if anyone could see a way of importing it quicker.</p>
  
  <p>Here is my code:</p>
  
  <pre><code>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 =&gt; 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();
  </code></pre>