text search too slow on sqlite db on tableview on iphone

Question

hi,

I have a large table of around 2500 entries. I am displaying it on tableview. however the search bar is too slow while doing dynamic search. ie I am filtering the table everytime the user puts in a character on search bar.

following is the code:

- (void)searchBar:(UISearchBar *)theSearchBar textDidChange:(NSString *)searchText {
if([searchText length] > 0) {
    searching = YES;
    letUserSelectRow = YES;
    self.tableView.scrollEnabled = YES;
    [self searchTableView];
} else {
    searching = NO;
    letUserSelectRow = NO;
    self.tableView.scrollEnabled = NO;
    [whereClause setString: @"%%"];
}

[self.tableView reloadData];
   }


- (void) searchTableView {

NSString *searchText = searchBar.text;

[whereClause setString: @"%%"];
[whereClause appendString: searchText];
[whereClause appendString: @"%%"];

[self.tableView reloadData];
}

the whereClause is there in the sqlite query, so it keeps appending the search character. When the user types in the keyboard keys become quite sticky and slow to type. Any suggestion will be appreciated...

Answer 1

After the first character, copy the resulting data into an array and then filter the array using a predicate. It's much faster. Then reload your table from the array. The array might be an array of dictionaries where each element is a dictionary with the search string (i.e. name or whatever) and the other entry is a reference to the core data entity that you want as the final selection. Or you can just so the core data selection at the end when the user makes a selection.

Note that when you filter with a predicate, the objects in the array need to have properties that match what you are searching for. In my example, I created an object that had properties fullName emailAddressString etc..

predicate = [NSPredicate predicateWithFormat:@"fullName contains[cd] %@ or emailAddressString contains[cd] %@", searchString, searchString];

NSArray *resultArray = [[NSArray alloc] initWithArray:[allContacts filteredArrayUsingPredicate:predicate]];

Answer 2

SQLite's selects are usually very fast. If you posted some sample queries, it'd probably be easier to help you.

However, that said: If you have a query with a simple WHERE clause that's performing slowly, you probably don't have an index on the column that's being searched. Add one.

2500 entries in a table is NOT large for SQLite. 25,000,000 would be large.

When you're working with SQLite, you can test a lot of things on the desktop using the sqlite3 shell. For instance, if your query is something like:

SELECT * FROM MyTable WHERE Column='A';

You can do something like this:

EXPLAIN QUERY PLAN SELECT * FROM MyTable WHERE Column='A';

If you see output like this:

0|0|TABLE MyTable

It means SQLite is crawling the entire table to get the results.

In this case, adding an index would help:

CREATE INDEX MyTableColumn ON MyTable(Column);

Then, the explain above will give you something like this instead:

0|0|TABLE MyTable WITH INDEX MyTableColumn

If your WHERE clause is more complicated, you might need to make a compound index instead, since SQLite will only use one index per table.

CREATE INDEX MyTableFirstSecond ON MyTable(First,Second);

So in your case:

1. Get your database onto the Mac.
2. Use the shell to diagnose which of your queries are slow.
3. Study the queries to see why they're slow: What needs to be indexed? And less likely: What about the query is awkward, preventing the query optimizer from picking a sensible plan?
4. Add the appropriate indexes or tune the query.
5. Re-test to make sure the change works.

The rule of thumb with SQLite is simple: If you're going to search on it, it should be indexed.