N1QL Key-value operations vs. Queries

Question

Right now I'm trying to determine which is more efficient for a problem I have. Here's the situation:

Problem Statement I have an array of objects that I want to update/insert into my Couchbase database. If they are already in the database, they should update all fields but one. If they are not in the database, they should simply be inserted in. I have a working parametrized Merge statement that does what I want, where it accepts the array of objects as a parameter and does the update/inserts accordingly. But I was wondering, is it more efficient to run that single query, or is it better to loop through the array and for each object run a Get() key-value operation for each object to see if it exists in the db and then run an insert() function?

Unfortunately, I don't have a great estimate on the size of the array, but what I do know is that this job won't be run very frequently, if that's any help. Thanks in advance for your help!

If already know document keys use SDK key-values. You can also explore key-value UPSERT. — vsr
@vsr A key-value UPSERT would clobber the existing field the OP wants to preserve. — dnault
The reason i said upsert is "If they are already in the database, they should update all fields but one. If they are not in the database, they should simply be inserted in." — vsr

dnault dnault · Accepted Answer · 2020-08-27T00:39:21

In general, if you know the keys, it's more efficient to use the KV service, as vsr pointed out.

In this specific case you're doing something just slightly off the beaten path, so then answer to "Is it more efficient?" might depend on the documents you are working with.

Here's something you could try using the KV service. Measure and see whether it out-performs the N1QL query.

STEP 1: Use a sub-document operation to fetch only the field whose value you want to retain. If the document is found, remember the CAS value from the result and GO TO STEP 3. Otherwise GO TO STEP 2.

STEP 2: The document doesn't exist, so try to insert it. If the insertion succeeds, you're DONE! Otherwise, if the insertion fails because the document exists (someone else inserted the document before you), GO TO STEP 1.

STEP 3: Take the existing field value you retrieved in STEP 1 and merge it into your document. Then do a REPLACE operation using the CAS value. If the replace fails due to CAS mismatch (someone else modified the document before you) GO TO STEP 1.

Alternatively... if you know your document can have at most 16 top-level fields, you could do the entire update in a single subdocument operation.

N1QL Key-value operations vs. Queries

1 Answers