iphone sdk sqlite lookup performance for +40k records

Question

hey guys i wanted to know the best way to get this thing done:

i have a huge table with +40k records (tv show titles) in sqlite and i want to do real time lookups to this table. for eg if user searches for a show, as and when user enters search terms i read sqlite and filter records after every keystroke (like google search suggestion)

my performance benchmark is 100 milliseconds. few things i have thought of are: creating indexes, splitting the data into multiple tables.

however i would really appreciate any suggestions to achieve this in the fastest possible time so i can avoid any ui refresh delays - it would be awesome to have feedback from coders who have already done something similar

thanks in advance

Answer 1

Avoid doing any joins, try to use paging so that you keep the amount of data returned to a minimum. Perhaps you should try loading the whole thing into memory, then sort and do binary search? If it is just a list of show titles it would fit?

Answer 2

Keep "perceived performance" in mind - doing lookups right after a key is hit is could be somewhat expensive. How many milliseconds does it take a user to hit a key, though? You can probably get away with not updating the resultlist until the user hasn't typed anything for several hundred milliseconds. (For really fast users, perhaps update every X hundred millisecodns while he's still typing).

Answer 3

Things to do:

1. Index fields appropriately.
2. Limit yourself to only 10-15 records on the initial query—that should be enough to populate the top of the table view.
3. If you don't need to sort, don't. If you do need to sort, sort on an indexed field.
4. Do as much as you can in SQLite rather than your own code.
5. Do as little as you can overall.

You'll likely find what I have: SQLite and the iPhone are actually amazingly capable as long as you don't do anything really dumb.

Answer 4

How do you know the performance will be bad? 40k rows is not that much, even for an iPhone... try it on the phone before you optimize.