在iphone上的tableview上的sqlite db上文本搜索速度太慢 [英] text search too slow on sqlite db on tableview on iphone

问题描述

我有一个大约2500个条目的大表。我在tableview上显示它。但是在进行动态搜索时搜索栏太慢了。即每次用户在搜索栏上放置一个字符时，我都会过滤表格。

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.

以下是代码：

- (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];
}

在sqlite查询中有whereClause，因此它会不断追加搜索字符。当用户输入键盘键时，键盘变得非常粘，输入速度很慢。任何建议将不胜感激...

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...

推荐答案

SQLite的选择通常非常快。如果您发布了一些示例查询，则可能更容易为您提供帮助。

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

然而，这说：如果您有一个查询，其中一个简单的WHERE子句正在缓慢执行，您可能没有正在搜索的列的索引。添加一个。

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.

对于SQLite，表中的2500个条目 NOT 大。 25,000,000会很大。

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

当你使用SQLite时，你可以使用sqlite3 shell在桌面上测试很多东西。例如，如果您的查询类似于：

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';

你可以这样做：

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

如果你看到这样的输出：

If you see output like this:

0|0|TABLE MyTable

这意味着SQLite正在爬行整个表格得到结果。

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

如果您的WHERE子句更复杂，您可能需要编写复合索引，因为SQLite每个表只使用一个索引。

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);

所以在你的情况下：

1. 将您的数据库存入Mac。


2. 使用shell来诊断哪些查询速度很慢。


3. 研究查询看看为什么它们很慢：需要索引什么？并且不太可能：查询有什么尴尬，阻止查询优化器选择合理的计划？


4. 添加适当的索引或调整查询。


5. 重新测试以确保更改有效。

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.

使用SQLite的经验法则很简单：如果你要去搜索它，它应该被编入索引。

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

这篇关于在iphone上的tableview上的sqlite db上文本搜索速度太慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！