How to improve Update query in arangodb

Question

I have a collection which holds more than 15 million documents. Out of those 15 million documents I update 20k records every hour. But update query takes a long time to finish (30 min around).

Document:

{ "inst" : "instance1", "dt": "2015-12-12T00:00:000Z", "count": 10}

I have an array which holds 20k instances to be updated.

My Query looks like this:

For h in hourly filter h.dt == DATE_ISO8601(14501160000000) 
   For i in instArr
      filter i.inst == h.inst
      update h with {"inst":i.inst, "dt":i.dt, "count":i.count} in hourly

Is there any optimized way of doing this. I have hash indexing on inst and skiplist indexing on dt.

Update

I could not use 20k inst in the query manually so following is the execution plan for just 2 inst:

FOR r in hourly FILTER r.dt == DATE_ISO8601(1450116000000) FOR i IN 
 [{"inst":"0e649fa22bcc5200d7c40f3505da153b", "dt":"2015-12-14T18:00:00.000Z"}, {}] FILTER i.inst == 
 r.inst UPDATE r with {"inst":i.inst, "dt": i.dt, "max":i.max, "min":i.min, "sum":i.sum, "avg":i.avg, 
 "samples":i.samples} in hourly OPTIONS { ignoreErrors: true } RETURN NEW.inst

Execution plan:
 Id   NodeType              Est.   Comment
  1   SingletonNode            1   * ROOT
  5   CalculationNode          1     - LET #6 = [ { "inst" : "0e649fa22bcc5200d7c40f3505da153b", "dt" : "2015-12-14T18:00:00.000Z" }, { } ]   /* json expression */   /* const assignment */
 13   IndexRangeNode      103067     - FOR r IN hourly   /* skiplist index scan */
  6   EnumerateListNode   206134       - FOR i IN #6   /* list iteration */
  7   CalculationNode     206134         - LET #8 = i.`inst` == r.`inst`   /* simple expression */   /* collections used: r : hourly */
  8   FilterNode          206134         - FILTER #8
  9   CalculationNode     206134         - LET #10 = { "inst" : i.`inst`, "dt" : i.`dt`, "max" : i.`max`, "min" : i.`min`, "sum" : i.`sum`, "avg" : i.`avg`, "samples" : i.`samples` }   /* simple expression */
 10   UpdateNode          206134         - UPDATE r WITH #10 IN hourly
 11   CalculationNode     206134         - LET #12 = $NEW.`inst`   /* attribute expression */
 12   ReturnNode          206134         - RETURN #12

Indexes used:
 Id   Type       Collection   Unique   Sparse   Selectivity Est.   Fields   Ranges
 13   skiplist   hourly       false    false                 n/a   `dt`     [ `dt` == "2015-12-14T18:00:00.000Z" ]

Optimization rules applied:
 Id   RuleName
  1   move-calculations-up
  2   move-filters-up
  3   move-calculations-up-2
  4   move-filters-up-2
  5   remove-data-modification-out-variables
  6   use-index-range
  7   remove-filter-covered-by-index

Write query options:
 Option                   Value
 ignoreErrors             true
 waitForSync              false
 nullMeansRemove          false
 mergeObjects             true
 ignoreDocumentNotFound   false
 readCompleteInput        true

Answer 1

I assume the selection part (not the update part) will be the bottleneck in this query.

The query seems problematic because for each document matching the first filter (h.dt == DATE_ISO8601(...)), there will be an iteration over the 20,000 values in the instArr array. If instArr values are unique, then only one value from it will match. Additionally, no index will be used for the inner loop, as the index selection has happened in the outer loop already.

Instead of looping over all values in instArr, it will be better to turn the accompanying == comparison into an IN comparison. That would already work if instArr would be an array of instance names, but it seems to be an array of instance objects (consisting of at least attributes inst and count). In order to use the instance names in an IN comparison, it would be better to have a dedicated array of instance names, and a translation table for the count and dt values.

Following is an example for generating these with JavaScript:

var instArr = [ ], trans = { }; 
for (i = 0; i < 20000; ++i) { 
  var instance = "instance" + i;
  var count = Math.floor(Math.random() * 10);
  var dt = (new Date(Date.now() - Math.floor(Math.random() * 10000))).toISOString();
  instArr.push(instance);        
  trans[instance] = [ count, dt ];  
} 

instArr would then look like this:

[ "instance0", "instance1", "instance2", ... ]

and trans:

{ 
  "instance0" : [ 4, "2015-12-16T21:24:45.106Z" ], 
  "instance1" : [ 0, "2015-12-16T21:24:39.881Z" ],
  "instance2" : [ 2, "2015-12-16T21:25:47.915Z" ],
  ...
}

These data can then be injected into the query using bind variables (named like the variables above):

FOR h IN hourly 
  FILTER h.dt == DATE_ISO8601(1450116000000) 
  FILTER h.inst IN @instArr 
  RETURN @trans[h.inst]

Note that ArangoDB 2.5 does not yet support the @trans[h.inst] syntax. In that version, you will need to write:

LET trans = @trans
FOR h IN hourly 
  FILTER h.dt == DATE_ISO8601(1450116000000) 
  FILTER h.inst IN @instArr 
  RETURN trans[h.inst]

Additionally, 2.5 has a problem with longer IN lists. IN-list performance decreases quadratically with the length of the IN list. So in this version, it will make sense to limit the length of instArr to at most 2,000 values. That may require issuing multiple queries with smaller IN lists instead of just one with a big IN list.

The better alternative would be to use ArangoDB 2.6, 2.7 or 2.8, which do not have that problem, and thus do not require the workaround. Apart from that, you can get away with the slightly shorter version of the query in the newer ArangoDB versions.

Also note that in all of the above examples I used a RETURN ... instead of the UPDATE statement from the original query. This is because all my tests revealed that the selection part of the query is the major problem, at least with the data I had generated. A final note on the original version of the UPDATE: updating each document's inst value with i.inst seems redudant, because i.inst == h.inst so the value won't change.