Search code examples
iosobjective-csqliteblobfmdb

Query select of BLOB data in sqlite too slow in iOS using FMDB

i'm using FMDB wrapper for my sqlite database, and in the database i store image downloaded from a url, and stored in BLOB data, but when i make a SELECT query in the database like this:

SELECT id,img FROM table1 WHERE name = "Carl"

and this is the column in the database:

img BLOB

but the query is very slow, if i remove the img from the query, it's very faster, after i download the image from a url i resized it to make it smaller and so save memory space in this way:

NSData *biggerImg = [NSData dataWithContentsOfURL:[NSURL URLWithString:url_image]];

NSData *img = UIImageJPEGRepresentation([self imageWithImage:[UIImage biggerImg] scaledToSize:CGSizeMake(90, 134)], 1.0);


- (UIImage *)imageWithImage:(UIImage *)image scaledToSize:(CGSize)newSize {
//UIGraphicsBeginImageContext(newSize);
UIGraphicsBeginImageContextWithOptions(newSize, NO, 0.0);
[image drawInRect:CGRectMake(0, 0, newSize.width, newSize.height)];
UIImage *newImage = UIGraphicsGetImageFromCurrentImageContext();
UIGraphicsEndImageContext();
return newImage;
}

anyone knows how i can make the query faster? i need that in the query there is also the image.

thanks

Solution

  • As documented here: http://www.sqlite.org/intern-v-extern-blob.html, it can improve the SQLite performance if large BLOBs (such as image data) are stored externally, and only the path to the file is recorded in the database.

    If you store the images in the "Documents" directory then they are backed up to iCloud (if the user has activated that feature). If an iCloud backup is not wanted (e.g. because the images can be reloaded from a server at any time), then "Library/Caches" (use NSCachesDirectory) would be a better location.