Large operations in sync databases
kent at caspia.com
Mon Jul 12 19:41:49 UTC 2010
This message is primarily for asuth, but I post it here because it
might be of general interest.
Background: After discussing issues of usage of sqlite databases for
message metadata with asuth in my blog posting at
I decided to attempt to get around possible blocking of short sync
access by long async queries by dividing my message database into two
separate files with independent connections. One file "db" has the
metadata represented as a single text field, accessed by an indexed
string itemId. This file will be accessed in sync operations, that would
typically just be to get the text metadata blob for a single message
given its itemId. The second file "queries" is used for async queries
that could span many messages, such as "give me a list of all item ids
of offline items that need syncing to the server". The idea is that by
separating the two functions, I might be able to get adequate sync
performance for simple retrieval of the message metadata of a single
message. I will manually keep any common fields between the two files in
sync with each other.
Now the questions.
1) Any suggestions about what I do about large operations with lots of
messages? For example, deleting a folder requires me to obtain a long
list of itemIds, and delete those messages from the supposedly sync
metadata store. How do I do that on the UI thread? One possibility is to
setup an async delete loop, one message per loop, that would delete
these messages. Another might be to use sync deletes, one per messages,
with some sort of manual yielding function. So the basic question is,
how would you recommend that I setup a delete of a large number of
messages from an sqlite store that does not block possible other sync
calls that might be UI sensitive?
2) If I setup a db file primarily for sync access, is there any reason
to avoid all async statements on that database? Or could I also add some
short async statements (such as the proposed delete loop above) without
compromising the ability to prevent blocking of the UI thread?
More information about the tb-planning