In the sqlite.swift framework is there a way to prepare the statement once ahead of time and then bind the variable just before execution?

Question

I use SQLite for my persistent store. I use a dictionary based off the primary key for an in-memory store: var localContext = [String: GrandLite]().

I use the function below to retrieve an object from the dictionary, or from the database and then store in the dictionary. This function is called frequently, and I'm trying to optimize it.

class func retrieveByKey<T: GrandLite>(aType: [T], thisKey: String) -> T? {
    let thisStack = FoodysLiteStack.thisDataStack
    if let thisObject = thisStack.localContext[thisKey] as? T {
        return thisObject
    } else {
        do {
            let db = thisStack.localDatabase
            let thisTable = T.getTable()
            if let thisRow = try db.pluck(thisTable.filter(uuidKeyLite == thisKey)) {
                let thisObject = T(withRow: thisRow)
                thisStack.localContext[thisKey] = thisObject
                return thisObject
            } else {
                return nil
            }
        } catch {
            NSLog("WARNING: Unhandled error for T retrieveByKey")
            return nil
        }
    }
}

As I understand sqlite.swift pluck is basically a prepare with limit 1. Also, prepare compiles the SQL statement, binds the variable, and executes it. I'm trying to avoid the SQLite compile time every time this function is called.

In the sqlite.swift framework is there a way to prepare the statement once ahead of time and then bind the variable just before execution? You can do this for inserts and updates with db.prepare and db.run, but I don't see a way to bind a variable for an already prepared select statement. I may be overthinking this, the SQLite compile time on thisTable.filter(uuidKeyLite == thisKey) is probably quite small.

Answer 1

I may be overthinking this, the SQLite compile time on thisTable.filter(uuidKeyLite == thisKey) is probably quite small.

I think you do. Especially that with SQLite.swift, the statement compilation time is currently negligible compared to its internal mechanisms. See Comparing the Performances of Swift SQLite libraries

Answer 2

That framework tries its very best to abstract such details away.

The only way to keep a prepared statement around is by using the functions to execute raw SQL.

And yes, in SQLite, the prepare overhead usually is quite small; the most typical performance problem comes from not combining multiple statements into a single transaction.