vendredi 19 juin 2015

How to insert bulk data into SQLite database in iOS using swift

I want to insert more than 20000 records to local database SQLite using Swift.I am getting data from json service.I am able to insert records successfully, but the time taking to insert records is above 5 min.I have searched a lot and implemented the begin and commit transactions, but i have not found any change in execution time. Below is the code:

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

Please help me to solve the issue. Thanks in advance.

Aucun commentaire:

Enregistrer un commentaire