Applying .range() and .keys() on different key elements in a viewQuery

Couchbase Server
1

I have a sample document below:

{
companyId: 1,
productId: 2,
companyName: “ABC”,
productName: “XYZ”,
productType: “qwerty”,
epoch: 20150801,
location: “Mumbai”
}

The “epoch” value is an integer representing date. The view is created in design document named demo and view name demoView, in which the map function is:
function(doc, meta)
{
emit([ doc.epoch, doc.companyId, doc.productId ], doc]);
}

Using Node sdk, I want to write a query to retrieve all documents after a given date and which match a set of given companyId and productId combinations.

For example,
latestEpoch=request.payload.epoch
keyArray=[ [1,1], [1,2], [3,5], [2,6] ] // combination of Company Id and Product Id

var query = couchbase.ViewQuery.from(‘dev_demo’, ‘demoView’).range( [latestepoch,{},{}], [{},{},{}] ).keys(keyArray).limit(50);

Can I use range query on the first key element and .keys on the second and third elements?

2

To the best of my knowledge, no you cannot. the range way of querying and the keys method both need your compound keys to be complete (ie. to have the correct number of elements, here 3).

@brett19 unless I’m missing something available in the Node SDK?

If you had been only interested in a unique combination [A, B] for the second and third parts of your key, you could still have done it with range only:

query.range( [latestepoch,A,B], [{},A,B] );

Or maybe even with several contiguous values for the second part (eg. A through Z):

query.range( [latestepoch,A,B], [{},Z,B] );

Doing the same for the third part of the key would select the cartesian product of parts 2 and 3 of the range… So sparse values for parts 2 and 3 are AFAIK not possible.

Looks like your use case could be a good match for N1QL :wink:
Have you tried out the Couchbase Server 4.0 BETA?

3

@simonbasle
Thanks a lot for the immediate reply. We did try using N1QL, but the response time is far too low for our purposes. We created a GSI primary index on the bucket as well as secondary indices on the fields companyId and productId, but the response from the N1QL query is much worse than when we use views for the same purpose.
Any other suggestions?

4

maybe @colm or @geraldss can help on that front?

@shraddha it was separate indexes for productId and companyId right? And you did not create a secondary index on the epoch field?
What was the timing difference like, broadly?

5

We did create separate secondary indices on companyId, productId and epoch. We performed apache benchmarking on a single node cluster, on a machine with 192 GB RAM and 40-core processors.

The response time for 10000 requests with 200 concurrency was around 700ms (time per request mean) using Views , whereas N1QL gave us a response time of 6600ms on the exact same data.

6

Hey shraddha,

Though I’m not certain if the keys parameter is compatible for use simultaneously with the range parameter, it is possible to do an epoch search alone on an index built with additional values using group_level. If you wish to search only the first element of the emitted array, simply specify a group_level of 1. Perhaps you would get acceptable performance by performing a search by epoch on the server and simply filtering your companyId and productId on the client side?

var q = new couchbase.ViewQuery(...);
q.range([latestEpoch], [latestEpoch]).group_level(1);
bucket.query(q, ...);

Cheers, Brett

7

Hello @brett19 and @simonbasle

We had tried your approach of retrieving only on the basis of epoch value. But the filtering becomes complex while matching it with companyId and productId combinations. Using group_level needs a reduce function in viewquery.

Also, when we ran the N1ql query through cbq engine, the CPU utilization of couchbase server went upto 98%.

8

@shraddha did you benchmark N1QL with a composite index on companyId, productId and epoch? Can you share the N1QL query?