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
}

I assume you read [this question](http://stackoverflow.com/q/1711631/335858), right? — Sergey Kalinichenko, Dec 01 '15 at 13:34
@dasblinkenlight As mentioned in that question i have to use transaction, i used as above updated question but its taking same time.In that question there are many ways to do like using prepared statement, PRAGMA synchronous = OFF. How can i achieve with reduced time? — Shrikant K, Dec 03 '15 at 10:38
No! i didn't. There are 3 func. i.e. insertIntoContact, executeUpdate, executeStatement. & just before for loop i used transaction. — Shrikant K, Dec 03 '15 at 10:59

score 4 · Accepted Answer · answered Nov 25 '15 at 11:56

4

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.

answered Nov 25 '15 at 11:56

Serhii Mamontov

4,942
22
26

where should update my code ? because i'm using begin and end for each iteration i think that is useless. I need to call insertIntoContact for each record. – Shrikant K Nov 25 '15 at 12:05
As I said, you need to use them outside of your `for record:ABRecordRef in contactList` cycle and remove from `insertIntoContact ` itself. Call `sqlite3_exec(insertPerson, "BEGIN TRANSACTION", nil, nil, nil)` before `for` and `sqlite3_exec(insertPerson, "END TRANSACTION", nil, nil, nil)` after it - this will allow to perform very few I/O operations and should be much faster. – Serhii Mamontov Nov 25 '15 at 12:16
as u suggested i've updated my code but still taking 45 seconds.? – Shrikant K Dec 01 '15 at 10:28

Gwynant Jones · Answer 2 · 2015-12-01T13:36:32.150

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)

as u suggested i've updated my code but still taking 45 seconds.? — Shrikant K, Dec 01 '15 at 10:28
K. Shrikant I've updated my answer......take a look. But essentially you need to find out what's taking so long. Also I haven't tested this myself. — Gwynant Jones, Dec 01 '15 at 13:37