SDK method versus N1Ql Query

Hi,

Can someone explain the internals and which is performant enough between spring data SDK method “findById” and a n1ql query with @query annotation ?

For ex, spring data has provided below method to fetch an entity

Mono<T> findById(ID id);

And we can write custom method with @Query annotation,

@Query("#{#n1ql.selectEntity} use keys $1")
Mono<T> findObjectById(final String id);

Out of above two, which is better ?
We are assuming that SDK method does document lookup and it directly fetches from data node whereas @Query relies on query nodes and eventually on data node so it might be slower. Please suggest.

And subsequent queries on the same topic,
2. Which is better “Use Keys” or “where Meta().id in” ?
3. For doing a count operation, which way is better, count(*) or count(1) ?

Thanks,
Hari

@hari in general every time you know the document ID, using KeyValue (so in your case findById without a n1ql query) is going to be the most efficent way to retrieve the document. As a matter of fact, if you run a N1QL query in the background the query engine will also use KeyValue to look up the document.

So in your case - without knowing more about the specific use case - the first snippet is going to be more efficient.

2 Likes

A major difference between the kv api (*ById) and the query api (everything else) is that the sdk will send the jv request to the node where the document resides and there is no additional network hops.The query request will go to a query engine which will then need to fetch the document from the data node, and then return it to the client. I’ve done some rudimentary benchmarks and found the kv api to be 10-20 times faster. Your mileage may vary.

Thank you @mreiche and @daschl .
Could you answer the other two queries also please ?

  1. Which is better “Use Keys” or “where Meta().id in” ? - From the above replies, its evident that KV operation is much more efficient. Here in this case, does both of them “use keys” and “where clause with Meta().id” relies upon kv operation or the request lands onto query engine ?

  2. For doing a count operation, which way is better, count(*) or count(1) ?

Thanks,
Hari

Any query content will go to the Query service. Direct SDK document retrieval (findById) is more efficient if you know the keys and want complete documents.

Within the Query service USE KEYS goes directly to the KV, meta().id = is an Index lookup; which is most efficient would depend on document size, what other predicates are used, what fields are selected and what indexes exist to support the query. If just a simple key lookup of the entire document then USE KEYS is the most efficient within the Query service.

(If your documents are large and you’re selecting only a few fields, all of which are part of an index, you could have a covered index scan which likely would be more efficient.)

For COUNT, it doesn’t matter; the plans are identical. Either is an aggregate without grouping keys.

HTH.

(3) please do timed test of each and post your result.

(2) since the kv api can be used to retrieve the document belonging to the id, why would you use query?