title: How to improve performance of bulk SELECT query using SQLite in .NET?
body: <p>I have a large (long) table of data stored in SQLite - potentially 5m+ entries. I am using the <code>System.Data.SQLite</code> package to execute my query and read the data into a bespoke in-memory collection structure in the regular ADO.net way.</p>
  
  <h1>CODE (F#)</h1>
  
  <pre class="lang-ml prettyprint-override"><code>use cnxn = new SQLiteConnection(@"Data Source=C:\Temp\test.db;Version=3;Read Only=True;")
  cnxn.Open()
  
  let data = ResizeArray&lt;Data&gt;()
  
  let cmd = new SQLiteCommand(@"SELECT X, Y, Z, AAA FROM Data", cnxn)
  let reader = cmd.ExecuteReader()
  
  while reader.Read() do
      let d = {X = reader.GetInt32(0); Y = reader.GetInt32(1); 
                      Z = reader.GetInt32(2); AAA = reader.GetDouble(3)}
      data.Add(d)
  cnxn.Close()
  </code></pre>
  
  <h1>Questions</h1>
  
  <ol>
  <li><p>Is <code>System.Data.SQLite</code> the most performant library to be using for the job here? I am only using it because it appears to be the <strong>standard</strong></p></li>
  <li><p>Is their a better way to code this up?</p></li>
  <li><p>Are there any settings/configurations on the database itself that would help this scenario?</p></li>
  </ol>
  
  <h1>Why do I think this should be able to go faster?</h1>
  
  <p>My computer has a theoretical read speed of 725 mb/s (SSD). Reading the sqlite above I  am reading 40mb in 1s which is an effective actual speed of 40 mb/s.</p>
  
  <p>Another surprising result from profiling shows that about 35% of the time is spent on <code>reader.Read()</code> [not surprising] and the remainder in <code>GetInt32</code> and <code>GetDouble()</code> [very surprising].</p>