Retrieve the ith document (in fast) [IOS]

Hi,

i’d like to query a view - or more precisely the ith document of the view. What is a good approach to do this in “fast”?

Simple (obvious) solution would be to insert the number i as key in the view. Can this be done? So like …

CBLView* view = [self.database viewNamed: @“myNewView”];
[view setMapBlock: MAPBLOCK({
emit(doc[ i , doc[@"…"]);
}) version: @“1”];

Whereby “i” should refer to the ith document.

Greetings,

Ralf

Just set query.offset=i and query.limit=1.

Hm, but CBLQuery.h does not provide a property “offset” ? Well, at least not my version (couchbase-lite-ios-community_1.0.3.1).

//
// CBLQuery.h
// CouchbaseLite
//
// Created by Jens Alfke on 6/18/12.
// Copyright © 2012-2013 Couchbase, Inc. All rights reserved.
//

@interface CBLQuery : NSObject

/** The database that contains this view. /
@property (readonly) CBLDatabase
database;

/** The maximum number of rows to return. Defaults to ‘unlimited’ (UINT_MAX). */
@property NSUInteger limit;

/** The number of initial rows to skip. Default value is 0.
Should only be used with small values. For efficient paging, use startKey and limit.*/
@property NSUInteger skip;

/** Should the rows be returned in descending key order? Default value is NO. */
@property BOOL descending;

/** If non-nil, the key value to start at. */
@property (copy) id startKey;

/** If non-nil, the key value to end after. */
@property (copy) id endKey;

/** If non-nil, the document ID to start at.
(Useful if the view contains multiple identical keys, making .startKey ambiguous.) /
@property (copy) NSString
startKeyDocID;

/** If non-nil, the document ID to end at.
(Useful if the view contains multiple identical keys, making .endKey ambiguous.) /
@property (copy) NSString
endKeyDocID;

/** If YES (the default) the startKey (or startKeyDocID) comparison uses “>=”. Else it uses “>”. */
@property BOOL inclusiveStart;

/** If YES (the default) the endKey (or endKeyDocID) comparison uses “<=”. Else it uses “<”. */
@property BOOL inclusiveEnd;

/** If nonzero, enables prefix matching of string or array keys.
* A value of 1 treats the endKey itself as a prefix: if it’s a string, keys in the index that
come after the endKey, but begin with the same prefix, will be matched. (For example, if the
endKey is “foo” then the key “foolish” in the index will be matched, but not “fong”.) Or if
the endKey is an array, any array beginning with those elements will be matched. (For
example, if the endKey is [1], then [1, “x”] will match, but not [2].) If the key is any
other type, there is no effect.
* A value of 2 assumes the endKey is an array and treats its final item as a prefix, using the
rules above. (For example, an endKey of [1, “x”] will match [1, “xtc”] but not [1, “y”].)
* A value of 3 assumes the key is an array of arrays, etc.
Note that if the .descending property is also set, the search order is reversed and the above
discussion applies to the startKey, not the endKey. */
@property NSUInteger prefixMatchLevel;

/** An optional array of NSSortDescriptor objects; overrides the default by-key ordering.
Key-paths are interpreted relative to a CBLQueryRow object, so they should start with
"value" to refer to the value, or “key” to refer to the key.
A limited form of array indexing is supported, so you can refer to “key[1]” or “value[0]” if
the key or value are arrays. This only works with indexes from 0 to 3. /
@property (copy) NSArray
sortDescriptors;

/** An optional predicate that filters the resulting query rows.
If present, it’s called on every row returned from the query, and if it returns NO
the row is skipped.
Key-paths are interpreted relative to a CBLQueryRow, so they should start with
"value" to refer to the value, or “key” to refer to the key. /
@property (retain) NSPredicate
postFilter;

/** Determines whether or when the view index is updated. By default, the index will be updated
if necessary before the query runs – this guarantees up-to-date results but can cause a
delay. The “Never” mode skips updating the index, so it’s faster but can return out of date
results. The “After” mode is a compromise that may return out of date results but if so will
start asynchronously updating the index after the query so future results are accurate. */
@property CBLIndexUpdateMode indexUpdateMode;

/** If non-nil, the query will fetch only the rows with the given keys. /
@property (copy) NSArray
keys;

/** If set to YES, disables use of the reduce function.
(Equivalent to setting “?reduce=false” in the REST API.) */
@property BOOL mapOnly;

/** If non-zero, enables grouping of results, in views that have reduce functions. */
@property NSUInteger groupLevel;

/** If set to YES, the results will include the entire document contents of the associated rows.
These can be accessed via CBLQueryRow’s -documentProperties property.
This slows down the query, but can be a good optimization if you know you’ll need the entire
contents of each document. */
@property BOOL prefetch;

/** Changes the behavior of a query created by -queryAllDocuments.
* In mode kCBLAllDocs (the default), the query simply returns all non-deleted documents.
* In mode kCBLIncludeDeleted, it also returns deleted documents.
* In mode kCBLShowConflicts, the .conflictingRevisions property of each row will return the
conflicting revisions, if any, of that document.
* In mode kCBLOnlyConflicts, only documents in conflict will be returned.
(This mode is especially useful for use with a CBLLiveQuery, so you can be notified of
conflicts as they happen, i.e. when they’re pulled in by a replication.) */
@property CBLAllDocsMode allDocsMode;

/** Sends the query to the server and returns an enumerator over the result rows (Synchronous).
Note: In a CBLLiveQuery you should access the .rows property instead. */

  • (CBLQueryEnumerator*) run: (NSError**)outError;

/** Starts an asynchronous query. Returns immediately, then calls the onComplete block when the
query completes, passing it the row enumerator (or an error). */

  • (void) runAsync: (void (^)(CBLQueryEnumerator*, NSError*))onComplete attribute((nonnull));

/** Returns a live query with the same parameters. */

  • (CBLLiveQuery*) asLiveQuery;

@end

The explanation of “skip” sounds slow and CBLQueryEnumerator::indexAtRow is actually slow.

Here is what I do … I retrieve the keys of the current visible (table) elements. If the first entry changes the current visible key elements are retrieved/updated from the database. Thus there is one query of x elements (presented elements) if the rows change. The retrieval is done with a startkey and limit. And I like to speed up things a little more. Any suggestions?

Sorry, it’s query.skip.

The explanation of “skip” sounds slow

It has the same characteristics as OFFSET in SQL. Internally I think the database cursor has to step through that many rows first.

What exactly are you trying to do? If you want to implement paging of results, you should instead remember the last key of the previous page and then use startKey for the next page’s query.

Here is what I do …

Sorry, I can’t understand that from your description. Can you be more specific? What’s your actual goal?

This sounds like something you might be able to use grouping for; have you looked at that?

Ok, thanks … I described the scenario in the previous post (last update). The database(s) are kind of large. :wink:

Grouping? Not yet. You mean subsets (=groups) of the overall set can be searched faster (than the overall set).

Please read the docs.

Thanks for the group hint. :smile:

Nevertheless I still have problem(s) with queries.

If I pass a startkey the query works as expected and generates a list of limit size rows starting from startkey.

If I do the same with endkey (and startkey = nil) I would expect a list of limit size rows ending at endkey. Is this wrong? The last query returns a list from the start (first db entry) of limit size but not ending with the endkey.

I assume the implementation ignores the endkey when the startkey is not set?

No, startKey=nil means to start from the beginning of the index.

If you want the last n rows before a key, reverse the direction:

query.descending = YES;
query.startKey = maxKey;
query.limit = n;

Note that you set the startKey not the endKey because you’re starting from the maxKey and going backwards.

Ok, thanks - this works fine.

Interesting … when I query in ascending order each query requires 0.3 sec. Limiting the entries of a query has nearly no effect on the performance. But when I change the order to descending (the same) query takes 0.001 sec.

When I use a smaller number of entries the queries are much faster. Can I tweak some environment properties (e.g., memory consumption) to achieve the same performance with large tables?

No, there’s no such configuration. Have you tried profiling the slow query to see where the time is spent?

Yes, in the beginning.

Actually the XCode profiler is not responsive enough for this task (afaik). Then I introduced a simple time tracking around the DB query, like

NSDate *methodStart = [NSDate date];
CBLQueryEnumerator *q = [query run: &error];
NSTimeInterval executionTime = [[NSDate date] timeIntervalSinceDate:methodStart];
NSLog(@“Query Time = %f”, executionTime);

And this changes (drastically) from 0.3s to 0.001s (vice versa). When this is fast the GUI is also fast (so obviously the bottleneck).

I obviously can create different / more views to speed things (DBs with less entries are faster) up but question if it’s possible to change some parameters (add them) to tweak the performance of large DBs without such a “patch” / or what could be the reason for the performance breakdown.

The profiler (Instruments) is plenty fast for that.

The long times you’re seeing are probably when the view isn’t up to date and the index has to be updated by running the map function on every doc that’s changed. If that’s too slow, it may be because your map function is slow. (I don’t know what your map function looks like.) Profiling would tell you that.

Hi,

you’re right, the profiler showed one (the most important) bottleneck. This was in my code and now it’s fixed. Thanks!

Nevertheless the utilization is still high (and the database is not filled completely). The mapping function is rather “simple”.

Please find a profiling screen attached.

Is this enough to derive some suggestions? Or what (else) do you need?

Greetings,

Ralf

The profile here shows querying, not indexing, so the map function isn’t involved.

The vast majority of the profile time is being spent in -[CBL_FMResultSet step], which is simply a wrapper around sqlite3_step. (The symbols inside that call are completely wrong; I think your binary doesn’t have full symbol data and the profiler is making wrong guesses about symbol names.)

In other words, the performance bottleneck at this point is SQLite, which is primarily dominated by disk I/O.

I just noticed that this is all being called from -[SearchViewController tableView:cellForRowAtIndexPath:, which gets called for every visible row in the table. You’re not running a query for every single row, are you?

Ok, only if the visible cell range changes … right now, I do the following:

  • determine the visible tableview::cell range
  • query the db if something is missing (e.g., when a scrolling occurs and a new cell becomes visible) and storing the query in a(/the) local cache
  • a query takes into account the keys of the index of the db entries currently visible (to grab the delta fast)
  • tableview::cells are provided by the local cache

Would you propose a different approach?

Hm, it depends on how often you end up running the query. It’s going to be a trade-off between memory usage and query time. If I were doing it, I’d put in some logging that triggered when the view had to run a query, and see how often that happened in normal usage.

Every time the user scrolls the database is queried.

You mean I can increase the cache size as trade-off, or?