iphone - iPhone 上的 sqlite3 - 内存被吞噬？解析大量xml数据时

Question

想象一个包含 5000 组数据的 xml 文件。在解析时，我 NSLog 一个元素，它返回了 5000 组数据，但我有一个循环将数据插入到 sqlite3 数据库中。好像在400+左右就停止插入了。我环顾四周，发现它吞噬了内存？在泄漏工具中，负责的库是 libsqlite3.dylib ，负责的框架是 sqlite3MemMalloc 那么我现在该如何解决呢？下面是我的代码。该方法位于 NSXMLParser didEndElement 方法中。

- (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

你在浪费内存

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

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

当您所做的只是测试 aBedInstance.bedno 的值时，无需创建新实例。 p2 在 block 中的其他地方根本没有使用

删除前两行并替换 for 循环中的以下内容

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];
}

代码还存在其他问题。

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);

您应该为每个语句只调用一次准备。 sqlite3 函数调用 sqlStmt1 将失败，因为您已经关闭了数据库。

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);

请注意，您可以通过使用使用参数的静态 SQL 字符串来提高整体性能，在 for 循环之外准备语句，然后使用 sqlite3_bind_* 例程设置 for 循环内的值。

来自sqlite documentation

语句对象的生命周期是这样的:

1. 使用 sqlite3_prepare_v2() 或相关函数创建对象。
2. 使用 sqlite3_bind_*() 接口(interface)将值绑定(bind)到主机参数。
3. 通过调用 sqlite3_step() 一次或多次来运行 SQL。
4. 使用 sqlite3_reset() 重置语句，然后返回步骤 2。执行此操作零次或多次。
5. 使用 sqlite3_finalize() 销毁对象。