iOS实施理论 [英] iOS Implementation Theory
问题描述
我们（正在工作的IT部门）正在寻求构建一个iPad应用程序，该应用程序将使用数字ID，并在表格中提供简单的查找。本质上，它是在单个表上进行主键搜索，并在进行较小的处理后显示一个字段。
We (IT Department at work) are looking to build an iPad app, that will take numeric IDs and provide a simple lookup in a table. It is essentially a primary key search on a single table, and displaying a field after minor processing.
The Caveat ：
此表中有450万行，并且查找时间最多为1秒。它没有互联网连接，因此必须在设备上进行。我们有一些想法，但是最有意义的是：
There are 4.5 million rows in this table, and it needs a lookup time of maximum of 1 second. It will not have an internet connection, so it has to happen on the device. We have a few ideas but which makes most sense:
-
Sqlite：它可以承受这种滥用吗？
Sqlite: Will it stand up to such abuse? Can it handle that many rows, and will it do it well?
平面文件搜索：我们可以自己遍历文件，也可以按
Flat file search: we can loop over the file ourselves, or split them up by the first few digits to do some slightly more intelligent indexing.
卸载一些可以通过API进行处理的设备上的第三方数据库应用程序。
Off load to some 3rd party database app on the device which can handle it through an API.
我们以无穷的智慧完全错过了其他东西。
Something else entirely which in our infinite wisdom we have missed.
我必须借此机会感谢apple使其易于测试。没有Mac或Dev许可证，我们不愿意花2000英镑以上，除非我们知道我们能做得很好。
I must take the chance to thank apple for making this so easy to test ourselves. Without a Mac or Dev license, we don't want to commit over £2000 until we know we can get it done well.
推荐答案
SQLite非常快。具有450万条记录的测试表具有以下结构：
SQLite is amazingly fast. A test table with 4.5 million records has that structure:
CREATE TABLE testtable (numericid INTEGER PRIMARY KEY, testtext TEXT);
用数字（0，1，....）的递增值和字符串填充
It is filled with increasing values for numericid (0, 1, .... ) and a string for testtext.
在MacBook Pro（2009年）上原子地完成所有插入操作需要1小时42分钟。生成的SQLite文件的大小为94 MB。
Doing all the insert atomically took 1 hour 42 minutes on a MacBook pro (2009). The resulting SQLite file is 94 MB of size.
在iOS应用程序内部，使用viewDidLoad方法打开数据库。一个简单的按钮触发了这样的数据库查询：
Inside the iOS app, the database is opened in the viewDidLoad method. A simple button triggered database query like this:
- (void)btnPressed:(UIButton *)sender{
NSLog(@"btn pressed, start");
sqlite3_stmt *statement = nil;
NSString *querystring;
querystring= [NSString stringWithFormat:@"SELECT * FROM testtable WHERE numericid = 2571312;"];
const char *sql = [querystring UTF8String];
NSLog(@"sql is: %s", sql);
if (sqlite3_prepare_v2(dbConnection, sql, -1, &statement, NULL)!=SQLITE_OK){
NSLog(@"sql problem occured with: %s", sql);
NSLog(@"%s", sqlite3_errmsg(dbConnection));
}
else
{
while (sqlite3_step(statement) == SQLITE_ROW) {
NSString *numericid = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 1)];
NSString *testtext = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 0)];
NSLog(@"%@",[NSString stringWithFormat:@"%@ (%@)", numericid, testtext]);
} // while
}
sqlite3_finalize(statement);
NSLog(@"btn pressed, finished");
}
输出为：
2012-08-10 17:51:36.734 DBQueryTest[28462:707] Database Successfully Opened
2012-08-10 17:51:39.083 DBQueryTest[28462:707] btn pressed, start
2012-08-10 17:51:39.087 DBQueryTest[28462:707] sql is: SELECT * FROM testtable WHERE numericid = 2571312;
2012-08-10 17:51:39.099 DBQueryTest[28462:707] text2571312 (2571312)
2012-08-10 17:51:39.102 DBQueryTest[28462:707] btn pressed, finished
因此，查询所需的时间少于 19ms ！尽管我没有对统计评估进行完全随机的测试，但可以复制几个数值的值。
So a query takes under 19ms! This could be reproduced for several values of numericid, though I didn't run a fully randomized test for statistical evaluation.
结论：此测试设置可以满足您的要求。
Conclusion: This test setup fulfills your requirements. SQLite is definitely a way to go.
更新：
快速随机具有100000个键值的访问测试将验证第一个结果。将SQL语句字符串创建和耗时的NSLog输出排除在时间之外，平均数据库查询时间下降了一个数量级，即：
A quick random access test with 100000 key values verifies the first result. Leaving sql statement string creation and time-consuming NSLog outputs out of time measurement, the average database query time drops by an order of magnitude to:
平均查询时间：1.8毫秒
平均值偏差：0.4毫秒
最长查询时间：25.9毫秒
最短查询时间：0.6毫秒
这篇关于iOS实施理论的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
