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

Here's how I'm guessing SQLite performs that query:

1. Find all rows that match one of your LIKE statements.

2. Sort them by DEFIN.

3. Truncate the results after the first 50 rows.

I suspect the truly painful part is the sort; without that, it could simply collect the first 50 matching rows it found. Can you get away with not sorting the definitions?

Answer 2

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.

Answer 3

Try making the search query in a different thread. As UI operations are performed only on the main thread, any time consuming operations done on the main thread will delay the UI operations.

Answer 4

Thank you all for the comments. Most tend to be steering me towards a second thread for the [self search:keyword] call. I guess now is the time to learn more about threading. I'm especially thankful for the comment about UI operations all being in the main thread.

Finally (I haven't googled it yet) can anyone point me to a straightforward thread tutorial?

Thanks all! I've come to love stackoverflow!

-Mitch

@Brent I'll also try it without sort see if that improves it at all.

Answer 5

Because you are using LIKE sqlite won't be using an index and it will sequentially scan from the start till is hits a limit of 50 records in the result set. Depending on the order of your data it could hit the first 50 results in the beginning or at the end worst case.

How about you create new column that stores the first letter of each of the base words in defin ie d for dog etc and index this column.

Modify your query to select * from words where fisrtletter = :firstletter order by defin limit 50

This will cut your search space significantly

The use the like operator for the complete match

you could also pre-order the data so that the order by won't be needed, I suspect the order by will cause the full table to be scanned rather than terminating when the first 50 records are hit

Answer 6

I generally use

[self performSelectorInBackground:@selector(threadedFetch) withObject:nil];

in combination with Core Data's NSFetchedResultsController to start a fetch upon user search input. But be careful when treading. Use separate NSManagedObjectContext for each thread or have a single context which you lock with -[NSManagedObjectContext lock].