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 it is taking morethan 5 minutes to insert.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.
This may not be exact answer to the problem.
As you have around 20000 records to insert it will take quite a long time. One thing that comes to my mind that could reduce time is using
dispatch_apply
method from GCD. You could check outPerforming Loop Iterations Concurrently
section of this link.With that you would be able to execute many loops concurrently on a concurrent queue. That could make your execution time shorter. I am not sure how much fast it will make, but you could give it a shot.
Also make sure not to execute each of your loop on separate task block but club them in considerable chunk to avoid unnecessary overhead of scheduling these tasks.