Inserting data into FMDB crashes on device, most memory efficient way to insert data into sqlite

Question

I have A LOT of rows that need to be inserted into a database. I download about 50 .txt files that are tab delimited with the information I need. I parse the data and insert it into the database in the same method. When I had it as separate methods, the simulator on my 27" iMac would crash because it ran out of memory. The method looks like this:

- (BOOL)insertDataWithFileName:(NSString *)fileName {
    NSLog(@"%s %@", __FUNCTION__, fileName);
    if (_db == nil) {
        return NO;
    }
    // Get the data from the file
    NSData *data = [[NSData alloc] initWithContentsOfFile:[[self applicationDocumentsDirectory] stringByAppendingPathComponent:fileName]];
    NSString *responseString = [[NSString alloc] initWithData:data encoding:NSUTF8StringEncoding];

    // Parse each line by newlines first
    NSArray *lines = [responseString componentsSeparatedByString:@"\n"];        

    NSString *ID = [[fileName componentsSeparatedByString:@"."] objectAtIndex:0];

    // Set up the database insert statement
    NSString *tableName = @"Hardcodedtablename"; // remove after prototype
    BOOL oldshouldcachestatements = _db.shouldCacheStatements;
    [_db setShouldCacheStatements:YES];
    [_db beginTransaction];
    NSString *insertQuery = [NSString stringWithFormat:@"INSERT INTO %@ values(null, ?, ?, ?, ?, ?, ?, ?);", tableName];
    BOOL success;

    // Parse each line by tabs
    for (NSString *line in lines) {
        NSAutoreleasePool *pool = [[NSAutoreleasePool alloc] init];
        NSArray *fields = [line componentsSeparatedByString:@"\t"];

        // Need to check since some of the .txt files have an empty newline at the EOF.
        if ([fields count] == NUM_VARIANT_FIELDS) {
            NSNumber *loc = [NSNumber numberWithInteger:[[fields objectAtIndex:0] integerValue]];       
            NSString *carType = [fields objectAtIndex:1];
            NSString *model = [fields objectAtIndex:2];
            NSString *AA = [fields objectAtIndex:7];
            NSString *BB = [fields objectAtIndex:8];

            NSNumber *freq = [[fields objectAtIndex:11] isEqualToString:@""] ? [NSNumber numberWithDouble:-1.0] : [NSNumber numberWithDouble:[[fields objectAtIndex:11] doubleValue]];

            NSArray *argArray = [[NSArray alloc] initWithObjects:ID, loc, carType, model, AA, BB, freq, nil];

            success = [_db executeUpdate:insertQuery withArgumentsInArray:argArray];
            [argArray release];

        }
        [pool drain];
    }

    [_patientAnnotatedDatabase commit];
    [_patientAnnotatedDatabase setShouldCacheStatements:oldshouldcachestatements];

    return success;
}

As you can see from my for loop, I actually don't need all the files in the array after it gets separated by the \t. I set up an autorelease pool to reduce the memory footprint in the for loop. The largest file has 270,000 rows in it. There are about 50 of these files. So after 2 files on the device, it crashes. No error message though.

I was wondering what my options were at this point. Can the iPad actually handle that amount of data? Can I make optimizations on this method?

Or would I have to create a database on the server side and download the database to the device instead? If I did download the database from the server to the device, does FMDB support an ability to just transfer those tables from one DB to another? I wasn't able to find anything like that while I was googling. Thanks!

Answer 1

It really depends on what you are using the data for. If you are using it to search or for some purpose where you will really need ALL of it, you are basically using this much memory:

270,000 rows * 25 chars/row * 50 files * 1 byte/char = 321 Mb of RAM

I'm guessing about the 25 chars/row part, but when you are using over 100 Mb of memory, you are using a lot of memory. However, the iPad does have about 1GB of RAM for you to use, so you would seem to be within the amount of available memory. But, because of the NSArrays and SQLite FMDB stuff you are using to store it (as well as other NSObjects), the data with encapsulation could amount to over 1GB, when added to other processes running on the iPad.

So, all of this memory might be your problem. You might be better of saving all of this data on a webserver that can handle it, and call whatever functions you would like through said server. It probably would be much faster, and it would allow you to make adjustments to the data easily, if it needed to be updated. It also would allow your app to be used on devices like the iPhone that do not have enough RAM.

If sheer amount of data is your problem, then your solution is the one above. However, I still believe that the iPad is capable of handling 300+ Mb of RAM, so your problem could lie in the code. Make sure you are accessing all of your data correctly, especially when you are separating the data into arrays from the files, because the smallest "out of bounds" array error could cause your program to fail.

Have you also tried running your code with the memory profiler enabled. You can enable this app in the new Xcode by holding down the "Run" round button until you can select an option that says "Profiler." This app will show you how much memory your app is using, so you can make sure its under 1GB.

Answer 2

Unfortunately, I had NSZombieEnabled set on in my scheme to track down an earlier bug. With it turned off, it seems to be now not crashing at this moment.