插入到sqlite数据库需要花费太多时间——iOS

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

我把6000个电话簿记录插入到sqlite中只要45秒，时间太长了。

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?

对于每条记录，我只需要一些属性，比如名称、电子邮件、id、修改日期。所以我至少需要一个for循环因为它需要45秒。我怎么能减少?

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

这是更新后的代码(此代码在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.

我还检查了类似的问题:如何在iPad解决方案中快速将40000条记录插入到一个sqlite数据库中。

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

更新——根据建议的解决方案，我更新了我的代码，但仍然需要45秒。请帮助我。

    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.

这是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
}

2 个解决方案

#1

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.

您需要在开始迭代超过6000条记录和所有添加条目之后的结束事务之前使用BEGIN事务—这样您将降低I/O负载并使事情发生得更快。

#2

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)

您正在每个循环上创建一个新的NSDateFormatter实例。这意味着你创造了6000倍的数据。创建实例非常昂贵。把它移出循环。(参见下面的代码示例)

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.

我已经将所有与sqlite相关的函数放在循环中，这样就更容易看到实际情况。但是，您确实需要找出什么需要花费时间，因为您应该已经看到了使用事务的性能提高。

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)