表中有很多记录的查询非常慢

发布于 2025-01-08 09:37:42 字数 1099 浏览 0 评论 0原文

我在 iPhone 上使用 sqlite。我有一个包含 200 000 条记录的表。它的大小很小，只有 2 MB。我正在使用两个简单的条件进行标准选择（3 列）。我只对整数值进行操作（返回和条件）。所有数据都是唯一的。我通常只从数据库中获取 10-50 条记录。

尽管如此，我的查询还是花了 3 秒。这是很多时间。我该如何改进这个？

编辑：我无法用其他线程加载我的表。

NSString * query = [ NSString stringWithFormat:@"SELECT rRozklad.hour, rRozklad.minute, rRozklad.marks FROM  rRozklad.idOkres=%i AND rRozklad.idPolaczeniaLinie=2 ORDER by rRozklad.hour, rRozklad.minute", okres];

NSLog(@"%@", query);

const char * querystring = [ query UTF8String ];
sqlite3_stmt * statment;

if(sqlite3_prepare_v2(database, querystring, -1, &statment, nil) == SQLITE_OK){
    while(sqlite3_step(statment) == SQLITE_ROW){
        NSMutableArray * temp = [[NSMutableArray alloc] initWithCapacity:2];
        [temp addObject:[NSNumber numberWithInt: sqlite3_column_int(statment, 0)]];
        [temp addObject:[NSNumber numberWithInt: sqlite3_column_int(statment, 1)] ];
        [temp addObject:[ NSString stringWithUTF8String: (const char *)sqlite3_column_text(statment, 2)]];

        [wyniki addObject:temp];
        [temp release];

    }
}

原文

I' m using sqlite on iphone.
I have table with 200 000 records. It' s size is small, only 2 MB. I' m doing standard select(3 collumns) with two simple condtions. I' m operating only on integer values( in return and condtion). All data are unique. I'm get usually only 10-50 records from database.

In spite of that my query take 3 sec. It' s a lot of time.
How i can improve this ?

Edit: I can't load my table with other thread.

NSString * query = [ NSString stringWithFormat:@"SELECT rRozklad.hour, rRozklad.minute, rRozklad.marks FROM  rRozklad.idOkres=%i AND rRozklad.idPolaczeniaLinie=2 ORDER by rRozklad.hour, rRozklad.minute", okres];

NSLog(@"%@", query);

const char * querystring = [ query UTF8String ];
sqlite3_stmt * statment;

if(sqlite3_prepare_v2(database, querystring, -1, &statment, nil) == SQLITE_OK){
    while(sqlite3_step(statment) == SQLITE_ROW){
        NSMutableArray * temp = [[NSMutableArray alloc] initWithCapacity:2];
        [temp addObject:[NSNumber numberWithInt: sqlite3_column_int(statment, 0)]];
        [temp addObject:[NSNumber numberWithInt: sqlite3_column_int(statment, 1)] ];
        [temp addObject:[ NSString stringWithUTF8String: (const char *)sqlite3_column_text(statment, 2)]];

        [wyniki addObject:temp];
        [temp release];

    }
}

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

南薇 2025-01-15 09:37:42

您的 SQL 似乎缺少表名和 WHERE 关键字。

SELECT rRozklad.hour, rRozklad.minute, rRozklad.marks
FROM  rRozklad.idOkres=%i AND rRozklad.idPolaczeniaLinie=2
ORDER by rRozklad.hour, rRozklad.minute

您的列有索引吗？特别是 idOkres、idPolaczeniaLinie、小时和分钟？

It looks like your SQL is missing the table name and the WHERE keyword.

SELECT rRozklad.hour, rRozklad.minute, rRozklad.marks
FROM  rRozklad.idOkres=%i AND rRozklad.idPolaczeniaLinie=2
ORDER by rRozklad.hour, rRozklad.minute

Do you have your columns indexed? Especially idOkres, idPolaczeniaLinie, hour, and minute?

回复收藏 0 原文

~没有更多了~