FMDB - Improve FMResultSet speed

Question

This code take a very long time to run. I have roughly 100,000 records. Each step takes roughly the same mount of time.... get the result from FMResultSet and reading it into the 3 arrays. I tried using two select statements. One to get the result count and then allocating that amount in the mutableArray. No performance difference. Any suggestions to improve speed?

FMResultSet *results = [database executeQuery:@"SELECT FriendlyName, Serial, Barcode FROM Inventory, Company WHERE Inventory.friendlyName <> '' AND Company.CompanyID = Inventory.companyID AND Company.CompanyName = ?", selectedCompany];

arrayFriendlyName =[[NSMutableArray alloc]init];
arraySerial = [[NSMutableArray alloc]init];
arrayBarcode = [[NSMutableArray alloc]init];


while([results next]) {

    [arrayFriendlyName addObject:[results stringForColumn:@"FriendlyName"]];
    [arrayBarcode addObject:[results stringForColumn:@"Barcode"]];
    [arraySerial addObject:[results stringForColumn:@"Serial"]];

}

[database close];

how much time does it take and is it bad for the user? I believe the 100000 records slow down the process. Can you send the task to the background? — Volker, Feb 19 '14 at 21:54
Multiple select would buy you nothing of course. If you are not willing to hang up your UI thread, optimally you can perform the task to some background queue as @Volker said — Ayan Sengupta, Feb 19 '14 at 21:57
By the way, why do you need 1000,000 records at once? why don't you lazy load the records when required or else it would pose some serious performance threat to your app? — Ayan Sengupta, Feb 19 '14 at 21:58
It loads the 100k records so they can be searched and viewed using a search bar. This is an inventory app. — CrazyGoose, Feb 19 '14 at 22:55

score 0 · Accepted Answer · answered Feb 19 '14 at 22:05

Depending on your database structure any object-oriented wrapper around sqlite can be quite slow when it comes to thousands of rows. You can try to speed it up by directly accessing the sqlite database, without using any object oriented wrapper. As you don't need any relations (at least in your example code), you won't loose any relevant functionality - and it will be as fast as it can get.

As always, Ray Wenderlich is a good resource to look at: http://www.raywenderlich.com/913/sqlite-tutorial-for-ios-making-our-app

In case you didn't do it already, you can also have a look whether you have your indexes set right on your query - as you are joining two tables, having no indexes on the joined columns can seriously impact your performance.

FMDB - Improve FMResultSet speed

1 Answers1