Speed up MmsSmsDatabaseHelper thread updates
As the database grew, the updateAllThreads() method took way too long.
Overarching changes:
* Rename to updateThreads() -- "all" is technically untrue.
* Changed everything to bulk queries, instead of looping over all
matches, to reduce overhead.
* Turn updateThread() into an updateThreads() call with a
very specific selection.
* Use DISTINCT to reduce duplicates in the subqueries of the delete
(as was already done in updateAllThreads, but not updateThread).
* Used a WITH clause for the date/snippet update. The subquery is
unfortunately still run thrice, but code deduplication, yay!
* Moved subquery WHERE clauses for the date/snippet update
inwards, to allow for proper utilization of indices.
* Speed up the date/snippet subqueries with a (thread_id, date) index.
* Simplified the error update by using the EXISTS operator.
* Changed the error update from three queries (calculate-read-update)
into just one.
* Formatted the new queries the way *I* like them. :)
This can be easily benchmarked from the shell on userdebug:
(but you'll need a fix in appops to make it accept the "root" package)
adb root
adb shell appops set root READ_SMS allow
adb shell appops set root WRITE_SMS allow
adb shell time content delete --uri content://mms-sms/conversations/ --where "0=1"
This, of course, deletes nothing (still, don't risk your data!), but it
does trigger a call to updateAllThreads(db, null, null).
My benchmark: 1000 threads with 1 sms each, 1 thread with 2000 sms.
Before: 0m59.09s real 0m0.26s user 0m0.19s system
After: 0m00.75s real 0m0.23s user 0m0.09s system
Note that most of the "after" time is process-launch and communication
overhead -- setting db.log.slow_query_threshold to 0 and summing up
the queries tells us that we're now spending less than 150 ms on SQL.
Change-Id: I379f6712c2d569226dbeedac2ef2df9e49d42ec7
3 files changed