1 Reply Latest reply: Jun 3, 2013 8:26 AM by Nigel West RSS

    Hierarchy performance issues

    estoy

      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;

        • Re: Hierarchy performance issues

          Hi

           

          I just came across this and was wondering if you ever got an answer to it either through the forum or via QlikTech support?  I've just tried to create a hierarchy on a table with 4 million rows on it and I'm getting the exact same problem as you did.

           

          I've used hierarchy loads for years and they have always worked fine but of course this has been on much smaller tables (50K rows or less).

           

          Any update?

           

          Nigel.