这是我应该提交给苹果的错误，还是这个预期的行为？ [英] Is this a bug I should submit to Apple, or is this expected behavior?
问题描述
使用CoreData时，以下多列索引谓词非常慢 - 对于26,000条记录，它需要几乎2秒。
When using CoreData, the following multi-column index predicate is very slow - it takes almost 2 seconds for 26,000 records.
请注意，两个列都是索引的，我有意使用>和< =，而不是开始，进行查询以使其快速：
Please note both columns are indexed, and I am purposefully doing the query with > and <=, instead of beginswith, to make it fast:
NSPredicate *predicate = [NSPredicate predicateWithFormat:
@"airportNameUppercase >= %@ AND airportNameUppercase < %@ \
OR cityUppercase >= %@ AND cityUppercase < %@ \
upperText, upperTextIncremented,
upperText, upperTextIncremented];
然而，如果我运行两个单独的fetchRequest，每个列一个，然后我合并结果，然后每个fetchRequest只需要1-2百分之一秒，并合并列表（这是排序）花费大约1/10秒。
However, if I run two separate fetchRequests, one for each column, and then I merge the results, then each fetchRequest takes just 1-2 hundredths of a second, and merging the lists (which are sorted) takes about 1/10th of a second.
这是一个错误，CoreData如何处理多个索引，或这是预期的行为？以下是我的完整，优化的代码，其工作速度非常快：
Is this a bug in how CoreData handles multiple indices, or is this expected behavior? The following is my full, optimized code, which works very fast:
NSFetchRequest *fetchRequest = [[[NSFetchRequest alloc] init]autorelease];
[fetchRequest setFetchBatchSize:15];
// looking up a list of Airports
NSEntityDescription *entity = [NSEntityDescription entityForName:@"Airport"
inManagedObjectContext:context];
[fetchRequest setEntity:entity];
// sort by uppercase name
NSSortDescriptor *nameSortDescriptor = [[[NSSortDescriptor alloc]
initWithKey:@"airportNameUppercase"
ascending:YES
selector:@selector(compare:)] autorelease];
NSArray *sortDescriptors = [[[NSArray alloc] initWithObjects:nameSortDescriptor, nil]autorelease];
[fetchRequest setSortDescriptors:sortDescriptors];
// use > and <= to do a prefix search that ignores locale and unicode,
// because it's very fast
NSString *upperText = [text uppercaseString];
unichar c = [upperText characterAtIndex:[text length]-1];
c++;
NSString *modName = [[upperText substringToIndex:[text length]-1]
stringByAppendingString:[NSString stringWithCharacters:&c length:1]];
// for the first fetch, we look up names and codes
// we'll merge these results with the next fetch for city name
// because looking up by name and city at the same time is slow
NSPredicate *predicate = [NSPredicate predicateWithFormat:
@"airportNameUppercase >= %@ AND airportNameUppercase < %@ \
OR iata == %@ \
OR icao == %@",
upperText, modName,
upperText,
upperText,
upperText];
[fetchRequest setPredicate:predicate];
NSArray *nameArray = [context executeFetchRequest:fetchRequest error:nil];
// now that we looked up all airports with names beginning with the prefix
// look up airports with cities beginning with the prefix, so we can merge the lists
predicate = [NSPredicate predicateWithFormat:
@"cityUppercase >= %@ AND cityUppercase < %@",
upperText, modName];
[fetchRequest setPredicate:predicate];
NSArray *cityArray = [context executeFetchRequest:fetchRequest error:nil];
// now we merge the arrays
NSMutableArray *combinedArray = [NSMutableArray arrayWithCapacity:[cityArray count]+[nameArray count]];
int cityIndex = 0;
int nameIndex = 0;
while( cityIndex < [cityArray count]
|| nameIndex < [nameArray count]) {
if (cityIndex >= [cityArray count]) {
[combinedArray addObject:[nameArray objectAtIndex:nameIndex]];
nameIndex++;
} else if (nameIndex >= [nameArray count]) {
[combinedArray addObject:[cityArray objectAtIndex:cityIndex]];
cityIndex++;
} else if ([[[cityArray objectAtIndex:cityIndex]airportNameUppercase] isEqualToString:
[[nameArray objectAtIndex:nameIndex]airportNameUppercase]]) {
[combinedArray addObject:[cityArray objectAtIndex:cityIndex]];
cityIndex++;
nameIndex++;
} else if ([[cityArray objectAtIndex:cityIndex]airportNameUppercase] <
[[nameArray objectAtIndex:nameIndex]airportNameUppercase]) {
[combinedArray addObject:[cityArray objectAtIndex:cityIndex]];
cityIndex++;
} else if ([[cityArray objectAtIndex:cityIndex]airportNameUppercase] >
[[nameArray objectAtIndex:nameIndex]airportNameUppercase]) {
[combinedArray addObject:[nameArray objectAtIndex:nameIndex]];
nameIndex++;
}
}
self.airportList = combinedArray;
推荐答案
CoreData不支持创建或使用multi列索引。这意味着当您执行与多属性谓词相对应的查询时，CoreData只能使用一个索引进行选择。随后，它使用索引进行其中一个属性测试，但随后SQLite不能使用索引来收集第二个属性的匹配，因此必须在内存中这么做，而不是使用其磁盘索引结构。
CoreData has no affordance for the creation or use of multi-column indices. This means that when you execute the query corresponding to your multi-property predicate, CoreData can only use one index to make the selection. Subsequently it uses the index for one of the property tests, but then SQLite can't use an index to gather matches for the second property, and therefore has to do it all in memory instead of using its on-disk index structure.
选择的第二阶段结果是缓慢的，因为它必须从磁盘收集所有结果到内存中，然后进行比较并将结果保存在内存中。因此，你最终可能会比使用多列索引更多的I / O。
That second phase of the select ends up being slow because it has to gather all the results into memory from the disk, then make the comparison and drop results in-memory. So you end up doing potentially more I/O than if you could use a multi-column index.
这就是为什么，如果你将在谓词的每一列中取消很多潜在结果的资格，你会通过做你正在做的事而看到更快的结果并进行两次单独的提取和内存中合并，比你做一个提取。
This is why, if you will be disqualifying a lot of potential results in each column of your predicate, you'll see much faster results by doing what you're doing and making two separate fetches and merging in-memory than you would if you made one fetch.
为了回答你的问题，苹果公司并不期望这种行为;它只是一个设计决定不支持CoreData中的多列索引的效果。但您应该在 http://radar.apple.com 提出请求支持的错误
To answer your question, this behavior isn't unexpected by Apple; it's just an effect of a design decision to not support multi-column indices in CoreData. But you should to file a bug at http://radar.apple.com requesting support of multi-column indices if you'd like to see that feature in the future.
同时，如果你真的想在iOS上获得最大的数据库性能，你可以考虑直接使用SQLite而不是CoreData。
In the meantime, if you really want to get max database performance on iOS, you could consider using SQLite directly instead of CoreData.
这篇关于这是我应该提交给苹果的错误，还是这个预期的行为？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
