inserting into sqlite database taking too much time - iOS

Question

I have 6000 records of PhoneBook I'm inserting into sqlite its taking 45 seconds, that is huge time.

for each record I want only few properties like name, email, id, modified date. So atleast one for loop i need because of that its taking 45 seconds. How can i reduce?

Here is the updated code (This code is running in dispatch_async)

I also checked similar problem How to insert 40000 records fast into an sqlite database in an iPad solution says I've to use BEGIN & END transaction that i used already but still facing the same.

UPDATE - As per suggested solutions i've updated my code but still its taking 45 seconds. Please help me.

    sqlite3_exec(db.insertPerson, "BEGIN TRANSACTION", nil, nil, nil)

    for record:ABRecordRef in contactList
    {


        contactNumber = ""
        email = ""
        fullName = ""


        if (ABRecordCopyValue(record,kABPersonPhoneProperty) != nil) && (ABRecordCopyValue(record,kABPersonFirstNameProperty) != nil)

        {

                firstName = (ABRecordCopyValue(record, kABPersonFirstNameProperty)?.takeRetainedValue() as? String)!

                let numbers:ABMultiValue = ABRecordCopyValue(record, kABPersonPhoneProperty).takeRetainedValue()

               if (ABMultiValueGetCount(numbers) > 0)
               {
                    contactNumber = (ABMultiValueCopyValueAtIndex(numbers,0)?.takeRetainedValue() as? String)!

               }


                let modificationNSDate = (ABRecordCopyValue(record, kABPersonModificationDateProperty)?.takeRetainedValue())! as! NSDate


                modificationDate = dateFormatter.stringFromDate(modificationNSDate)

                recordId = ABRecordGetRecordID(record)

               if (ABRecordCopyValue(record,
                   kABPersonLastNameProperty) != nil)
               {

                   lastName = (ABRecordCopyValue(record,
                        kABPersonLastNameProperty).takeRetainedValue()as? String)!

                }


                let emails: ABMultiValueRef = ABRecordCopyValue(record, kABPersonEmailProperty).takeRetainedValue()

                for (var i = 0; i < ABMultiValueGetCount(emails); i++)
                {
                    email = ABMultiValueCopyValueAtIndex(emails, i).takeRetainedValue() as! String

                }


        }


        fullName = "\(firstName) \(lastName)";
        lastName = "";


   db.insertIntoContact(contactName: fullName, contactNumber: contactNumber, contactEmail: email, recordid : recordId, modifieddate: modificationDate)

    }

    sqlite3_exec(db.insertPerson, "END TRANSACTION", nil, nil, nil)

Here is insertIntoContact func.

  func insertIntoContact(contactName contactName : String!, contactNumber : String!, contactEmail : String!, recordid:Int32!, modifieddate:String! ) -> Bool
   {
    sqlite3_bind_text(insertPerson, 1, (contactName as NSString).UTF8String, -1, nil)
    sqlite3_bind_text(insertPerson, 2, (contactNumber as NSString).UTF8String, -1, nil)
    sqlite3_bind_text(insertPerson, 3, (contactEmail as NSString).UTF8String, -1, nil)
    sqlite3_bind_int(insertPerson, 4, Int32(recordid))
    sqlite3_bind_text(insertPerson, 5, (modifieddate as NSString).UTF8String, -1, nil)
    return executeUpdate(sqlStatement: insertPerson)
}

For more details

func executeUpdate(sqlStatement statement:COpaquePointer) -> Bool
    {
        let resultCode = executeStatement(sqlStatement: statement, success:Int(SQLITE_DONE))
        sqlite3_reset(statement)
        return resultCode
    }



func executeStatement(sqlStatement statement:COpaquePointer,success successConstant:Int) -> Bool
{
    let success = Int(sqlite3_step(statement))

    if success != successConstant
    {
        print("Statement \(successConstant) failed with error \(success)")
        return false
    }

    return true
}

Answer 1

1st Problem (NSDateFormatter allocations):

You are creating a new instance of NSDateFormatter on every loop. That means you created it 6000 times.....and it's really expensive to create an instance. So move that out of the loop. (see code sample below)

2nd Problem (Use transactions):

Then you need to begin the transaction at this point as the previous answer suggested. After you have looped through the contacts you end the transaction as suggested from previous answer.

