Large operations in sync databases

Kent James kent at
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 mailing list