Joins and chaining Joins takes a long time to execute [Couchbase Lite 2.0.2]

Mobile Couchbase Lite
1

Hi there,

I have a mobile app that has continuous two-way replication enabled and all the data is being synced across to my Android devices. However, i am finding that any time that i do any sort of JOIN in Query interface it takes 10 or more seconds to fetch the data from the local database and we are talking about a recordset of about 20 items.

To give you some context - the Observation document has three UUIDs for which i am trying to join and load corresponding entities.

I am new to Query interface and here is the query i am trying to execute in Kotlin:

private val OBSERVATION_DOCUMENT_TYPE:String = "Observation"
private val METHOD_DOCUMENT_TYPE:String = "Method"
private val SCALE_DOCUMENT_TYPE:String = "Scale"
private val TRAIT_DOCUMENT_TYPE:String = "Trait"


db?.createIndex("type", IndexBuilder.valueIndex(ValueIndexItem.property("type")))

        val observationsDS = DataSource.database(db).`as`("observations")
        val traitDS = DataSource.database(db).`as`("traits")
        val methodDS = DataSource.database(db).`as`("methods")
        val scaleDS = DataSource.database(db).`as`("scales")

        val observationsTraitExpr = Expression.property("trait_UUID").from("observations")
        val traitIdExpr = Meta.id.from("traits");

        val observationsMethodExpr = Expression.property("method_UUID").from("observations")
        val methodIdExpr = Meta.id.from("methods");

        val observationsScaleExpr = Expression.property("scale_UUID").from("observations")
        val scaleIdExpr = Meta.id.from("scales");


        val traitJoinExpr = observationsTraitExpr.equalTo(traitIdExpr)
                .and(Expression.property("type").from("observations").equalTo(Expression.string(OBSERVATION_DOCUMENT_TYPE)))
                .and(Expression.property("type").from("traits").equalTo(Expression.string(TRAIT_DOCUMENT_TYPE)))

        val methodJoinExpr = observationsMethodExpr.equalTo(methodIdExpr)
                .and(Expression.property("type").from("observations").equalTo(Expression.string(OBSERVATION_DOCUMENT_TYPE)))
                .and(Expression.property("type").from("methods").equalTo(Expression.string(METHOD_DOCUMENT_TYPE)))

        val scaleJoinExpr = observationsScaleExpr.equalTo(scaleIdExpr)
                .and(Expression.property("type").from("observations").equalTo(Expression.string(OBSERVATION_DOCUMENT_TYPE)))
                .and(Expression.property("type").from("scales").equalTo(Expression.string(SCALE_DOCUMENT_TYPE)))

        val traitJoinQuery = Join.innerJoin(traitDS).on(traitJoinExpr)
        val methodJoinQuery = Join.innerJoin(methodDS).on(methodJoinExpr)
        val scaleJoinQuery = Join.innerJoin(scaleDS).on(scaleJoinExpr)


        val query = QueryBuilder.select(
                SelectResult.expression(Meta.id.from("observations")).`as`("id"),
                SelectResult.expression(Expression.all().from("traits")).`as`("trait"),
                SelectResult.expression(Expression.all().from("methods")).`as`("method"),
                SelectResult.expression(Expression.all().from("scales")).`as`("scale"))
                .from(observationsDS)
                .join(traitJoinQuery, methodJoinQuery, scaleJoinQuery)
                .where(Meta.id.from("observations").equalTo(Expression.string(observationId.toString())))

        val rs = query.execute();

Any help would be appreciated,
Thanks

2

The best way to troubleshoot query performance is to look at the query’s ‘Explanation’ property. This returns the results of SQLite’s EXPLAIN QUERY PLAN on the translated SQL query. The key thing to look at there is whether it says it will “scan” or “search”. The former is a linear process with O(n) time; the latter is a B-tree lookup with O(log n).

I’m guessing you’ve got several layers of nested scans going on, due to the unindexed joins, which is extremely slow. Look at the table scans and create appropriate indexes to resolve them into searches.