sqlite bulk insert performance via native c++

getting the most out of sqlite on the desktop.

I have been working on a couple desktop applications with Sqlite and insert performance out of the box wasn't quite what I needed. For a simple table being fed from a flat file a little over 200Mb the inserts per second were around 38,000 per second. After tuning the project on my development machine comes up to around 230,000 inserts per second.

After some hints ( google is your friend ) and then some testing I have what seems to be more of less the best configuration :
+ Tune Sqlite
+ Commit after around 50,000 inserts
+ strtok directly into statement.Bind
+ depending on input file stronging consider using boost::iostreams::mapped_file_source for access to the file

SQLite3::CDB is provided of course by PJ naughter

CREATE TABLE IF NOT EXISTS stop_times 
( trip_id        INTEGER 
, arrival_time   TEXT 
, departure_time TEXT 
, stop_id        INTEGER 
, stop_sequence  INTEGER 
, stop_headsign  TEXT 
, pickup_type    TEXT 
, drop_off_type  TEXT 
, traveled       TEXT )

void SetDbParameters( SQLite3::CDB* pDbSQLite )
 {
  if ( NULL == pDbSQLite  )
  {
   // go home
   return;
  }

  std::string sSynchronous  = "PRAGMA synchronous   = OFF";
  std::string sJournalMode  = "PRAGMA journal_mode  = MEMORY";
  std::string sCountChanges = "PRAGMA count_changes = OFF";
  std::string sTempStore    = "PRAGMA temp_store    = MEMORY";
  std::string sPageSize     = "PRAGMA page_size     = 32768";  
  std::string sLockingMode  = "PRAGMA locking_mode  = EXCLUSIVE";

  pDbSQLite->Exec( sSynchronous.c_str() );
  pDbSQLite->Exec( sJournalMode.c_str() );
  pDbSQLite->Exec( sCountChanges.c_str() );  
  pDbSQLite->Exec( sTempStore.c_str() );  
  pDbSQLite->Exec( sPageSize.c_str() );   
  pDbSQLite->Exec( sLockingMode.c_str() );

 }

void PrepareBind( char* buffer )
 {
  SQLite3::CStatement stmt;
  stmt.Prepare(m_db, "INSERT INTO stop_times VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"); 
  stmt.Bind( 1, strtok (buffer, ",") );         
  stmt.Bind( 2, strtok (NULL, ",") );  
  stmt.Bind( 3, strtok (NULL, ",") );  
  stmt.Bind( 4, strtok (NULL, ",") );  
  stmt.Bind( 5, strtok (NULL, ",") );  
  stmt.Bind( 6, strtok (NULL, ",") );  
  stmt.Bind( 7, strtok (NULL, ",") );  
  stmt.Bind( 8, strtok (NULL, ",") );  
  stmt.Bind( 9, strtok (NULL, ",") );  
  stmt.Step();
  stmt.ClearBindings();
  stmt.Reset();
 }