Update with ARRAY_APPEND performance

n1ql

#1

Hi,

I’ve a query which appends a new element to the array within the document. My queries are getting slower and slower though for updating I’m using primary key. I wonder if it is related with ARRAY_APPEND and the actual array containing many elements.

Here is an example of my query:

UPDATE
    `my_bucket`
SET 
    `my_bucket`.`my_array` = ARRAY_APPEND(IFMISSING(`my_bucket`.`my_array`, []), "0001535026777023-36153c4f-69a8-0001"),
    `millis` = ARRAY_MAX([IFMISSING(`millis`, 0), 1535026777020]),        
    `cnt` = IFMISSING(`cnt`, 0)+1,
    `bool` = True
WHERE meta().id = 'key'    

And my document is like:

{
  "cnt": 1666,
  "my_array": [
    "0001529069020906-16557a7e-4994-0001",
    "0001529292654188-16557a7e-4994-0001",
    "0001529292654190-16557a7e-4994-0001",
    ...
    "0001535026812391-36153c4f-69a8-0001",
    "0001535026790450-16557a7e-4994-0001",
    "0001535026775545-16557a7e-4994-0001",
    "0001535026793265-16557a7e-4994-0001"
  ],
  "bool": true  
}

my_array already contains 1666 items.

Please advise if this document structure and array_update can be the reason of slow queries.

Thanks in advance.


#2
UPDATE
    `my_bucket` USE KEYS "key"
SET 
    `my_bucket`.`my_array` = ARRAY_APPEND(IFMISSING(`my_bucket`.`my_array`, []), "0001535026777023-36153c4f-69a8-0001"),
    `millis` = ARRAY_MAX([IFMISSING(`millis`, 0), 1535026777020]),        
    `cnt` = IFMISSING(`cnt`, 0)+1,
    `bool` = True;

If you already know key try using USE KEYS which avoids index usage.

If number of array elements are more size of document is large and also any array operation is looping operation if number of elements increases loops increases and as well as time taken.

It is not any performance issue. If millis MISSING or null or number GREATEST(millis,1535026777020)


#3

Thanks. Not sure if it was USE KEYS or application server reboot but everything works much faster. And thanks for the tip with more readable GREATEST alternative