sqlite3 on iPhone - memory gobbled up? while parsing large amounts of xml data

Question

Hi guys, imagine an xml file with 5000 sets of data. While parsing , i NSLog an element and it returned me 5000 sets of data, but i have a loop that is inserting the data into the sqlite3 database. It seems to stopped inserting around after 400+. I looked around and found out that it gobbles up memory?in the leaks instrument, the responsible library was libsqlite3.dylib and the Responsible frame was sqlite3MemMalloc so how do i solve it now? below is my code. The method is in NSXMLParser didEndElement method.

- (void)parser:(NSXMLParser *)parser didEndElement:(NSString *)elementName namespaceURI:(NSString *)namespaceURI qualifiedName:(NSString *)qName{     
//NSLog(@"ended element: %@", elementName);
MedicalBedAppDelegate *appDelegate = (MedicalBedAppDelegate *)[[UIApplication sharedApplication] delegate];

if ([elementName isEqualToString:@"Status"]) {

    //setup some globals
    databaseName = @"MedicalBedDatabase.sql";

    NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDir = [documentPaths objectAtIndex:0];
    databasePath = [documentsDir stringByAppendingPathComponent:databaseName];

    //Method Stackoverflow
    sqlite3 *database;
    NSInteger i;
    NSString *p = [[[NSString alloc]init] autorelease];
    NSString *p2 = [[[NSString alloc]init] autorelease];

    NSString *str = [NSString stringWithFormat:@"INSERT INTO UsageData(Date,Time,NoOfMovementRegistered,BreathingRate,TimeSinceLastMovement,Status,bID) VALUES ('%@','%@','%@','%@','%@','%@','%@')",currentDate,currentTime,currentNoMove,currentNoBreathe,currentTimeSinceLastMovement,currentStatus,currentBedNo];
    const char *sqlStmt = [str UTF8String];
    sqlite3_stmt *cmp_sqlStmt;

    NSString *str1 = [NSString stringWithFormat:@"INSERT INTO XMLEntryID(EntryID,bID) VALUES ('%@','%@')",currentEntryID ,p];
    const char *sqlStmt1 = [str1 UTF8String];
    sqlite3_stmt *cmp_sqlStmt1;
    if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) {
    for (i = 0; i<([appDelegate.beds count]) ; i++) {
        Bed *aBedInstance = (Bed *)[appDelegate.beds objectAtIndex:i];
        EntryID *aEntryIDInstance = (EntryID *)[appDelegate.entryids objectAtIndex:i];

        p = aBedInstance.bedno;
        p2 = aEntryIDInstance.bedID;

        if ([p intValue] == [currentBedNo intValue]) {
            if ([aEntryIDInstance.bedID intValue] == [currentBedNo intValue])
                    {
                      if ([aEntryIDInstance.entryID intValue] > [currentEntryID intValue] && [aEntryIDInstance.bedID intValue] == [currentBedNo intValue]) {
                          //NSLog(@"xmlEntryID is lower then dBCount");
                      } 

                      else if ([aEntryIDInstance.entryID intValue] == [currentEntryID intValue] && [aEntryIDInstance.bedID intValue] == [currentBedNo intValue])
                      {
                          //This if else if statement is needed because if the dbCount == currentEntryID , it would still consider it 
                          // to be dbCount < currentEntryID
                          //NSLog(@" IT IS EQUAL ");    
                      }
                      else if ([aEntryIDInstance.entryID intValue] < [currentEntryID intValue] && [aEntryIDInstance.bedID intValue] == [currentBedNo intValue] )  {
                          ////NSLog(@"dBCount at selectionScreen = %d",[[appDelegate.dBCount objectAtIndex:0] intValue]);
                          //NSLog(@"currentEntryID at selectionScreen = %d",[currentEntryID intValue]);

                          //NSLog(@"xmlEntryID is higher then dBCount");




                              if(sqlite3_prepare_v2(database, sqlStmt, -1, &cmp_sqlStmt, NULL) == SQLITE_OK)
                              {
                                  int returnValue = sqlite3_prepare_v2(database, sqlStmt, -1, &cmp_sqlStmt, NULL);
                                  ((returnValue ==SQLITE_OK)? NSLog(@"INSERT into USAGEDATA SUCCESS") : NSLog(@"INSERT into USAGEDATA Fail"));
                                  sqlite3_step(cmp_sqlStmt);
                              }
                              sqlite3_finalize(cmp_sqlStmt);
                              sqlite3_close(database);


                              if(sqlite3_prepare_v2(database, sqlStmt1, -1, &cmp_sqlStmt1, NULL) == SQLITE_OK)
                              {
                                  int returnValue = sqlite3_prepare_v2(database, sqlStmt, -1, &cmp_sqlStmt1, NULL);
                                  ((returnValue ==SQLITE_OK) ? NSLog(@"INSERT into XMLEntryID SUCCESS") : NSLog(@"INSERT into XMLEntryID Fail"));
                                  sqlite3_step(cmp_sqlStmt1);
                              }
                              sqlite3_finalize(cmp_sqlStmt1);
                              sqlite3_close(database);

                      }
            } 
        }else if ([p intValue] != [currentBedNo intValue]) {

        }
   }
    }
    NSLog(@"adding currentEntryID: %@", currentEntryID);

    sqlite3_close(database);

}

}

