Best pracice on CB Lite index?

I’m experiencing fine response times on iOS in my Xamarin Forms app, however, on Android everything is “sluggish” - especially if the replication is running. I have a feeling that most of the issues with performance on Android could be side-effects of a slower database access. At the moment I only have one index:

Db.CreateIndex("type", IndexBuilder.ValueIndex(ValueIndexItem.Expression(Expression.Property("type"))));

I have many queries working on the entire dataset (~100K docs of 25-30 different types) - with quite a lot of different combinations of criteria.

So how do I find out what indexes (if any) I should create apart from the one I already have? I have searched this forum and your blogs as well - and much of the information is related to older versions of the SDK so I’m uncertain as to whether this information is still “best” practice.

Any input much appreciated :slight_smile:

/John

I’m using the latest Couchbase Lite version (2.7.1) - and Couchbase Lite Mapping (1.1.0)

I suppose the answer to that would be to profile your code and see which queries are getting used the most, and what keys they are accessing. Note that the flash storage in iOS is usually far superior (i.e. way faster) to the flash storage found in Android and so the culprit for sluggishness might be the device itself.

Yeah, I appreciate that the culprit may be the device itself :slight_smile:

I guess the thing is that I didn’t have to bother about it on iOS… - but apparently have to on Android…

So the question really is how to best go around finding out what indexes to create. If I need to cover all queries then I need to create quite a few indexes…

The other thing is that it seems that just the replication (or queries in the background) are running then it seems to influence the UI experience. Are there some precautions I need to take in that respect?

There is not a catch all answer in this situation, so it would probably be best to start with the most used queries and keep going until you notice performance improvement (assuming that your performance problems are query related). To keep it from affecting the UI you should definitely be making sure you are doing this work off of the UI thread. @blake.meike can tell you more about that, it’s been ages since I did any decent work with Android (if you can call any of my work decent :smile: )

Thanks Jim
Well, I’m using C# (Xamarin Forms) and I have really put an effort in running things in the background. But the experience is not as clear. I originally started with database code from samples from you… But obviously that may just be more like samples than best practice…

So I guess the question is if I should run the replication specifically in the background? From various articles here, on Stackoverflow and your blogs, I’ve got the impression that you handle all replication in the background…? But I may have misunderstood that - and need to take special steps to ensure that?

Yeah… I gotta say that indexes would not be the very first place I’d look, if my app seemed sluggish.

I would say that best practice, given our current implementation, would be that you make most calls to the the Couchbase library from a single thread that is not the UI/Main thread. This is pretty easy to do with either Kotlin co-routines, or RxJava. If that isn’t your current architecture, refactoring might be a good place to start.

Also, several of the methods that subscribe listeners (replication listeners, live queries, things like that) take an executor as an optional argument. It would also be a good idea to make sure that you are using those calls wisely.

I forgot that this was C# once again, so it’s less of an issue but you need to make sure that you are scheduling callbacks on the main thread. Replication already runs in the background, except for the callbacks which run where you tell them to (or if you don’t tell them to do anything they will use the thread pool)

I’m using dependency injection and create the database class as a singleton. And I start the replicator in a background thread when initializing the database class…

@blake.meike “It would also be a good idea to make sure that you are using those calls wisely.” - I appreciate that - but what exactly should I look out for here (C#)? I have both - e.g. to be able to publish an event if my data has been updated (thus requiring a re-read of data on some forms). I’m trying not to do too much work in those listeners…

Just to be sure that I understand the way I should create the index. Say if I have a query like this:

/*
    SELECT max(length) longest,specieskey
    FROM data
    WHERE type = "Catch" and userkey='2124DEFEC111BA8FC1257ED20034B387' and length is valued
    GROUP BY specieskey
    ORDER BY longest DESC
*/
var _condition = Expression.Property(TYPE).EqualTo(Expression.String(typeof(Catch).Name)).And(_userCondition)
                    .And(Expression.Property(nameof(Catch.Length).ToLower()).NotNullOrMissing());
if (forAll)
{
    _condition = _condition.And(Expression.Property(nameof(Catch.Visibility).ToLower()).NotEqualTo(Expression.String(Catch.PRIVATE)));
}
using (var query = QueryBuilder.Select(
            SelectResult.Property(nameof(Catch.SpeciesKey).ToLower()),
            SelectResult.Expression(Function.Max(Expression.Property(nameof(Catch.Length).ToLower()))).As(nameof(BestCatch.Longest).ToLower())
        )
        .From(dbSource)
        .Where(_condition)
        .GroupBy(Expression.Property(nameof(Catch.SpeciesKey).ToLower()))
        .OrderBy(Ordering.Property(nameof(BestCatch.Longest).ToLower()).Descending()))
{

Should I then create an index on the fields in the Where clause? And in this case include the “extra” condition (visibility) that may be used?

Should the order of the fields be the same?

And if in this case I don’t have a condition for “visibility” should I then add the condition that the field should be e.g. in this case “not null or missing” - or doesn’t that make any difference?

Can/should I also create separate indexes for e.g. type="Catch" where “length” and “visibility” are fields - and for type="FishingTrip" where these fields do not exist? This is the way I do it on the server (N1QL)

Thanks in advance!

/John

Hey John…
If this is C#, I may not be of much help. You may be quite correct that indices are what you need.

Injecting a singleton DB is very definitely the way to go. Good call. As you no doubt realize, though, the way you create the db has very little to do with the threads on which it is being called.

I use Kotlin. I always create a single threaded executor and guarantee that all my db routines run on it.

Similarly, the thread on which you create the replicator has very little to do with the thread on which it runs. In fact, there really is no way for you to get a handle to the replicator thread: it is created in the native code. (The exception to that is that replicator filters are run on the replicators thread. If you are using replicator filters, you should be very careful that they do not throw exceptions or take a long time to run.)

The issue is that, by default, all of the replicator/query callbacks run on the main thread. If those callbacks do any significant work, they will make your UI sluggish. If your application code is thread safe and prepared to handle out-of-order requests, you can handle these callbacks, optimally, concurrently.

I hope that is of some help. I now return you to the regularly scheduled programming.

:smile:

Thanks for the input,. I have found out that I can do a couple of things to control that all database access is run on a background thread. Basically, by running all the service calls with .ConfigureAwait(false) I can ensure that the calls run on a background thread instead of the UI thread (if anyone else should happen to come across this thread).