We have a sqlite database that our iOS app stores images in in a blob column. We use FMDB to read the blob as NSData and then convert to a UIImage. The code is shown below.
-(UIImage*)getImageWithGuid:(NSString *)guid imageSizeKind:(ImageSizeKind)imageSizeKind
{
FMDatabase *db = [self openFMDatabase];
if (!db) {
return nil;
}
NSData *imageData = nil;
NSString *query = [NSString stringWithFormat:@"SELECT Image FROM images WHERE Guid = '%@' AND MediaType = %d limit 1", guid, imageSizeKind];
FMResultSet *rs = [db executeQuery:query];
if ([rs next])
{
imageData = [rs dataForColumn:imagesTable.image];
}
[rs close];
[db close];
if (!imageData) {
NSLog(@"Image was not found in database '%@' using sql query '%@'", [self databasePath], query);
}
UIImage *image = [UIImage imageWithData:imageData];
return image;
}
The caller to this method above, receives the image and then resizes it. In the caller to this method above, I have some code to time the obtaining and resizing portions of the code, and I received the following output in the debug console...
23:31:17.084 Obtained image in 4.208354 sec
23:31:17.086 Resized image in 0.001961 sec
23:31:17.115 Obtained image in 0.028943 sec
23:31:17.117 Resized image in 0.001891 sec
23:31:17.131 Obtained image in 0.013373 sec
23:31:17.133 Resized image in 0.002036 sec
23:31:17.844 Obtained image in 0.711072 sec
23:31:17.846 Resized image in 0.001634 sec
23:31:17.880 Obtained image in 0.034076 sec
23:31:17.882 Resized image in 0.001678 sec
23:31:17.910 Obtained image in 0.028255 sec
23:31:17.912 Resized image in 0.001652 sec
23:31:17.943 Obtained image in 0.031323 sec
23:31:17.945 Resized image in 0.001783 sec
23:31:17.954 Obtained image in 0.009396 sec
23:31:17.956 Resized image in 0.001982 sec
23:31:17.986 Obtained image in 0.029724 sec
23:31:17.988 Resized image in 0.001977 sec
23:31:18.026 Obtained image in 0.037283 sec
23:31:18.027 Resized image in 0.001837 sec
23:31:18.051 Obtained image in 0.023700 sec
23:31:18.053 Resized image in 0.001947 sec
23:31:18.088 Obtained image in 0.035087 sec
23:31:18.090 Resized image in 0.001687 sec
23:31:18.136 Obtained image in 0.045304 sec
Notice that the very first image took a whopping 4.2 seconds to obtain, then all the following images took mere hundredths of a second.
Is there some way that I can "prime the pump" so-to-speak to get that 4.2 seconds out of the way and have the database ready to behave like it did with all the images that followed. Ideally, it would be awesome to tuck that 4 second delay away on some background thread so that the user doesn't have to experience it at some other point in the app, simply by moving this initial 4 seconds somewhere else to be incurred.
Thanks.
FMDB (or more accurately, SQLite) doesn't require any "priming". I just ran the following code:
CFAbsoluteTime last = CFAbsoluteTimeGetCurrent();
for (NSInteger i = 0; i < 10; i++) {
FMResultSet *rs = [database executeQuery:@"select image_data from images where guid = ?", @(i)];
NSAssert(rs, @"select failed: %@", [database lastErrorMessage]);
if ([rs next]) {
CFAbsoluteTime current = CFAbsoluteTimeGetCurrent();
NSData *data = [rs dataForColumnIndex:0];
NSLog(@"%lu %0.3f", (unsigned long)[data length], current - last);
last = current;
}
[rs close];
}
And on iPhone 6+, it reported:
2016-03-24 21:53:36.107 MyApp[3710:1262147] 1000000 0.010 2016-03-24 21:53:36.112 MyApp[3710:1262147] 1000000 0.004 2016-03-24 21:53:36.115 MyApp[3710:1262147] 1000000 0.004 2016-03-24 21:53:36.123 MyApp[3710:1262147] 1000000 0.008 2016-03-24 21:53:36.131 MyApp[3710:1262147] 1000000 0.008 2016-03-24 21:53:36.138 MyApp[3710:1262147] 1000000 0.007 2016-03-24 21:53:36.146 MyApp[3710:1262147] 1000000 0.007 2016-03-24 21:53:36.153 MyApp[3710:1262147] 1000000 0.007 2016-03-24 21:53:36.161 MyApp[3710:1262147] 1000000 0.007 2016-03-24 21:53:36.168 MyApp[3710:1262147] 1000000 0.007
As you can probably deduce from the log above, this was benchmarking the retrieval of ten 1 mb BLOB from the database.
So, there are a couple of possible issues:
Double check where you're starting your timer. Make sure you don't have other code in between where you start the timer and when you start retrieving the images. Maybe there's something else in your code that is slowing down the app.
I might suggest running Instruments' "Time Profiler" and confirm precisely what's accounting for your 4 second delay. Or, a little more complicated, you can sometimes identify blocking calls by sorting "wait time" in the "System Calls" in the "System Trace" tool. But, bottom line, confirm that SQLite is actually the source of the 4 second delay.
If you're not comfortable with Instruments, you can often identify the bottle necks the old-fashioned way, inserting more and more timing statements and narrow it down to the one or two lines that are accounting for your 4 second delay. (E.g. is it the executeQuery, or the step, or the image resizing rather than the image retrieval, or perhaps the first opening of the database is doing some expensive copy from bundle to documents, etc.)
If the problem truly rests in SQLite, I'd make sure that you have an index on guid (ideally a unique key). It's unlikely to cause this problem (especially if you're closing the database before opening the next row), but if the performance delay is truly in SQLite, that might help.
Also, I notice that you are using limit 1 in your SQL statement. Is it really true that you have multiple entries with the same guid (usually that would be unique)? Maybe the time difference is related to the number of matches you're getting for the first key.
Also make sure you don't have any other processes trying to work on the same SQLite database at the same time. I assume this is not the case, as you said that you allowed the app to settle down before you did your benchmarking process, but I mention this just for the sake of completeness.
To Wolverine's observation that it's better to store images in the file system and only store filenames in the database, this is definitely true (if I recall correctly, the last time I benchmarked that, was 10-20% slower retrieving images from SQLite rather than getting the file name from SQLite but then retrieve the image directly from the file system). The old rule of thumb used to be that if your images are thumbnail sized, storing them in SQLite is fine, but if they're megabyte sized (rather than 10s of KB), then storing them in the file system starts to yield material performance improvements. I don't think this answers the question of why your first call is so slow, though, but it's worth considering if the images are larger.
If you search Stack Overflow for [sqlite] blob performance or perform similar google search, you'll see many discussions on the disadvantages in storing very large BLOB objects in SQLite.
Having said that, I do have one performance related observation: I wouldn't open and close the database each time. Open the database once when the app starts and leave it open. This only saves 10s of milliseconds, so this won't account for your 4 second delay problem, but it will be a little more efficient.
But, bottom line, I'm unable to reproduce the performance delay that you report. If you can create a small stand alone MCVE, then we can help you diagnose it. But I suspect the problem rests somewhere other than the code snippet you've shared with us thus far.