Caching prepared SQLite statements in iOS application

Many applications use Core data for iOS devices, others utilize frameworks like fmdb. But there are also people that write SQLite access from scratch. The comes the answer - how do you cache prepared statements (I will not discuss here why we cache prepared statements)?

Well - I have implemented 2 methods:

  • 1) Using C syntax to maintain array of sqlite_stmt*
  • 2) Using NSPointerArray* to store sqlite_stmt* that are prepared

First approach has it’s advantages in regards of using older SDKs (NSPointerArray is available from iOS 6.0+)

Second approach is more elegant, but as mentioned - only for newer versions.

Lets have some code:

Using C syntax

// Declare instance variables to store statements - good place is in you Database manager instance
sqlite3_stmt **stmtCached;
int stmtCachedCount;
NSMutableDictionary *stmtSQLPool;
@property (atomic, retain) NSMutableDictionary *stmtSQLPool;
// in implementation
@synthesize stmtSQLPool;
// in constructor initialize the array
stmtCached = (sqlite3_stmt**) malloc(sizeof(sqlite3_stmt*)*MAX_NUM_CACHED_STATEMENT);

Code to prepare and cache statements:

- (sqlite3_stmt*) prepareAndCacheStatementWithSQL: (NSString*) sql andDatabase: (sqlite3*) db {
        // Try to find if we have cached the statement
        NSNumber *index = [self.stmtSQLPool objectForKey:sql];
        if (index != nil && stmtCachedCount > index.intValue) {
            // we have already cached statement
            sqlite3_stmt* stmt = stmtCached[index.intValue];
            return stmt;
        // Not prepared yet - prepare it and put it in the pool
        sqlite3_stmt *stmt = [DAO prepareStatementWithNSString:sql andDB: db];
        if (!stmt)
            @throw [NSException exceptionWithName:@"Error preparing statement" 
                                           reason:[NSString stringWithFormat:@"Something went wrong - cannot prepare statement for sql: '%@' - %s", sql, sqlite3_errmsg(db)] 

        if (stmtCachedCount >= MAX_NUM_CACHED_STATEMENT) {
            NSLog(@"Maximum number of cached statements reached. Stmt for sql %@ will not be cached.", sql);
        } else {
            stmtCached[stmtCachedCount++] = stmt;
            // store the index
            NSNumber *indexNumber = [NSNumber numberWithInt:(stmtCachedCount-1)];
            [stmtSQLPool setObject:indexNumber forKey:sql];
        return stmt;

This is pretty much everything. Of course don’t forget to release and finalize statements on db close.

In second approach - just instead of using (sqlite**) - replace with (NSPointerArray*):

NSPointerFunctionsOptions options = (NSPointerFunctionsStrongMemory |
self.stmtCached = [NSPointerArray pointerArrayWithOptions:options];

Fetch and store statements:

sqlite3_stmt * stmt = [stmtCached pointerAtIndex:i];
[stmtCached addPointer:stmt];