Looking for advice on importing large dataset in sqlite and Cocoa/Objective-C

Question

I have a fairly large hierarchical dataset I'm importing. The total size of the database after import is about 270MB in sqlite. My current method works, but I know I'm hogging memory as I do it. For instance, if I run with Zombies, my system freezes up (although it will execute just fine if I don't use that Instrument). I was hoping for some algorithm advice.

I have three hierarchical tables comprising about 400,000 records. The highest level has about 30 records, the next has about 20,000, the last has the balance. Right now, I'm using nested for loops to import.

I know I'm creating an unreasonably large object graph, but I'm also looking to serialize to JSON or XML because I want to break up the records into downloadable chunks for the end user to import a la carte. I have the code written to do the serialization, but I'm wondering if I can serialize the object graph if I only have pieces in memory.

Here's pseudocode showing the basic process for sqlite import. I left out the unnecessary detail.

[database open];
[database beginTransaction];
NSArray *firstLevels = [[FirstLevel fetchFromURL:url retain];
for (FirstLevel *firstLevel in firstLevels)
{
    [firstLevel save];
    int id1 = [firstLevel primaryKey];
    NSArray *secondLevels = [[SecondLevel fetchFromURL:url] retain];
    for (SecondLevel *secondLevel in secondLevels)
    {
        [secondLevel saveWithForeignKey:id1];
        int id2 = [secondLevel primaryKey];
        NSArray *thirdLevels = [[ThirdLevel fetchFromURL:url] retain];
        for (ThirdLevel *thirdLevel in thirdLevels)
        {
            [thirdLevel saveWithForeignKey:id2];
        }
        [database commit];
        [database beginTransaction];
        [thirdLevels release];
    }
    [secondLevels release];
}
[database commit];
[database release];
[firstLevels release];

Answer 1

You might take a look at setting up a local autorelease pool inside your loop.

[database open];
[database beginTransaction];
NSArray *firstLevels = [[FirstLevel fetchFromURL:url retain];
for (FirstLevel *firstLevel in firstLevels)
{
    NSAutoreleasePool * pool = [[NSAutoreleasePool alloc] init];

...

    [pool release];
}