How does 2.0 deal with CBLViews?

[Brendan Duddridge]

Hi Jens et. al.,

CBL 1.x creates separate tables in the underlying SQLite database to hold the results from the map/reduce functions. Fetching from the database was fast because essentially the results were pre-computed.

With 2.0 it looks like a lot of work is going into the query syntax to be a bit more SQL like.

Are the queries transient or do they get materialized into views the first time they're executed and from then on they would run more quickly, just like views?

How do we choose what data structures in our documents we want used for index purposes?

I have a lot of code written to create CBLViews and to emit the keys and values, so I'm just wondering if I'm in for a big rewrite or not.

Thanks,

Brendan

[Jens Alfke]

On Feb 6, 2017, at 3:32 PM, Brendan Duddridge <bren...@gmail.com> wrote:

With 2.0 it looks like a lot of work is going into the query syntax to be a bit more SQL like.

Are the queries transient or do they get materialized into views the first time they're executed and from then on they would run more quickly, just like views?

The current 2.0 query implementation does not use views or map/reduce at all. (Whether to support map/reduce is still an open question and feedback is welcome.) It’s much more like querying a SQL database, or N1QL queries in Couchbase Server.

- Any query you construct will run; it just might not be fast, because by default it has to do a linear scan on every document. 

- To make queries faster, create indexes on the properties/expressions they’re scanning for.

- A CBLQuery object takes some time to create. So for best performance, you should create a CBLQuery once and keep it around. Using parameters instead of hardcoding values in the query helps you do this.

[Actually in practice I’ve found that even unindexed queries are pretty fast! But I haven’t benchmarked them with databases larger than about 12,000 docs, or with ‘cold’ database files.]

How do we choose what data structures in our documents we want used for index purposes?

Call -[CBLDatabase createIndexOn:…]. Indexes are persistent, but the call is a no-op if the index already exists, so there’s no harm in creating your indexes on every launch. If indexes become obsolete it’s best to delete the old ones, though.

As for what to index, it’s usually the property or properties being tested by the ‘where’ predicate. 

If that looks like “cost >= $MINCOST and cost <= $MAXCOST”, you’d want to index “cost”. 

If it’s “type == ‘lampshade’ && cost >= $MINCOST”, then you want an index on [type, cost].

Also, if what you’re comparing is the result of an expression on a property, you should index that expression, not just the property. So if the query is for “lowercase(name) == $NAME”, then you should index “lowercase(name)”, not “name”.

I have a lot of code written to create CBLViews and to emit the keys and values, so I'm just wondering if I'm in for a big rewrite or not.

A good rule of thumb for conversion is that the key in your view’s emit() call is the same thing you should be indexing. (This is because the view index is composed of the keys you emit, and that has a very similar purpose to the index generated by SQLite.)

I recently added a method -[CBLQuery explain:] that returns a description of how the query works. That includes the SQL and also the result of SQLite’s “explain query plan” command, which is documented here. The key thing is to look for where the query plan says “scan” — that’s an inefficient linear scan of all docs. You want to see “search”. It might also talk about creating a temporary index/tree; that can be slow too, but there’s sometimes no way around it, for example if you want to search on one criterion but then sort the results by a different one.

—Jens

[Brendan Duddridge]

Thanks for all that info Jens. Very helpful.

 then you want an index on [type, cost]

Is it possible to build indexes given a keyPath?

In my model I have a structure like this:

"form" : "frm-41ebd7ee0d024aaf95c37d2cd10c0072",

"values" : {

  "fld-92b86046bfa44942a67095a3b63d8884" : "Brendan Duddridge",

  "fld-cfac4311481d43ceb92024faf1f09faa" : "sup...@emailaddress.com",

  "fld-d3c794118b0449a099f1ca903e4a64b6" : 1500,

  "fld-d4999599e2bd46b79c2260c320a038ad" : {

    "alert" : false,

    "date" : "2012-07-28T22:48:24.190Z"

  },

  "fld-eed8a32c52654558bd3620744af3355d" : 2

So I might search for all records that have a date between today and next week, etc. So I would want an index on "values.fld-d4999599e2bd46b79c2260c320a038ad.date" in that situation.

With CBLViews, I could just look at the document structure and emit() the date directly as a key in the view which effectively gives me what I need.

For complex queries with multiple search rules I build up CBLViews based on whatever the criteria is the customer asks for. It's a very dynamic data model. Essentially a database on top of Couchbase Lite.

Thanks!

Brendan

[Jens Alfke]

On Feb 6, 2017, at 10:18 PM, Brendan Duddridge <bren...@gmail.com> wrote:

Is it possible to build indexes given a keyPath?

Sure. Anything you can create an NSExpression for.

—Jens

[Brendan Duddridge]

As for feedback on the inclusion of CBLViews in 2.0, I would like that because it will make transitioning to the new query language easier. If I can run my app almost as is with 2.0 and then migrate my queries over that would be ideal. Then I can take a phased approach to converting. It already took me almost a year to migrate from raw SQL queries to Couchbase Lite map/reduce in my current app. Mind you I did add a bunch of new features along the way too, so it wasn't all pure SQL -> map/reduce conversion.

I did have to write a conversion routine to convert my SQLite tables to CBLModel / JSON data structures though.

So anything that can help make that process easier has my vote.

Thanks,

Brendan

[Todd Freese]

I agree. I have a lot of existing work with map/reduce. I would much rather move to 2.0 and migrate them over time.

Todd