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];