2 Replies
      Latest reply: Jan 5, 2017 6:32 AM by ksigiscar@cmc RSS
      nitisha Level 1 Level 1 (0 points)

        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

        • Re: Memory issue with sqlite3 while inserting data recursively
          eskimo Apple Staff Apple Staff (7,005 points)

          It’s likely that your inner loop is building up objects in the autorelease pool.  You should try wrapping the body of thaht loop in autoreleasepool(invoking:).  For example, this:

          for x in bigLongList {
              … do stuff …
          }
          

          would become this:

          for x in bigLongList {
              autoreleasepool {
                  … do stuff …
              }
          }
          

          If that doesn’t just fix the problem, I recommend you run your app using Instruments to see what sort of objects are taking up all the space.

          Share and Enjoy

          Quinn “The Eskimo!”
          Apple Developer Relations, Developer Technical Support, Core OS/Hardware
          let myEmail = "eskimo" + "1" + "@apple.com"

          • Re: Memory issue with sqlite3 while inserting data recursively
            ksigiscar@cmc Level 2 Level 2 (75 points)

            In in my experience, you have to fight requirements that don't make sense technically. There are other ways than loading all the data at once while providing a better user experience. Also, have you considered using Core Data instead ? It uses SQLLite and will solve many of the challenges you are facing.

             

            Check creating an Incremental Store with Core Data.

             

            Also check the SyncDB library on GitHub for Swift JSON synchronization to Core Data.