优化数以千计的SQLite查询安卓

Question

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

前 770 个联系人需要5-6 秒才能加载，而在 770 个联系人之后，每个联系人需要一秒以上的时间来查询所有数据。我知道这是一个复杂的查询，但这太慢了。有什么方法可以优化这一点吗？

法典

// 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 多次。我怎样才能改善这一点？

Answer 1

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

我假设这部分：

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

实际上会运行设备上的所有联系人，如果是这样，您可以这样做：

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;
}
}