Performance differences between SQLite on Android and iOS
android, ios, performance, sqlite
Solution
You need to use a transaction--start by executing `BEGIN` and finish with `COMMIT`.
This should greatly improve `INSERT` performance.
http://www.titaniumdevelopment.com.au/blog/2012/01/27/10x-faster-inserts-in-sqlite-using-begin-commit-in-appcelerator-titanium-mobile/
Once that's done I'd expect 5000 inserts to be quite fast on both platforms.
Here is another StackOverflow answer which lists a ton of different things which can improve SQLite performance, including using bind variables and enabling various PRAGMA modes which trade off robustness for speed: Improve INSERT-per-second performance of SQLite?
Problem
I am trying to perform a benchmark between SQLite performance in Android and iOS for a project, and seem to get really bad performance on the iOS platform, compared to Android. What I am trying to achieve is to measure the time to insert a number of rows (5000) into the SQLite DB and compare between platforms. For Android I get results around 500ms to perform all 5000 inserts, but for iOS the same operation takes above 20s. How can this be? This is a snippet of my iOS code (the insert part), dataArray is an array with 5000 random 100 char NSStrings: ``` int numEntries = 5000; self.dataArray = [[NSMutableArray alloc] initWithCapacity:numEntries];//Array for random data to write to database //generate random data (100 char strings) for (int i=0; i<numEntries; i++) { [self.dataArray addObject:[self genRandStringLength:100]]; } // Get the documents directory NSArray *dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); NSString *docsDir = [dirPaths objectAtIndex:0]; // Build the path to the database file NSString *databasePath = [[NSString alloc] initWithString:[docsDir stringByAppendingPathComponent: @"benchmark.db"]]; NSString *resultHolder = @""; //Try to open DB, if file not present, create it if (sqlite3_open([databasePath UTF8String], &db) == SQLITE_OK){ sql = @"CREATE TABLE IF NOT EXISTS BENCHMARK(ID INTEGER PRIMARY KEY AUTOINCREMENT, TESTCOLUMN TEXT)"; //Create table if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, NULL) == SQLITE_OK){ NSLog(@"DB created"); }else{ NSLog(@"Failed to create DB"); } //START: INSERT BENCHMARK NSDate *startTime = [[NSDate alloc] init];//Get timestamp for insert-timer //Insert values in DB, one by one for (int i = 0; i<numEntries; i++) { sql = [NSString stringWithFormat:@"INSERT INTO BENCHMARK (TESTCOLUMN) VALUES('%@')",[self.dataArray objectAtIndex:i]]; if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, NULL) == SQLITE_OK){ //Insert successful } } //Append time consumption to display string resultHolder = [resultHolder stringByAppendingString:[NSString stringWithFormat:@"5000 insert ops took %f sec\n", [startTime timeIntervalSinceNow]]]; //END: INSERT BENCHMARK ``` Android code snippet: ``` // SETUP long startTime, finishTime; // Get database object BenchmarkOpenHelper databaseHelper = new BenchmarkOpenHelper(getApplicationContext()); SQLiteDatabase database = databaseHelper.getWritableDatabase(); // Generate array containing random data int rows = 5000; String[] rowData = new String[rows]; int dataLength = 100; for (int i=0; i<rows; i++) { rowData[i] = generateRandomString(dataLength); } // FIRST TEST: Insertion startTime = System.currentTimeMillis(); for(int i=0; i<rows; i++) { database.rawQuery("INSERT INTO BENCHMARK (TESTCOLUMN) VALUES(?)", new String[] {rowData[i]}); } finishTime = System.currentTimeMillis(); result += "Insertion test took: " + String.valueOf(finishTime-startTime) + "ms \n"; // END FIRST TEST ```