ios - 如何使用 swift 将批量数据插入 iOS 中的 SQLite 数据库

Question

我想使用 Swift 将超过 20000 条记录插入到本地数据库 SQLite。我正在从 json 服务获取数据。我能够成功插入记录，但是插入需要超过 5 分钟。我已经搜索了很多并且实现了开始和提交交易，但我没有发现执行时间有任何变化。下面是代码:

func saveTeamDepartmentValueData(teamDepartmentValueArray : NSMutableArray) {
    var insertTeamDepartmentValueDataQuery : String = String()

    if (teamDepartmentValueArray.count > 0) {
        var errMsg:UnsafeMutablePointer<Int8> = nil
        sqlite3_exec(database, "BEGIN TRANSACTION", nil, nil, &errMsg)

        insertTeamDepartmentValueDataQuery = "INSERT OR IGNORE INTO store_feedback_form_kpi (team_id, department_id,value_id,ordinal,sub_category_id,team_department_value_id) VALUES (?, ?, ?, ?, ?, ?)"

        println("insertTeamDepartmentValueDataQuery is \(insertTeamDepartmentValueDataQuery)")
        var cSql = insertTeamDepartmentValueDataQuery.cStringUsingEncoding(NSUTF8StringEncoding)
        var result:CInt=0
        var statement:COpaquePointer = nil
        var path = getPath()
        var dbpath = path.cStringUsingEncoding(NSUTF8StringEncoding)
        let check = sqlite3_open(dbpath!, &database)
        var teamDepartmentValueDict : NSDictionary = NSDictionary()

        sqlite3_prepare_v2(database, cSql!, -1, &statement, nil);

        for teamDepartmentValueDict in teamDepartmentValueArray
        {
            var teamDepartmentValueData : TeamDepartmentValue = TeamDepartmentValue ()
            if var teamId = teamDepartmentValueDict.valueForKey("team_id") as? String
            {
                teamDepartmentValueData.teamId = teamId.toInt()!
            }
            else{
                teamDepartmentValueData.teamId = 0
            }
            if var departmentId = teamDepartmentValueDict.valueForKey("department_id") as? String
            {
                teamDepartmentValueData.departmentId = departmentId.toInt()!
            }
            else{
                teamDepartmentValueData.departmentId = 0
            }
            if var valueId = teamDepartmentValueDict.valueForKey("value_id") as? String
            {
                teamDepartmentValueData.valueId = valueId.toInt()!
            }
            else{
                teamDepartmentValueData.valueId = 0
            }
            if var ordinal = teamDepartmentValueDict.valueForKey("ordinal") as? String
            {
                teamDepartmentValueData.ordinal = ordinal.toInt()!
            }
            else{
                teamDepartmentValueData.ordinal = 0
            }
            if var subCategoryId = teamDepartmentValueDict.valueForKey("sub_category_id") as? String
            {
                teamDepartmentValueData.subCategoryId = subCategoryId.toInt()!
            }
            else
            {
                teamDepartmentValueData.subCategoryId = 0
            }
            if var departmentValueId = teamDepartmentValueDict.valueForKey("team_department_value_id") as? String
            {
                teamDepartmentValueData.teamDepartmentValueId = departmentValueId.toInt()!
            }
            else{
                teamDepartmentValueData.teamDepartmentValueId = 0
            }

            sqlite3_bind_int(statement, CInt(1), CInt(teamDepartmentValueData.teamId))
            sqlite3_bind_int(statement, CInt(2), CInt(teamDepartmentValueData.departmentId))
            sqlite3_bind_int(statement, CInt(3), CInt(teamDepartmentValueData.valueId))
            sqlite3_bind_int(statement, CInt(4), CInt(teamDepartmentValueData.ordinal))
            sqlite3_bind_int(statement, CInt(5), CInt(teamDepartmentValueData.subCategoryId))
            sqlite3_bind_int(statement, CInt(6), CInt(teamDepartmentValueData.teamDepartmentValueId))

            result = sqlite3_step(statement)

            if(result != SQLITE_DONE)
            {
                println("failed to insert")
            }
            else
            {
                println("inserted")
            }
            sqlite3_clear_bindings(statement);
            sqlite3_reset(statement);
        }
        sqlite3_exec(database, "COMMIT TRANSACTION", nil, nil, &errMsg)
        sqlite3_exec(database, "END TRANSACTION",  nil, nil, &errMsg)
        sqlite3_finalize(statement)
        sqlite3_close(statement)
    }
}

请帮我解决这个问题。提前致谢。

Answer 1

这可能不是问题的确切答案。

由于您有大约 20000 条记录要插入，因此需要相当长的时间。我想到可以减少时间的一件事是使用 GCD 中的 dispatch_apply 方法。您可以查看 this 的 同时执行循环迭代 部分链接。

有了它，您将能够在并发队列上并发执行许多循环。这可以使您的执行时间更短。我不确定它能跑多快，但你可以试一试。

还要确保不要在单独的任务 block 上执行每个循环，而是将它们分成相当大的 block ，以避免安排这些任务的不必要的开销。