Performance Issue using Java SDK - N1Q1 Queries

I am having some performance issues when executing N1Q1 queries using the Java SDK (v 2.7.10).
The total number of documents in the bucket is approx. 50k.
When executing the queries in the workbench the elapsed time is 1-2 s . However, from the Java SDK, it takes 4 - 5s.
Any help ?

ParameterizedN1qlQuery query =
    N1qlQuery.parameterized(query, JsonObject.fromJson(params));
  Observable<JsonObject> observable = bucket.async()
    .query(query)
    .map(AsyncN1qlQueryRow::value);

What is your query and index. Are you using IN clause . Checkout https://blog.couchbase.com/in-list-handling-improvements-in-couchbase-server-6-5/

Thanks, @vsr1.

The query uses the IN clause with primary index but with static values . Like this,
select* from bucketname as item where meta().id LIKE β€œuser::%” and item.userid IN [β€œ1”, β€œ2”, β€œ3”, β€œ4”, β€œ5”, β€œ6”, β€œ7”, β€œ8”, β€œ9”, β€œ10”, β€œ11”, β€œ12”, β€œ13”]
and (item.firstname LIKE β€œ$search” OR item.lastname LIKE $search) OFFSET $offset LIMIT $limit ;

Are there any better approaches using some indexing to improve the response time?

If you have already have document keys, USE KEYS better.

You should checkout above link.
Avoid primary index (http://index-advisor.couchbase.com/). If you have to use IN clause with query parameters use adhoc=true

1 Like