Optimising view queries

[Chris H]

New to Couchbase Lite iOS and have 350,000 documents in a bucket. These are split into 4 main categories:

 - Locations (60k)

 - Dogs (185k)

 - Cats (50k)

 - Owners (35k)

I'm having real problems with view speed, firstly trying to grab close locations will take 30 seconds for the first query, and subsequent ones are fast. However, we can't have the user waiting 30 seconds as the app loads! The code for this is below:

double lat = 51.504656;

double lon = -0.128299;

CBLView * view = [self.database viewNamed:@"close"];

[view setMapBlock: MAPBLOCK({

if ([doc[@"type"] isEqualToString:@"location"]) {

emit(CBLGeoPointKey([doc[@"lat"] doubleValue], [doc[@"lon"] doubleValue]), doc[@"name"]);

}

}) version: @"0.0.1"];

CBLQuery * query  = [[self.database viewNamed:@"close"] createQuery];

query.boundingBox = (CBLGeoRect){{lat - .005, lon - .005}, {lat + .005, lon + .005}};;

query.limit       = 10;

CBLQueryEnumerator * rowEnum = [query run:&error];

for (CBLGeoQueryRow * row in rowEnum) {

NSLog(@"%@", row.document[@"name"]);

}

I'm not sure how I can make this any faster. I've tried changing the indexUpdateMode to "after", but that didn't make any difference.

Also, a quick sub-question: I assume most of the time is from indexing the database (since subsequent queries are fast), is there any way to automatically index the database as soon as it is synced?

[Jens Alfke]

On Aug 27, 2014, at 2:17 AM, Chris H <cjh...@gmail.com> wrote:

New to Couchbase Lite iOS and have 350,000 documents in a bucket.

…

I'm having real problems with view speed, firstly trying to grab close locations will take 30 seconds for the first query, and subsequent ones are fast.

So it's indexing about 2k docs/sec. (What hardware is this on?)

if ([doc[@"type"] isEqualToString:@"location"]) {

emit(CBLGeoPointKey([doc[@"lat"] doubleValue], [doc[@"lon"] doubleValue]), doc[@"name"]);

The map function looks reasonable. I've never benchmarked views that use SQLite's Rtree (geo) indexing, so I don't know how much of the time is taken up by that. You could try profiling the indexing using Instruments' time profiler. It'd be interesting to know how much of the time is spent inside SQLite.

I assume most of the time is from indexing the database (since subsequent queries are fast),

Correct.

is there any way to automatically index the database as soon as it is synced?

I've considered doing that, but I don't think it would save time. Instead, the pull replication would just take 30 seconds longer … quite possibly more than that since the batch indexing has economies of scale that would be lost if indexing individual docs.

You could get the same effect by waiting for the initial pull to complete, then triggering an async query with limit=1 to update the view index in the background, then waiting for that to complete before telling the user that the data is available.

—Jens