我在iOS应用程序中使用SQLite,并且使用FMDB作为包装器。这是我的数据库架构:
CREATE TABLE Offer (code TEXT PRIMARY KEY NOT NULL, name TEXT);
CREATE TABLE OffreMarket (codeOffer TEXT NOT NULL,
codeMarket TEXT NOT NULL,
FOREIGN KEY(codeOffer) REFERENCES Offer(code),
FOREIGN KEY(codeMarket) REFERENCES Market(code));
CREATE TABLE Market (code TEXT PRIMARY KEY NOT NULL, name TEXT);
我的模型对象:
@interface Offer : NSObject
@property (nonatomic,copy) NSString *code;
@property (nonatomic,copy) NSString *name;
@property (nonatomic,copy) NSArray *markets;
@end
@interface OffreMarket : NSObject
@property (nonatomic,copy) NSString *codeOffer;
@property (nonatomic,copy) NSString *codeMarket;
@end
@interface Market : NSObject
@property (nonatomic,copy) NSString *code;
@property (nonatomic,copy) NSString *name;
@end
例如,我正在获取数据库中的所有报价,如下所示:
- (NSArray *)offers {
// Open database
NSMutableArray *offers = [NSMutableArray new];
FMResultSet *resultSet = [database executeQuery:@"SELECT * FROM Offer"];
while ([resultSet next]){
Offer *offer = [Offer new];
offer.code = [resultSet stringForKey:@"code"];
offer.name = [resultSet stringForKey:@"name"];
// Get the markets for each offer
FMResultSet *marketResultSet = [database executeQuery:@"SELECT * FROM OffreMarket WHERE codeOffer = ?",offer.code];
NSMutableArray *offers = [NSMutableArray new];
while ([marketResultSet next]) {
OffreMarket *offerMarket = [OffreMarket new];
....
[offers addObject:offerMarket];
}
market.offers = [offers copy];
}
return [offers copy]
}
这是有效的,但它需要时间,因为我正在使用许多SQL请求来获取所有报价和相应的市场。
我能否避免多次SQL
请求获取相应市场的所有报价?感谢您的回答
我能建议的是:
-
优化您的 SQL 语句。您可以简单地将语句更改为"
SELECT * FROM OffreMarket WHERE codeOffer IN (SELECT code FROM Offer)
",而不是 2 个循环。如果要在表"Offer
"中使用列"name
",可以连接两个表"codeOffer
"和"Offer
"。这里的经验法则是避免太多循环,但尝试组合或优化您的 SQL 语句。 -
为表"
Offer
"中的列"code
"创建索引。它将大大加快您的搜索速度。例如,有一次在我的一个项目中,我必须处理一个包含 36K 条记录的 SQL 表。通过将简单索引设置为主键列,我设法将该表的搜索时间减少了 10 秒。
在这种情况下,您只需一个查询即可获得结果:
select * from Offer
left outer join OffreMarket OM on (OM.codeOffer = Offer.code)
如果您使用 FMDB 作为包装器,那么这里是您的答案:
@try {
// Select Contact Details From Modules
NSString *selectSQL = [NSString stringWithFormat:
@"SELECT * FROM %@ INNER JOIN %@ ON %@.%@=%@.%@ ;",
OffreMarket,
Offer,
OffreMarket
code
Offer
code];
//NSLog*(@"Get All Offers select SQL: %@", selectSQL);
FMResultSet *resultSet = [db executeQuery:selectSQL];
NSMutableArray *marketOffers = [[NSMutableArray alloc]init];
while ([resultSet next]) {
// Create Offers Details Modal
Offer *offer = [[Offer alloc] init];
offer.code = [resultSet stringForKey:@"code"];
offer.name = [resultSet stringForKey:@"name"];
[marketOffers addObject: offer];
}
return (NSArray *)infos;
}
@catch (NSException *exception) {
//NSLog*(@"%@ : %@",exception.name,exception.reason);
return nil;
}
return nil;
尝试上面的代码。它将在最短的时间内为您提供所有数据。FMDB是数据库操作的不错选择。