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