Slow N1QL update - how to troubleshoot?

SQL++
1

I have a relatively simple query to update approx. 50.000 docs (total bucket is 450-500.000 docs) on a test server (two cluster nodes, each 6GB RAM, CouchbaseCE 6.0.0):

update data set statstriptarget=false where type="Catch"

I enter the query in the web admin interface. The query times out after 6 minutes (only having updated perhaps half of the docs). This seems way too slow - so something must be spoiling it for me. But how do I find out??

Thanks in advance!

2

N1QL update is full document update.

Create temporary index and use following update and drop the index.

CREATE INDEX ix1 ON data(type) WHERE type="Catch" AND IFMISSINGORNULL(statstriptarget,true) != false;
UPDATE data
SET statstriptarget  = false 
WHERE type="Catch" AND   IFMISSINGORNULL(statstriptarget,true) != false ;

If timeout repeat the UPDATE until mutation is 0 (you may want use scan_consistency or wait few secs to catch the index).

Other option is use SDK and subdoc API

3

Ok, thanks. I was just unsure if I did something wrong. I’ll try the index approach next time :+1: