Sqlite + 80K rows + LIKE = keyboard lag

Question

I've had this problem that I have been putting off solving, but now is the time.

I have a basic dictionary program. It has a UISearchBar and a UITableView. It works the way that it should except when running on the device it causes Keyboard lag. (Simulator is fine, of course) I have two types of searching. As-you-type and On-return. I find that both take about the same amount of time to return results, but the As-you-type makes the keyboard lag.

I have UISearchBar textDidChange that takes the searchText and sends it to a search method that does all the sqlite lifting, puts the results in an Array. The reloads the table.

- (void)searchBar:(UISearchBar *)searchBar textDidChange:(NSString *)searchText{
    if((searchType == SEARCH_AS_YOU_TYPE) && ([searchText length] >= 2)){
        NSString *myKeyword = [NSString stringWithFormat:@"%@", searchText];
        [self search:myKeyword];
        [myTableView reloadData];
    }
}

I limit the results to 50. And I my SQL query uses the LIKE and OR, no way around that yet.

SELECT WORD_ID, DEFIN, PINYIN, SIMP, TRAD from WORDS where DEFIN LIKE "%to dog %" OR DEFIN LIKE "%dog" OR DEFIN LIKE "%dog%"  ORDER BY DEFIN LIMIT 50

I've also tried moving the [myTableView reloadData] into the search method, in hopes that the keyboard would at least not lag. No Joy. And sadly I know that sqlite is basically checking every row, when it uses the like operator. But 3-4 seconds for 80rows seems kinda slow.

Any thoughts, ideas, comments or suggestions would be greatly appreciated!

Answer 1

It sounds to me that you are searching and ready the keyboard in the same thread. This way you will search as many times as you have characters and the typing speed is limited to the search speed.

A proper solution is to separate this into two threads, one to read and display the keyboard, the second to search and display the search results. This way, if you are typing fast er than you can search, only the search will lag, but not the typing. For example the Firefox address bar works that way.

Your code will be more complex due to the two threads and the communication/coordination between the two, but I think this is the only good solution.