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();

USEFUL?
We benefit hugely from resources on the web so we decided we should try and give back some of our knowledge and resources to the community by opening up many of our company’s internal notes and libraries through mini sites like this. We hope you find the site helpful.
Please feel free to comment if you can add help to this page or point out issues and solutions you have found, but please note that we do not provide support on this site. If you need help with a problem please use one of the many online forums.