iDevGames Forums
sqlite3 multi-line query - Printable Version

+- iDevGames Forums (http://www.idevgames.com/forums)
+-- Forum: Development Zone (/forum-3.html)
+--- Forum: Tools & Technology (/forum-10.html)
+--- Thread: sqlite3 multi-line query (/thread-10368.html)

sqlite3 multi-line query - funkboy - Oct 16, 2012 04:23 PM

I have sqlite running fine in my iOS app, but am trying to increase performance and have run into a problem.

I thought putting multiple "INSERT" commands into a single string, preparing that statement, and performing sqlite3_step on that would be faster than performing many prepares for each "INSERT" command.

I programmatically construct a long string with a bunch of lines such as:

Code:
INSERT INTO publications ( name, someColumn, anotherColumn, yetAnotherColumn ) VALUES ( 'Some Name','3t995','','BBA0' ) ;
INSERT INTO publications ( name, someColumn, anotherColumn, yetAnotherColumn ) VALUES ( 'Journal of Math','1326','','BBAA' ) ;
INSERT INTO publications ( name, someColumn, anotherColumn, yetAnotherColumn ) VALUES ( 'Journal of Jumping','thisValue','','BBAB' ) ;
INSERT INTO publications ( name, someColumn, anotherColumn, yetAnotherColumn ) VALUES ( 'Journal of Medicine','1529','','BBAC' ) ;

This is in an NSMutableString named sqlString, which I do the following to:

Code:
sqlite3_stmt *statement;
            if (sqlite3_prepare_v2(appDelegate.database, [sqlString UTF8String], -1, &statement, NULL) == SQLITE_OK)
            {
                int resultCode = sqlite3_step(statement);
                if( resultCode != SQLITE_ERROR )
                {
                    //we're good!
                }
                else
                {
                    NSLog(@"sqlite ERROR for committing [%@] that: %i", sqlString, resultCode);
                }
            }
            sqlite3_finalize(statement);

Both sqlite3_prepare_v2 and sqlite3_step are returning SQLITE_OK (which is code 101), but when I exit out of the app and completely reload it, none of the data has truly been written to disk.

I have escaped all the single quote ' characters in the string to be double single quotes ''... any ideas what might be going wrong?

Thanks!

RE: sqlite3 multi-line query - OneSadCookie - Oct 16, 2012 07:58 PM

http://stackoverflow.com/questions/1711631/how-do-i-improve-the-performance-of-sqlite

RE: sqlite3 multi-line query - funkboy - Oct 17, 2012 07:29 PM

Fantastic link, thanks so much! After some tweaking, took my queries from 55 seconds to 5! Embarrassed I never took the time to optimize this before... But glad it's there now!