title: Improving SQLite Performance
body: <p>Well i have a <code>file.sql</code> that contains <code>20,000</code> of insert commands</p>
  
  <p>Sample From the <code>.sql</code> file</p>
  
  <blockquote>
    <p>INSERT INTO <code>table</code> VALUES
    (1,-400,400,3,154850,'Text',590628,'TEXT',1610,'TEXT',79);</p>
    
    <p>INSERT INTO <code>table</code> VALUES
    (39,-362,400,3,111659,'Text',74896,'TEXT',0,'TEXT',14);</p>
  </blockquote>
  
  <p>And i am using the following code to create an in memory Sqlite database and pull the values into it then calculate the time elapsed</p>
  
  <pre><code>using (var conn = new SQLiteConnection(@"Data Source=:memory:"))
  {
      conn.Open();
  
      var stopwatch = new Stopwatch();
      stopwatch.Start();
  
      using (var cmd = new SQLiteCommand(conn))
      {
          using (var transaction = conn.BeginTransaction())
          {
  
                  cmd.CommandText = File.ReadAllText(@"file.sql");
                  cmd.ExecuteNonQuery();
  
              transaction.Commit();
          }
      }
  
      var timeelapsed = stopwatch.Elapsed.TotalSeconds &lt;= 60
                            ? stopwatch.Elapsed.TotalSeconds + " seconds"
                            : Math.Round(stopwatch.Elapsed.TotalSeconds/60) + " minutes";
      MessageBox.Show(string.Format("Time elapsed {0}", timeelapsed));
      conn.Close();
  }
  </code></pre>
  
  <p>Things i have tried</p>
  
  <ol>
  <li>Using file database instead of memory one.</li>
  <li>Using begin transaction and commit transaction [AS SHOWN IN MY CODE].</li>
  <li>Using Firefox's extension named <code>SQLite Manager</code> to test whether the
  slowing down problem is from the script; However, I was surprised
  that the same <code>20,000</code> lines that i am trying to process using my code
  has been pulled to the database in JUST 4ms!!!.</li>
  <li>Using <code>PRAGMA synchronous = OFF</code>, as well as, <code>PRAGMA journal_mode =
  MEMORY</code>.</li>
  <li>Appending <code>begin transaction;</code> and <code>commit transaction;</code> to the
  beginning and ending of the <code>.sql</code> file respectively.</li>
  </ol>
  
  <p>As the SQLite documentations says : SQLite is capable of processing <code>50,000</code> commands per seconds. And that is real and i made sure of it using the <code>SQLite Manager</code> [AS DESCRIPED IN THE THIRD SOMETHING THAT I'V TRIED]; However, I am getting my <code>20,000</code> commands done in 4 minutes something that tells that there is something wrong.</p>
  
  <p>QUESTION : What is the problem am i facing why is the Execution done very slowly ?!</p>