优化数千个SQLite查询android [英] Optimise thousands of SQLite queries android

问题描述

我有这项任务，需要查询有关每个联系人的所有详细信息并进行备份.

I have this task where I need to query all details about every contact and back it up.

前770个联系人需要花费5到6秒钟来加载，而770个联系人之后，每个联系人都需要花费一秒钟以上的时间来查询所有数据.我知道这是一个复杂的查询，但是太慢了.有什么方法可以对此进行优化?

The first 770 contacts take up 5-6 seconds to load while after 770 contacts, each contact takes more than a second to query all data. I understand that this is a complex query but that is just too slow. Is there any way to optimize this?

代码

// This contacts array typically contains more than a thousand items in it, even more
contacts.forEach {
val lookupKey = it.lookupKey
queryContacts(lookupKey)
}

fun queryContacts(lookupKey: String) {
    val contact = Contact()
    val contactsCursor = resolver.query(CONTENT_URI, arrayOf(_ID, DISPLAY_NAME, HAS_PHONE_NUMBER), CONTACTS_ALL_DETAILS_SELECTION, arrayOf(lookupKey), null)

    if (contactsCursor.count > 0) {

        while (contactsCursor.moveToNext()) {

            val contactId = contactsCursor.getString(contactsCursor.getColumnIndex(_ID))
            val name = contactsCursor.getString(contactsCursor.getColumnIndex(DISPLAY_NAME))
            contact.name = name

            val hasPhoneNumber = Integer.parseInt(contactsCursor.getString(contactsCursor.getColumnIndex(HAS_PHONE_NUMBER)))

            // This is to read all phone numbers associated with the contact
            val phoneNumbers = arrayListOf<String>()
            if (hasPhoneNumber > 0) {
                val phoneCursor = contentResolver.query(PHONE_CONTENT_URI, null, "$PHONE_CONTACT_ID = ?", arrayOf(contactId), null)
                while (phoneCursor.moveToNext()) {
                    phoneNumbers.add(phoneCursor.getString(phoneCursor.getColumnIndex(NUMBER)))
                }
                phoneCursor.close()
            }
            contact.phoneNumbers.addAll(phoneNumbers)

            // Read every email id associated with the contact
            val emailCursor = contentResolver.query(EMAIL_CONTENT_URI, null, "$EMAIL_CONTACT_ID = ?", arrayOf(contactId), null)

            val emailIds = arrayListOf<String>()
            while (emailCursor.moveToNext()) {

                val email = emailCursor.getString(emailCursor.getColumnIndex(DATA))
                emailIds.add(email)
            }
            emailCursor.close()
            contact.emails.addAll(emailIds)

            val columns = arrayOf(
                    ContactsContract.CommonDataKinds.Event.START_DATE,
                    ContactsContract.CommonDataKinds.Event.TYPE,
                    ContactsContract.CommonDataKinds.Event.MIMETYPE)

            val where = ContactsContract.CommonDataKinds.Event.TYPE + "=" + ContactsContract.CommonDataKinds.Event.TYPE_BIRTHDAY +
                    " and " + ContactsContract.CommonDataKinds.Event.MIMETYPE + " = '" + ContactsContract.CommonDataKinds.Event.CONTENT_ITEM_TYPE + "' and " + ContactsContract.Data.CONTACT_ID + " = " + contactId

            val selectionArgs = arrayOf<String>()
            val sortOrder = ContactsContract.Contacts.DISPLAY_NAME

            val birthdayCur = contentResolver.query(ContactsContract.Data.CONTENT_URI, columns, where, selectionArgs, sortOrder);
            val birthayList = arrayListOf<String>()
            if (birthdayCur.count > 0) {
                while (birthdayCur.moveToNext()) {
                    val birthday = birthdayCur.getString(birthdayCur.getColumnIndex(ContactsContract.CommonDataKinds.Event.START_DATE))
                    birthayList.add(birthday)
                }
            }
            birthdayCur.close()
            contact.dates.addAll(birthayList)
        }

    }

    contactsToBackup.add(contact)
    contactsCursor.close()

}

如您所见，整个复杂的查询过程被调用了1000多次.我该如何改善呢?

As you can see, the whole complex query thing going on gets called more than 1000 times. How can I improve this?

推荐答案

您真的不需要optimise thousands of SQLite queries您需要做的就是将这数千个查询迁移到一个大查询中.

you don't really need to optimise thousands of SQLite queries what you do need is migrate those thousands of queries into one big query.

我假设这部分是

contacts.forEach {
val lookupKey = it.lookupKey
queryContacts(lookupKey)
}

实际上会在设备上的所有联系人上运行，如果是这样，您可以执行以下操作:

actually runs over all contacts on the device, if so, you can do this:

Map<Long, Contact> contacts = new HashMap<>();

String[] projection = {Data.CONTACT_ID, Data.DISPLAY_NAME, Data.MIMETYPE, Data.DATA1, Data.DATA2, Data.DATA3};
String selection = Data.MIMETYPE + " IN ('" + Phone.CONTENT_ITEM_TYPE + "', '" + Email.CONTENT_ITEM_TYPE + "', '" + Event.CONTENT_ITEM_TYPE + "')";
Cursor cur = cr.query(Data.CONTENT_URI, projection, selection, null, null);

while (cur != null && cur.moveToNext()) {
    long id = cur.getLong(0);
    String name = cur.getString(1);
    String mime = cur.getString(2); // email / phone / event
    String data = cur.getString(3); // the actual info, e.g. [phone]
    int type = cur.getInt(4);

    Log.d(TAG, "got " + id + ", " + name + ", " + data);

    Contact contact;
    if (contacts.containsKey(id)) {
        contact = contacts.get(id);
    } else {
        contact = new Contact();
        contact.name = name;
        contacts.put(id, infos);
    }

    switch (mime) {
        case Phone.CONTENT_ITEM_TYPE: 
            contact.addPhone(data, type); // you'll need to add this method
            break;
        case Email.CONTENT_ITEM_TYPE: 
            contact.addEmail(data, type); // you'll need to add this method
            break;
        case Event.CONTENT_ITEM_TYPE: 
            contact.addBirthday(data, type); // you'll need to add this method
            break;
    }
}

这篇关于优化数千个SQLite查询android的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！