title: Can SQLite handle 90 million records?
body: <p>Or should I use a different hammer to fix this problem.</p>
  
  <p>I've got a very simple use-case for storing data, effectively a sparse matrix, which I've attempted to store in a SQLite database. I've created a table:</p>
  
  <pre><code>create TABLE data ( id1 INTEGER KEY, timet INTEGER KEY, value REAL )
  </code></pre>
  
  <p>into which I insert a lot of data, (800 elements every 10 minutes, 45 times a day), most days of the year. The tuple of (id1,timet) will always be unique. </p>
  
  <p>The timet value is seconds since the epoch, and will always be increasing. The id1 is, for all practical purposes, a random integer. There is probably only 20000 unique ids though. </p>
  
  <p>I'd then like to access all values where id1==someid or access all elements where timet==sometime. On my tests using the latest SQLite via the C interface on Linux, a lookup for one of these (or any variant of this lookup) takes approximately 30 seconds, which is not fast enough for my use case. </p>
  
  <p>I tried defining an index for the database, but this slowed down insertion to completely unworkable speeds (I might have done this incorrectly though...)</p>
  
  <p>The table above leads to very slow access for any data. My question is:</p>
  
  <ul>
  <li>Is SQLite completely the wrong tool for this?</li>
  <li>Can I define indices to speed things up significantly?</li>
  <li>Should I be using something like HDF5 instead of SQL for this?</li>
  </ul>
  
  <p>Please excuse my very basic understanding of SQL!</p>
  
  <p>Thanks</p>
  
  <p>I include a code sample that shows how the insertion speed slows to a crawl when using indices. With the 'create index' statements in place, the code takes 19 minutes to complete. Without that, it runs in 18 seconds. </p>
  
  <hr>
  
  <pre><code>#include &lt;iostream&gt;
  #include &lt;sqlite3.h&gt;
  
  void checkdbres( int res, int expected, const std::string msg ) 
  {
    if (res != expected) { std::cerr &lt;&lt; msg &lt;&lt; std::endl; exit(1); } 
  }
  
  int main(int argc, char **argv)
  {
    const size_t nRecords = 800*45*30;
  
    sqlite3      *dbhandle = NULL;
    sqlite3_stmt *pStmt = NULL;
    char statement[512];
  
    checkdbres( sqlite3_open("/tmp/junk.db", &amp;dbhandle ), SQLITE_OK, "Failed to open db");
  
    checkdbres( sqlite3_prepare_v2( dbhandle, "create table if not exists data ( issueid INTEGER KEY, time INTEGER KEY, value REAL);", -1, &amp; pStmt, NULL ), SQLITE_OK, "Failed to build create statement");
    checkdbres( sqlite3_step( pStmt ), SQLITE_DONE, "Failed to execute insert statement" );
    checkdbres( sqlite3_finalize( pStmt ), SQLITE_OK, "Failed to finalize insert");
    checkdbres( sqlite3_prepare_v2( dbhandle, "create index issueidindex on data (issueid );", -1, &amp; pStmt, NULL ), SQLITE_OK, "Failed to build create statement");
    checkdbres( sqlite3_step( pStmt ), SQLITE_DONE, "Failed to execute insert statement" );
    checkdbres( sqlite3_finalize( pStmt ), SQLITE_OK, "Failed to finalize insert");
    checkdbres( sqlite3_prepare_v2( dbhandle, "create index timeindex on data (time);", -1, &amp; pStmt, NULL ), SQLITE_OK, "Failed to build create statement");
    checkdbres( sqlite3_step( pStmt ), SQLITE_DONE, "Failed to execute insert statement" );
    checkdbres( sqlite3_finalize( pStmt ), SQLITE_OK, "Failed to finalize insert");
  
    for ( size_t idx=0; idx &lt; nRecords; ++idx)
    {
      if (idx%800==0)
      {
        checkdbres( sqlite3_prepare_v2( dbhandle, "BEGIN TRANSACTION", -1, &amp; pStmt, NULL ), SQLITE_OK, "Failed to begin transaction");
        checkdbres( sqlite3_step( pStmt ), SQLITE_DONE, "Failed to execute begin transaction" );
        checkdbres( sqlite3_finalize( pStmt ), SQLITE_OK, "Failed to finalize begin transaction");
        std::cout &lt;&lt; "idx " &lt;&lt; idx &lt;&lt; " of " &lt;&lt; nRecords &lt;&lt; std::endl;
      }
  
      const size_t time = idx/800;
      const size_t issueid = idx % 800;
      const float value = static_cast&lt;float&gt;(rand()) / RAND_MAX;
      sprintf( statement, "insert into data values (%d,%d,%f);", issueid, (int)time, value );
      checkdbres( sqlite3_prepare_v2( dbhandle, statement, -1, &amp;pStmt, NULL ), SQLITE_OK, "Failed to build statement");
      checkdbres( sqlite3_step( pStmt ), SQLITE_DONE, "Failed to execute insert statement" );
      checkdbres( sqlite3_finalize( pStmt ), SQLITE_OK, "Failed to finalize insert");
  
      if (idx%800==799)
      {
        checkdbres( sqlite3_prepare_v2( dbhandle, "END TRANSACTION", -1, &amp; pStmt, NULL ), SQLITE_OK, "Failed to end transaction");
        checkdbres( sqlite3_step( pStmt ), SQLITE_DONE, "Failed to execute end transaction" );
        checkdbres( sqlite3_finalize( pStmt ), SQLITE_OK, "Failed to finalize end transaction");
      }
    }
  
    checkdbres( sqlite3_close( dbhandle ), SQLITE_OK, "Failed to close db" ); 
  }
  </code></pre>
  
  <hr>