Some pseudo code that needs better error checking:

I've put all sqlite related functions in the loop so it's easier to see what exactly is going on. But you really need to find out what is taking time, because you should have seen an increase in performance using transactions.

struct Contact
{
    let name: String
    let number: String
    let email: String
    let modificationDate: String
    let id: Int32
}

Method to get contact from ABRecordRef

func contactFromABRecordRef(record: ABRecordRef, dateFormatter: NSDateFormatter) -> Contact?
{
    var email = ""
    var contactNumber = ""
    var firstName = ""
    var lastName = ""
    var modificationDate = ""
    var id: Int32 = -1

    if (ABRecordCopyValue(record, kABPersonPhoneProperty) != nil)
    {
        let modificationNSDate = (ABRecordCopyValue(record, kABPersonModificationDateProperty)?.takeRetainedValue())! as! NSDate
        modificationDate = dateFormatter.stringFromDate(modificationNSDate)
        id = ABRecordGetRecordID(record)

        let numbers: ABMultiValue = ABRecordCopyValue(record, kABPersonPhoneProperty).takeRetainedValue()
        if (ABMultiValueGetCount(numbers) > 0)
        {
            contactNumber = (ABMultiValueCopyValueAtIndex(numbers,0)?.takeRetainedValue() as? String)!
        }

        if (ABRecordCopyValue(record, kABPersonFirstNameProperty) != nil)
        {
            firstName = (ABRecordCopyValue(record, kABPersonFirstNameProperty)?.takeRetainedValue() as? String)!
        }

        if (ABRecordCopyValue(record, kABPersonLastNameProperty) != nil)
        {
            lastName = (ABRecordCopyValue(record, kABPersonLastNameProperty).takeRetainedValue()as? String)!
        }

        let emails: ABMultiValueRef = ABRecordCopyValue(record, kABPersonEmailProperty).takeRetainedValue()
        for (var i = 0; i < ABMultiValueGetCount(emails); i++)
        {
            email = ABMultiValueCopyValueAtIndex(emails, i).takeRetainedValue() as! String
        }

        return Contact(name: "\(firstName) \(lastName)", number: contactNumber, email: email, modificationDate: modificationDate, id: id)
    }

    return nil
}

Update the loop to something similar to yours

// Load your contact list from here
let contactList: [ABRecordRef] = []
let dateFormatter: NSDateFormatter = NSDateFormatter()
dateFormatter.dateFormat = "yyyy-MM-dd HH:mm:ss"

sqlite3_exec(db, "BEGIN TRANSACTION", nil, nil, nil)
var statement: COpaquePointer = nil
if sqlite3_prepare_v2(db, "insert into contacts values (?1, ?2, ?3, ?4, ?5)", -1, &statement, nil) != SQLITE_OK
{
    let errmsg = String.fromCString(sqlite3_errmsg(db))
    // Handle the error message here!!!!
    print("Error when preparing statement: ", errmsg)
}

for record: ABRecordRef in contactList
{
    if let contact = contactFromABRecordRef(record, dateFormatter: dateFormatter)
    {
        sqlite3_bind_text(statement, 1, (contact.name as NSString).UTF8String, -1, nil)
        sqlite3_bind_text(statement, 2, (contact.number as NSString).UTF8String, -1, nil)
        sqlite3_bind_text(statement, 3, (contact.email as NSString).UTF8String, -1, nil)
        sqlite3_bind_int(statement, 4, Int32(contact.id))
        sqlite3_bind_text(statement, 5, (contact.modificationDate as NSString).UTF8String, -1, nil)

        if sqlite3_step(statement) != SQLITE_DONE
        {
            let errmsg = String.fromCString(sqlite3_errmsg(db))
            // Handle the error message here!!!!
            print("Error when stepping through statement: ", errmsg)
        }

        sqlite3_reset(statement)
    }
}

if sqlite3_exec(db, "COMMIT TRANSACTION", nil, nil, nil) != SQLITE_OK
{
    let errmsg = String.fromCString(sqlite3_errmsg(db))

    print("Error when commiting transaction: ", errmsg)
}

sqlite3_finalize(statement)

Answer 2

You need to use BEGIN TRANSACTION before starting to iterate over 6000 records and END TRANSACTION after all entries addition has been issued - this way you will lower I/O load and make things happened faster.