Answer 1

Hi guys, i solved it, instead of using the prepare statement, i used exec statement instead

if (sqlite3_exec(database, [str UTF8String], NULL, NULL, &errorMsg)!=SQLITE_OK) {
                              NSAssert1(0,@"Error updating tables: %s",errorMsg);
                              sqlite3_free(errorMsg);
                          }
                        sqlite3_close(database);

Answer 2

You are wasting memory

NSString *p = [[[NSString alloc]init] autorelease];
NSString *p2 = [[[NSString alloc]init] autorelease]

....
p = aBedInstance.bedno;
p2 = aEntryIDInstance.bedID;

There is no need to create a new instance when all you are doing is testing the value of aBedInstance.bedno. p2 is not used elsewhere at all in the block

Remove the first two lines and replace the following in your for loop

for (... ) {

    NSString *p = [aBedInstance.bedno retain]; // thread safety
    NSString *p2 = [aEntryIDInstance.bedID retain]; // FIXME: p2 is not used in the scope of this method

    if (....) {
        ....
    }
    ....
    [p release];
    [p2 release];
}

There are other problems with the code.

if(sqlite3_prepare_v2(database, sqlStmt, -1, &cmp_sqlStmt, NULL) == SQLITE_OK) {
    int returnValue = sqlite3_prepare_v2(database, sqlStmt, -1, &cmp_sqlStmt, NULL);
    ((returnValue ==SQLITE_OK)? NSLog(@"INSERT into USAGEDATA SUCCESS") : NSLog(@"INSERT into USAGEDATA Fail"));

    sqlite3_step(cmp_sqlStmt);
}
sqlite3_finalize(cmp_sqlStmt);
sqlite3_close(database);

if(sqlite3_prepare_v2(database, sqlStmt1, -1, &cmp_sqlStmt1, NULL) == SQLITE_OK) {
    int returnValue = sqlite3_prepare_v2(database, sqlStmt, -1, &cmp_sqlStmt1, NULL);
    ((returnValue ==SQLITE_OK) ? NSLog(@"INSERT into XMLEntryID SUCCESS") : NSLog(@"INSERT into XMLEntryID Fail"));
    sqlite3_step(cmp_sqlStmt1);
}
sqlite3_finalize(cmp_sqlStmt1);
sqlite3_close(database);

You should call prepare only once for each statement. The sqlite3 function calls for sqlStmt1 will fail because you have already closed the database.

if (SQLITE_OK != sqlite3_prepare_v2(database, sqlStmt, -1, &cmp_sqlStmt, NULL)) {
    NSLog(@"Prepare INSERT into USAGEDATA FAILED");
} else {
    if (SQLITE_DONE != sqlite3_step(cmp_sqlStmt)) {
        NSLog(@"INSERT into USAGEDATA FAILED");
    } else { 
        NSLog(@"INSERT into USAGEDATA SUCCEEDED");
    }
    sqlite3_finalize(cmp_sqlStmt);
}

if (SQLITE_OK != sqlite3_prepare_v2(database, sqlStmt, -1, &cmp_sqlStmt1, NULL)) {
    NSLog(@"Prepare INSERT into XMLEntryID FAILED");
} else {
    if (SQLITE_DONE != sqlite3_step(cmp_sqlStmt1)) {
        NSLog(@"INSERT into XMLEntryID FAILED");
    } else { 
        NSLog(@"INSERT into XMLEntryID SUCCEEDED");
    }
    sqlite3_finalize(cmp_sqlStmt1);
}

sqlite3_close(database);

Note that you can improve the overall performance of this by using static sql strings that use parameters, prepare the statements outside of for loop then use sqlite3_bind_* routines to set the values inside the for loop.

From the sqlite documentation

The life of a statement object goes something like this:

1. Create the object using sqlite3_prepare_v2() or a related function.
2. Bind values to host parameters using the sqlite3_bind_*() interfaces.
3. Run the SQL by calling sqlite3_step() one or more times.
4. Reset the statement using sqlite3_reset() then go back to step 2. Do this zero or more times.
5. Destroy the object using sqlite3_finalize().