ArangoDB Parent to Child edge creation on existing 1 milltion docucments for nested leves not working/or SLOW

Question

Created events document in ArangoDB. Loaded 1 million records as shown below which completes in 40 seconds.

FOR I IN 1..1000000
INSERT {
    "source": "ABC",
    "target": "ABC",
    "type": "REST",
    "attributes" : { "MyAtrib" : TO_STRING(I)},
    "mynum" : I
} INTO events

So record 1 is super parent, and 2 is child of 1 etc. 1 --> 2 --> 3 --> 4 --> ...1000000

Created empty Edge collection ChildEvents, and tried to establish the parent to child edge relations through the below query, but it never completes (created a hash index on mynum, but no luck)

FOR p IN events
   FOR c IN events
        FILTER p.mynum == ( c.mynum + 1 )
            INSERT { _from: p._id, _to: c._id}  INTO ChildEvents

Any help would be greatly appreciated.

score 2 · Accepted Answer · answered Jan 16 '20 at 23:10

Creating the event documents took around 50 seconds on my system. I added an index for mynum to the events collection and ran your second query (with an added RETURN NEW at the end), and it took roughly 70 seconds to process the edges (plus some time to render a subset of them):

I used ArangoDB 3.6.0 with RocksDB engine under Windows 10, Intel i7-6700K 4x4.0 GHz, 32 GB RAM, Samsung Evo 850 SSD.

Are you sure that the index is set up correctly? Explain the query and check the execution plan, maybe something is different for you?

Execution plan:
 Id   NodeType                     Est.   Comment
  1   SingletonNode                   1   * ROOT
  3   EnumerateCollectionNode   1000000     - FOR c IN events   /* full collection scan, projections: `mynum`, `_id` */
  9   IndexNode                 1000000       - FOR p IN events   /* persistent index scan, projections: `_id` */
  6   CalculationNode           1000000         - LET #5 = { "_from" : p.`_id`, "_to" : c.`_id` }   /* simple expression */   /* collections used: p : events, c : events */
  7   InsertNode                1000000         - INSERT #5 IN ChildEvents 
  8   ReturnNode                1000000         - RETURN $NEW

Indexes used:
 By   Name                      Type         Collection   Unique   Sparse   Selectivity   Fields        Ranges
  9   idx_1655926293788622848   persistent   events       true     false       100.00 %   [ `mynum` ]   (p.`mynum` == (c.`mynum` + 1))

Optimization rules applied:
 Id   RuleName
  1   move-calculations-up
  2   move-filters-up
  3   interchange-adjacent-enumerations
  4   move-calculations-up-2
  5   move-filters-up-2
  6   remove-data-modification-out-variables
  7   use-indexes
  8   remove-filter-covered-by-index
  9   remove-unnecessary-calculations-2
 10   reduce-extraction-to-projection

Thanks a lot. It worked. looks like earlier the index was not properly created in my system. I re-created and added return $NEW and it created edges in 55 seconds. Great to see the results and help.!! — Solai Srinivasan, Jan 17 '20 at 11:15
Great that it works now. Consider accepting my answer and upvoting it. Thanks! — CodeManX, Jan 17 '20 at 21:57

ArangoDB Parent to Child edge creation on existing 1 milltion docucments for nested leves not working/or SLOW

1 Answers1