Table Indexes

Give tables Indexes if they will help with SELECT queries, e.g.

sTemp = "CREATE INDEX LogDateNumeric_index ON tblLogEvents(LogDateNumeric);                             \
            CREATE INDEX LogInstrumentDateTime_index ON tblLogEvents(LogInstrumentNo, LogDateTimeNumeric);      \
        ";
Command1->CommandText = sTemp;
Command1->ExecuteNonQuery();

Group Transactions

SQLite is capable of carrying out many thousands of actions on a database per second, but not very many individually triggered transactions. You can work round this by grouping transactions such as INSERT or UPDATE to get a massive speed improvement if doing lots of individual actions. E.g.

WriteCommand1->CommandText = "BEGIN TRANSACTION;";
WriteCommand1->ExecuteNonQuery();

WriteCommand1->CommandText = "INSERT INTO tblLogEvents (LogInstrumentNo ) Values(@LogInstrumentNo)";
WriteCommand1->Parameters->AddWithValue("@LogInstrumentNo", LogInstrumentNo1);
WriteCommand1->ExecuteNonQuery();

WriteCommand1->CommandText = "INSERT INTO tblLogEvents (LogInstrumentNo ) Values(@LogInstrumentNo)";
WriteCommand1->Parameters->AddWithValue("@LogInstrumentNo", LogInstrumentNo2);
WriteCommand1->ExecuteNonQuery();

WriteCommand1->CommandText = "INSERT INTO tblLogEvents (LogInstrumentNo ) Values(@LogInstrumentNo)";
WriteCommand1->Parameters->AddWithValue("@LogInstrumentNo", LogInstrumentNo3);
WriteCommand1->ExecuteNonQuery();

WriteCommand1->CommandText = "COMMIT;";
WriteCommand1->ExecuteNonQuery();

Increasing the cache size can be useful for this so SQLite has enough memory to hold all the actions

ConnectionWriteLogEvents = gcnew System::Data::SQLite::SQLiteConnection("data source=" + OurDatabaseFileName + ";Cache Size = 100000;Password=" + SQLITE_DATABASE_FILE_PASSWORD);
ConnectionWriteLogEvents->Open();

If you are doing both reading and writing then you can simply create two connections and have one for the writing with the grouped actions into a single transaction and the other for reading at any time around it.

Disk Access

Stopping SQLite waiting to confirm the OS has written the data to disk

Can result in database corruption if power is lost say, but can improve performance if there are lots of UPDATE transactions occurring say. However this is nwhere near as good as reducing the number of transactions – you get MUCH better results by going that!

ConnectionWriteLogEvents = gcnew System::Data::SQLite::SQLiteConnection("data source=" + OurDatabaseFileName + ";synchronous = OFF;Password=" + SQLITE_DATABASE_FILE_PASSWORD);
ConnectionWriteLogEvents->Open();