How to not reload NSMutableArray from SQLite every time when opening iOS TableView

Question

I have an iOS app with three SQLite databases that I load from web services.

When I open the TableView for one of the dbs I load the data into a NSMutableArray and use that as the datasource.

Here is the viewDidLoad for the employee View...

- (void)viewDidLoad
{

    [super viewDidLoad];

    //Get array of employees and sections
    _locations = [LocationDatabase getLocations];
    self.sections = [LocationDatabase getSections:_locations];

    //Set up Search
    self.searchDisplayController.displaysSearchBarInNavigationBar = YES;
    self.filteredLocations = [NSMutableArray array];
    searchController.delegate = self;
    searchController.searchResultsDataSource = self;
    isSearching = FALSE;

    //iPad
    //self.dtailViewController = (detailViewController *)[[self.splitViewController.viewControllers lastObject] topViewController];

    //Set the back bar button
    UIBarButtonItem *backButton = [[UIBarButtonItem alloc] initWithTitle:@"Locations" style:UIBarButtonItemStylePlain target:nil action:nil];
    self.navigationItem.backBarButtonItem = backButton;
}

and the method it calls...

+ (NSMutableArray *) getEmployees

{

    id tmpDatabasePath = [(AppDelegate *)[[UIApplication sharedApplication] delegate] databasePath];
    NSMutableArray *tmpEmployees;
    tmpEmployees = [[NSMutableArray alloc] init];

    FMDatabase *db = [FMDatabase databaseWithPath:tmpDatabasePath];
    [db open];
    FMResultSet *results = [db executeQuery:@"SELECT * FROM employees"];
    while([results next])
    {
        employee *thisEmployee      = [employee new];
        thisEmployee.firstName      = [results stringForColumn:@"firstName"];
        thisEmployee.lastName       = [results stringForColumn:@"lastName"];
        thisEmployee.fullName       = [results stringForColumn:@"fullname"];
        thisEmployee.city           = [results stringForColumn:@"city"];
        thisEmployee.ste            = [results stringForColumn:@"state"];
        thisEmployee.emailAddress   = [results stringForColumn:@"email"];

        NSString *tst = [results stringForColumn:@"id"];
        thisEmployee.id    = [NSNumber numberWithInt:[tst intValue]];
        [tmpEmployees addObject:thisEmployee];
    }
    [db close];

    //Sort by Last Name
    NSSortDescriptor *sortDescriptor = [[NSSortDescriptor alloc] initWithKey:@"lastName"
                                                                  ascending:YES];
    NSMutableArray *sortDescriptors = [NSMutableArray arrayWithObject:sortDescriptor];
    tmpEmployees = [[tmpEmployees sortedArrayUsingDescriptors:sortDescriptors] mutableCopy];

    return tmpEmployees;
}

This all works fine and I am happy with it. The problem is that one of my databases is larger, with 1 or 2 k items in it, so the load is kind of slow. There is a noticeable lag for the user. It seems silly to just keep loading this NSMutableArray again and again. The data cannot be changed by the user, and is not updated that often.

I am wondering what best practices are here. My thinking is to set a value for each DB in NSUserDefaults for when the db was last updated. I have background fetch for each database set so I can update that flag every time. Then when I am loading the view I would first check to see if my flag has been set (i.e. data has been updated) and if so load the NSMutableArray, if not grab the existing array. If the NSMutable Array doesn't exist, then of course load it.

Looking for any guidance on what others do....

Bryan

score 0 · Answer 1 · answered Jan 22 '14 at 21:06

I would leave the querying from the database in place. However, I would make 2 changes to what you're currently doing: (1) i would let the database query handle the sorting (e.g., "ORDER BY lastName"), and (2) i would pull back a 'page' of data at a time instead of all the records. This second item would have some complexity of keeping track of which page is current and how to retrieve previous and next, but it should help performance considerably.

It's wasteful on battery life and memory to pull big data sets into memory all of the time, IMO.

Oh, one other thing -- I'd also put the database retrieval in the background with GCD.

score 0 · Answer 2 · answered Jan 22 '14 at 21:15

For the performance issue's , implement lazy loading table view concept. You can see a good example here with images : developer.apple-Sample code.

When some of the rows are modified, better to modify only the particular table view cell. You can do it without reloading the entire table.

Also, you are correct, you can make use of a BOOL flag to decide whether you need to update the data source again.

How to not reload NSMutableArray from SQLite every time when opening iOS TableView

2 Answers2