Today, I was trying to find an alternative on reading a Binary data file to use information in my software. For this I choose to use SQlite the embedded database. It has quite a few steps before I achieve a acceptable speed with database and here is list of those that need to be taken care for BULK INSERT in SQLite database.

1. Try to simplify your for or while loop as much as you can. For example do not construct the Query within Loop. Give String Query before the start of loop.

2. Loop should be enclosed within the Transaction. Somebody techincally explain me that with Tranasction the fsync function which is responsible of ensuring the  data safely written, is called once only. If you don’t use Transaction fsync is called on every insert query and proves to be a bottleneck in performance.

3. Index your Table if you have to “update” the result within the Loop. In my case I have to first find one value in my Binary file, write it down to Database, then in one of condition to read of file, I might get other value to UPDATE my existing record. So, within my loop I have insert and update commands. And indexing makes it easier to find record when I got more than 50-60K record inserted. It make the speed upgrade of 1500% in my case with just 0.1M records. I have to read 1.5M records.

SQLite is really fun. It works charm with proper techniques used and store more than 1M record for me to work on.