Speed up VISIBLE_CONTACTS update
The delete used to easily take >1 second with 10K contacts.
Now it's just ~1ms.
It's one of the slowest processes during aggregation.
The old SQL looked like this:
DELETE FROM visible_contacts
WHERE _id NOT IN (SELECT _id FROM contacts WHERE [CONTACT_IS_VISIBLE] = 1)
and _id = CONTACT_ID_IN_QUESTION
The problem is the subquery, especially the [CONTACT_IS_VISIBLE] part, is
pretty slow but it queries *all* visible contact IDs at once,
But this really means "Remove CONTACT_ID_IN_QUESTION from visible_contacts,
if it's not visible."
DELETE FROM visible_contacts
WHERE _id IN
(SELECT _id FROM contacts
WHERE _id = CONTACT_ID_IN_QUESTION and [CONTACT_IS_VISIBLE] = 0)
(We use 'IN' rather than '=' here, because this method is used to
update visible_contacts for *all* contacts as well, in which case
we just remoev the the [_id = CONTACT_ID_IN_QUESTION] part from
the selection.)
Change-Id: I73d629f6b352d010eb1deabb7e23d12130be9b3d
diff --git a/src/com/android/providers/contacts/ContactsDatabaseHelper.java b/src/com/android/providers/contacts/ContactsDatabaseHelper.java
index 074949c..905a87a 100644
--- a/src/com/android/providers/contacts/ContactsDatabaseHelper.java
+++ b/src/com/android/providers/contacts/ContactsDatabaseHelper.java
@@ -4224,7 +4224,7 @@
* Update {@link Contacts#IN_VISIBLE_GROUP} for all contacts.
*/
public void updateAllVisible() {
- updateCustomContactVisibility(getWritableDatabase(), "");
+ updateCustomContactVisibility(getWritableDatabase(), -1);
}
/**
@@ -4245,7 +4245,7 @@
public boolean updateContactVisible(
TransactionContext txContext, long contactId, boolean onlyIfChanged) {
SQLiteDatabase db = getWritableDatabase();
- updateCustomContactVisibility(db, " AND " + Contacts._ID + "=" + contactId);
+ updateCustomContactVisibility(db, contactId);
String contactIdAsString = String.valueOf(contactId);
long mimetype = getMimeTypeId(GroupMembership.CONTENT_ITEM_TYPE);
@@ -4318,26 +4318,38 @@
return mContactInDefaultDirectoryQuery.simpleQueryForLong() != 0;
}
- private void updateCustomContactVisibility(SQLiteDatabase db, String selection) {
+ /**
+ * Update the visible_contacts table according to the current visibility of contacts, which
+ * is defined by {@link Clauses#CONTACT_IS_VISIBLE}.
+ *
+ * If {@code optionalContactId} is non-negative, it'll update only for the specified contact.
+ */
+ private void updateCustomContactVisibility(SQLiteDatabase db, long optionalContactId) {
final long groupMembershipMimetypeId = getMimeTypeId(GroupMembership.CONTENT_ITEM_TYPE);
String[] selectionArgs = new String[]{String.valueOf(groupMembershipMimetypeId)};
+ final String contactIdSelect = (optionalContactId < 0) ? "" :
+ (Contacts._ID + "=" + optionalContactId + " AND ");
+
// First delete what needs to be deleted, then insert what needs to be added.
// Since flash writes are very expensive, this approach is much better than
// delete-all-insert-all.
- db.execSQL("DELETE FROM " + Tables.VISIBLE_CONTACTS +
- " WHERE " + "_id NOT IN" +
- "(SELECT " + Contacts._ID +
- " FROM " + Tables.CONTACTS +
- " WHERE (" + Clauses.CONTACT_IS_VISIBLE + ")=1) " + selection,
+ db.execSQL(
+ "DELETE FROM " + Tables.VISIBLE_CONTACTS +
+ " WHERE " + Contacts._ID + " IN" +
+ "(SELECT " + Contacts._ID +
+ " FROM " + Tables.CONTACTS +
+ " WHERE " + contactIdSelect + "(" + Clauses.CONTACT_IS_VISIBLE + ")=0) ",
selectionArgs);
- db.execSQL("INSERT INTO " + Tables.VISIBLE_CONTACTS +
- " SELECT " + Contacts._ID +
- " FROM " + Tables.CONTACTS +
- " WHERE " + Contacts._ID +
- " NOT IN " + Tables.VISIBLE_CONTACTS +
- " AND (" + Clauses.CONTACT_IS_VISIBLE + ")=1 " + selection,
+ db.execSQL(
+ "INSERT INTO " + Tables.VISIBLE_CONTACTS +
+ " SELECT " + Contacts._ID +
+ " FROM " + Tables.CONTACTS +
+ " WHERE " +
+ contactIdSelect +
+ Contacts._ID + " NOT IN " + Tables.VISIBLE_CONTACTS +
+ " AND (" + Clauses.CONTACT_IS_VISIBLE + ")=1 ",
selectionArgs);
}