Tip: SQLite in RAM … to improve speed!

It’s very usual to use encrypted SQLitedatabases in our Xojo projects where we expect to get the maximum read speed from them. But the truth is that encrypting the data in these databases can introduce a penalty in our queries, both from read and writing/updating data to them. How can we improve this? One technique is the creation of a new in-memory based SQLite database, where we will be able to copy the table (or tables) we are interested in getting the maximum speed possible with. Continue reading to see how to do this.

You can further modify this technique so you can work with your databases in a mixedway; that is, where we want to simultaneously execute a query involving both an encrypted database table and an in-memory table database.

But for this example code we are only focused on opening an encrypted SQLite database (based on disk) as a new SQLite class instance, and using next a SQL sentence to create a new in-memory database (named RAMDBin the example), creating the same table on the RAM database next and then copying the data from the sourcetable during the process.

From that time on, you will be able to use the new table on RAM at light speed!

Another SQL command you might find of interest, and that you can use in all your SQLite databases where you expect to get a gain in the read speed, is to use the exclusive mode, using for that PRAGMA LOCKING_MODE = Exclusive.

However, and focusing on the tip itself, this is the snippet of code you can use:

Dim db as SQLiteDatabase = New SQLiteDatabase
db.DatabaseFile = // FolderItem to the SQLite database file on disk
db.EncryptionKey = "aes256:" + // same password that the one used to encrypt the data
If db.Connect = False Then
  Dim re As New RuntimeException
  re.Reason = "Error trying to connect to the database"
  Raise re
End If
db.SQLExecute("PRAGMA LOCKING_MODE = Exclusive") // Improves the read access to the database

// We create a in-memory database, 'attaching' it with the disk based SQLite database
db.SQLExecute("attach database ':memory:' as 'RAMDB'")

// Copying all the contents from the source table
// to the newly created table on the RAM SQLite database

db.SQLExecute("create table RAMDB.product_table as select * from main.product_table")

As you can see, the second line of code expects a valid FolderIteminstance pointing to the SQLite database on disk. In addition, you need to provide the String to the password used originally to encrypt the data on the source database. This example assumes that this has been done using the AES 256option, stronger than the one using a length of 128 bits and the only option available before Xojo 2018r1.

Finally, in the last sentence db.SQLExecuteyou’ll have to change the table name you want to copy from the source database to the new RAM based one (product_tablein the example).

The Xojo programming language is fast and easy to grasp!