Hi,I've implemented sqlite3.0 library in my project for large data storage.what my requirement is, i've a json file in my bundle and i've to insert data from json file to sqlite database.Data is more than 130000.as my requirement to load all data once and update it after receiving new data from webservice and data is used for autosearching in my application.
Issue: I'm facing is for each insertion memory increase to 4-5 mb and reaches to 1gb after insertion of 500 data and never gets released?
Previously,i was performing reading and writing data from file which was taking too much time to read and map 130000, it was taking more than 35 sec which was worst experience.
Below is my code:
class SQLDataBaseManger: NSObject {
var database: OpaquePointer? = nil
var writableDataBasePath: String?
init(dbfileName fileName:String) {
super.init()
//add code for copying database file to documentary folder
//load db file to document directory
writableDataBasePath = FileManagement.sharedInstance.getDocumentFilePath(fileName)
print(writableDataBasePath ?? "")
if !(FileManager.default.fileExists(atPath: writableDataBasePath!))
{
var sourceFilePath:String? = nil
sourceFilePath = Bundle.main.path(forResource: Constants.sharedInstance.DATABASE_NAME,ofType:"sqlite")!
var targetFile:String? = nil
targetFile = "\(Constants.sharedInstance.DATABASE_NAME).sqlite"
let storeLocation = FileManagement.sharedInstance.copyFile(sourceFilePath!, targetFile: targetFile!, documentDir: FileManagement.sharedInstance.getDocumentPath())
print(storeLocation)
sourceFilePath = nil
targetFile = nil
}
}
func initializeStatement(sqlStatement statement:inout OpaquePointer? , query sqlQuery:String) -> Bool
{
if statement == nil
{
if sqlite3_prepare_v2(database, (sqlQuery as NSString).utf8String, -1, &statement, nil) != SQLITE_OK
{
print("error in queuy\(sqlite3_prepare_v2(database, (sqlQuery as NSString).utf8String, -1, &statement, nil))")
return false
}
}
return true
}
// Function to call for INsert,Update and Delete
func executeUpdate(sqlStatement statement:OpaquePointer?) -> Bool {
_ = executeStatement(sqlStatement: statement, success: Int(SQLITE_DONE))
sqlite3_clear_bindings(statement)
sqlite3_reset(statement)
return true
}
func executeSelect(sqlStatement statement:OpaquePointer?) -> Bool {
return executeStatement(sqlStatement: statement, success: Int(SQLITE_ROW))
}
func executeStatement(sqlStatement statement:OpaquePointer?,success successConstant:Int) -> Bool {
if statement != nil{
let success = Int(sqlite3_step(statement))
if success != successConstant
{
print("Success \(success) and success constant \(successConstant)")
return false
}
}
return true
}
}
//DBModel class
class CompanyDbModel: SQLDataBaseManger {
var insertCompany:OpaquePointer? = nil
var updateCompany:OpaquePointer? = nil
var isAvailableCompany:OpaquePointer? = nil
var deleteCompany:OpaquePointer? = nil
var selectAllCompany:OpaquePointer? = nil
struct Static {
static var onceToken = "dbToken"
static var instance:CompanyDbModel? = nil
}
class func sharedInstance() -> CompanyDbModel
{
DispatchQueue.once(token: Static.onceToken, block: {
Static.instance = CompanyDbModel()
})
return Static.instance!
}
init()
{
super.init(dbfileName: "TabletPLUSDataBase.sqlite")
if sqlite3_open((writableDataBasePath! as NSString).utf8String, &database) == SQLITE_OK
{
_ = initializeStatement(sqlStatement: &insertCompany, query: "INSERT INTO CompanyTable(orgcode,company_data) values(?,?)")
_ = initializeStatement(sqlStatement: &updateCompany, query: "UPDATE CompanyTable SET company_data=? WHERE orgcode=?")
_ = initializeStatement(sqlStatement: &isAvailableCompany, query: "SELECT * FROM CompanyTable WHERE orgcode=?")
_ = initializeStatement(sqlStatement: &deleteCompany, query: "DELETE FROM CompanyTable WHERE orgcode=?")
_ = initializeStatement(sqlStatement: &selectAllCompany, query: "SELECT * FROM CompanyTable")
}
}
func inserAllCompany(companyArray :[Company])
{
//sqlite3_exec(database, "BEGIN TRANSACTION", nil, nil, nil)
sqlite3_exec(database, "PRAGMA synchronous = OFF", nil, nil, nil)
sqlite3_exec(database, "PRAGMA journal_mode = MEMORY", nil, nil, nil)
for (_,item) in companyArray.enumerated()
{
let comStr = item.toJSONString()
sqlite3_bind_text(insertCompany, 1, (item.orgCode! as NSString).utf8String, -1, nil)
sqlite3_bind_text(insertCompany, 2, (comStr! as NSString).utf8String , -1, nil)
_ = executeUpdate(sqlStatement: insertCompany!)
}
}
}
let db = CompanyDbModel()
db.inserAllCompany(<paasing array of 130000 data>)
Please help
