Query taking long time (about 4-5 seconds) to finish

[parvez....@decurtis.com]

For an iOS application using Couchbase Lite framework (version 1.3.1 (build 6)), a CBLLiveQuery is taking considerable time (about 4-5 seconds) for a database that is not having more than 1000 documents. The view is emitting document with key as date (from a chat message). The CBLView structure is as below:

if let aView: CBLView = self.database?.viewNamed("cmview"){

            aView.setMapBlock({ (doc, emit) in

                if let messageDate = doc["messageDate"]{

                    emit(messageDate, doc)

                }

            }, version: "1")

        }

messageDate is a CBLJSON's date instance for example 2017-07-19T14:07:15.469Z

The corresponding query;s structure is:

if let aView = self.database?.existingViewNamed("cmview"){

            chatMessagesQuery = aView.createQuery().asLive()

            if let query = chatMessagesQuery{

                let loggedInUserDocumentID = <a UUID string>

                let otherUserDocumentID = <a UUID string>

                let predicate = NSPredicate(format: "(value.fromId = %@ && value.toId = %@) || (value.fromId = %@ && value.toId = %@)", loggedInUserDocumentID,otherUserDocumentID,otherUserDocumentID,loggedInUserDocumentID)

                query.postFilter = predicate

                query.descending = true

                query.limit = 30 //The query is fetching documents in batches of 30 each

                do{

                    _ = try query.run()                    

                }catch{

                    print("Encountered error: \(error) while fetching chat messages.")

                }

            }

        }

What can be the reason behind slow performing query? The documents are already synced to the iOS device on which application is running. How can I be sure that the view is getting properly indexed or not. The document size is small.

The first batch fetch time is long.

Thanks

[Jens Alfke]

Any time you wonder about slow performance in an iOS (or Mac) app, you should reach for Instruments and run the CPU Profiler.

From looking at your code, I don’t see anything obvious. The map function should be fast. NSPredicates aren’t terribly fast, but it shouldn’t take 5ms to run that predicate. I’m curious to hear where Instruments says the bottlenecks are.

However, that’s a poorly optimized query: it’s going to do a linear scan of the index every time it runs, instead of taking advantage of b-tree search. Your search criterion is the (fromId, toId) pair, so that’s what you should be indexing on. What I would do is
emit( [min(doc.fromId, doc.toID), max(doc.fromID, doc.toId)], doc.messageDate )
Then query with key = [min(fromId, toID), max(fromID, toId)], and sort the results by row.value (which will be the date.)

In case it’s not clear: the reason for the min and max calls is to create the same key whichever order the fromId/toId appear in, since the query code you gave doesn’t care about the ordering.

—Jens

[parvez....@decurtis.com]

Shouldn't the view's emit block to include the fromId, toId along with messageDate as key as shown below:

emit([doc.messageDate,doc.fromId,doc.toId], doc)

Will it help resolve the issue? I haven't tried the instrument profiler yet. 

[Jens Alfke]

On Jul 20, 2017, at 7:37 AM, parvez....@decurtis.com wrote:

Shouldn't the view's emit block to include the fromId, toId along with messageDate as key as shown below:

emit([doc.messageDate,doc.fromId,doc.toId], doc)

If you do that, then you can’t efficiently search for ID pairs any more, only dates. That’s just the way indexes work — the whole index would be ordered by increasing messageDate, with the different fromID and toID values scattered around it.

You want the messageDate for sorting. An index can’t be used to search and sort by different criteria; if you need this you have to do the sort in postprocessing. (SQL databases just hide this reality from you as part of the underlying query plan.)

—Jens

[parvez....@decurtis.com]

Can you elaborate a little more on the use of min / max function inside the emit block ? Since we are specifying the messagerDate only in value field of emit section then how we can get the complete chat message corresponding to this date when query is run?

On Wednesday, July 19, 2017 at 9:43:38 PM UTC+5:30, Jens Alfke wrote:

[Jens Alfke]

On Jul 27, 2017, at 7:39 AM, parvez....@decurtis.com wrote:

Can you elaborate a little more on the use of min / max function inside the emit block ?

If your query wants to find docs with a (fromID, toID) pair regardless of ordering, then the key you emit needs to ignore ordering too. So a doc with {fromID: “a”, toID: “b”} should emit the same key as one with {fromID: “b”, toID: “a”}. An easy way to do that is to write the two IDs in sorted order.

Since we are specifying the messagerDate only in value field of emit section then how we can get the complete chat message corresponding to this date when query is run?

CBLQueryRow.document.

—Jens