I am trying to use Hierarchy and HierarchyBelongsTo on a big table without success. The table currently holds just over 2 million records but will in the end hold ~10 million. When I run the script I can see the individual tables loading as usual but when it comes to the hierarchy statements it goes silent. The processor load is 50% and stays like that for hour after hour. If I try to abort nothing happens, it just keeps going at full speed. When I kill the process the whole PC is in bad shape, e.g. has lost wireless access and can't regain it without rebooting, etc. I've tried this a few times, each time for > 4 hours, always with the same result.
As a test I did the same thing but with a small table and it works perfectly so I suspect that it is a performance issue.
Does anyone know of any known size limitations to using hierarchies?
Are there any other ways to handle recursive structures of unknown depth?
Any help would be greatly appreciated.
See below for the relevant parts of the script that I use.
------------------------------------------------------------------------------
MAST:
LOAD Material,
BOM
FROM
[MAST.xlsx]
(ooxml, embedded labels, table is MAST);
STPO:
LOAD BOM,
Component
FROM
[STPO1.xlsx]
(ooxml, embedded labels, table is STPO1);
LEFT JOIN (MAST) LOAD
*
RESIDENT STPO;
DROP TABLE STPO;
Hierarchy(Component, Material, Component, 'Parent', Component, 'Path', '/', 'Level' ) Load
*
RESIDENT MAST;
