Today, I found a use case for testing out the $filter operation on the SODataOfflineStore, and was really pleasantly surprised by the results. My problem was that the back end has some garbage data, so I wanted to filter out all Contacts which do not have a value for "function" or "company".
Unfortunately, "company" and "function" are not filterable on the back end. So, if I want to clean up the data, I'm going to have to do it client-side.
This is a bit of a lab scenario, since in production, I'd expect my data to be clean. But, it will show how I can use OData $filter method on entries in a client side database with the SODataOfflineStore APIs, and I can share a simple benchmarking procedure to test the performance in your code.
The SODataOnlineStore approach
The dirtiest option here is to query everything, then filter the result set on the device with an enumeration, on every request. If I'm using the "SODataOnlineStore", this is basically what I'll be stuck with. My -fetchContacts: code would look something like this:
-(void)fetchContactsWithCompletion:(void(^)(NSArray *entities))completion {
NSString *resourcePath = @"ContactCollection";
[self scheduleRequestForResource:resourcePath withMode:SODataRequestModeRead withEntity:nil withCompletion:^(NSArray *entities, id<SODataRequestExecution> requestExecution, NSError *error) {
if (entities) {
NSMutableArray *completeEntities = [[NSMutableArray alloc] init];
[entities enumerateObjectsUsingBlock:^(id<SODataEntity> obj, NSUInteger idx, BOOL *stop) {
NSDictionary *properties = (id<SODataEntity>)obj.properties;
NSString *company = (NSString *)[(id<SODataProperty>)properties[@"company"] value];
NSString *function = (NSString *)[(id<SODataProperty>)properties[@"function"] value];
if (company.length > 0 && function.length > 0) {
[completeEntities addObject:obj];
}
}];
completion(completeEntities);
} else {
NSLog(@"did not get any entities, with error: %@", error);
}
}];
}
Here, I query for the entire ContactCollection, get back an NSArray of entities, enumerate the entities to filter out those with values for "company" and "function", then call the completion block to pass the entities that pass the test.
This approach is really inefficient for every single request. To improve the speed of loading UI views in the application, I would probably create a singleton Model class, with a property value to store only these entities which pass the test, so that I don't end up running the enumeration block every time I refresh a UI.
But, there's a better way, if I'm using the SODataOfflineStore:
The SODataOfflineStore solution
The concept behind the SODataOfflineStore is that when you initialize your application for the first time, you supply a list of "defining requests", which are analyzed to construct the local Ultralite database schema, and then executed on the back end, to populate the database. (See some additional details about the behavior of these defining requests here.) The SODataOfflineStore APIs read and write to the local database; CUD entries executed locally are "flushed" to the server, and changes on the server are "refreshed" to the database.
One major benefit of using "defining requests" to populate the database is that the bulk download and insertion into the local database is optimized (using Mobilink protocol), so it is very fast to download very large sizes of records; once the records are on the local database, it is much faster to read/write locally than communicating over the network.
So, let's return to this problem of filtering out the garbage data: I can't use OData to $filter on the attributes directly, so I'm going to end up downloading all the records to the device anyway. In that case, let's specify "ContactCollection" as a defining request, so that I can use the optimized Mobilink protocol to download the Contact records in bulk, and then I have them in the local database, where it's much faster to read.
I configure my SODataOfflineStore with a set of SODataOfflineStoreOptions, which is where I set my defining requests:
- (SODataOfflineStoreOptions *)options
{
SODataOfflineStoreOptions *options = [[SODataOfflineStoreOptions alloc] init];
options.enableHttps = self.data.isHttps;
options.host = self.data.serverHost;
options.port = self.data.serverPort;
options.serviceRoot = [NSString stringWithFormat:@"/%@", self.data.applicationId];
options.definingRequests[@"req1"] = @"ContactCollection";
options.enableRepeatableRequests = NO;
options.conversationManager = self.httpConvManager;
return options;
}
Defining requests are stored in a dictionary, where keys should be named as: [NSString stringWithFormat:@"req%i", index + 1];
i.e.: [req1, req2, req3, ...]
Once my SODataOfflineStore is opened, I'll have access to the complete Contact collection. Now, I can write my -fetchContacts: method to use standard v2 $filter semantics, saving me the cost of enumerating the generated id<SODataEntity> entities. gist link.
-(void)fetchContactsWithCompletion:(void(^)(NSArray *entities))completion {
NSString *resourcePath = @"ContactCollection?$filter=length(company) gt 0 and length(function) gt 0";
[self scheduleRequestForResource:resourcePath withMode:SODataRequestModeRead withEntity:nil withCompletion:^(NSArray *entities, id<SODataRequestExecution> requestExecution, NSError *error) {
if (entities) {
completion(entities);
} else {
NSLog(@"did not get any entities, with error: %@", error);
}
}];
}
Benchmarking
What good is this comparison without some real numbers?? The SAP Mobile SDK actually has a new feature we snuck into SP05 named "Usage". The majority of the Usage features are currently dependent on the upcoming HANA Cloud Platform Mobile Services release, so I won't talk about it in detail, but there's a very useful little helper class named "Timer", that we can use to benchmark the performance of filtering though enumeration (option 1) versus $filter in the database (option 2).
The Timer object is generated with a factory method on Usage, where I pass a name. It has a simple method "stopTimer" that I can invoke directly; then, I can read out the duration in milliseconds.
-(void)fetchContactsWithCompletion:(void(^)(NSArray *entities))completion {
NSString *resourcePath = @"ContactCollection?$filter=length(company) gt 0 and length(function) gt 0";
Timer *t = [Usage makeTimer:@"ContactFilter"];
[self scheduleRequestForResource:resourcePath withMode:SODataRequestModeRead withEntity:nil withCompletion:^(NSArray *entities, id<SODataRequestExecution> requestExecution, NSError *error) {
if (entities) {
[t stopTimer];
NSLog(@"t = %@", [t duration]);
completion(entities);
} else {
NSLog(@"did not get any entities, with error: %@", error);
}
}];
}
2014-11-05 14:40:45.163 SAPCRMOData[11255:3299863] t = 5.281984806060791
In normal practice, the Timer object is stopped by invoking [Usage stopTimer:t], which results in the record being written to the Usage database. Calling stopTimer directly on the Timer object will not result in the record being saved.
My database has 200 Contact records, of which 26 entities meet the criteria of length(company) > 0 and length(function) > 0.
Results
- Executing the filter as an enumeration on results from local database averaged between 18.5 and 19 milliseconds.
- Executing the $filter in the database averaged between 5.2 and 5.6 milliseconds.
Executing the filter as an enumeration on results from the network averaged ~2.3 seconds round-trip :smile:
