topic Hierarchy and memory in QlikView

Hierarchy and memory

amien — Tue, 26 Jan 2010 22:51:14 GMT

I'm loading 2.3mil records with a 4GB 32bit version. each record contains a Node and Parent .. But i get a out of memory error .. i'v checked if any node is the same as the parent .. or if a parent of the partent points to the node. What can i do to optimize this? should i make a CTE in SQL? of is this just not possible in QV 4GB/32bit?

[Adjacent Nodes]:
LOAD NodeID, NodeName, NodeLocation, ParentID, ParentName;
SQL SELECT *
FROM TABLE;

[Hierarchy]:
HIERARCHY (NodeID,ParentID,L,ParentName,NodeID,Path)
LOAD
 NodeID
,ParentID
,NodeName as L
,NodeID as PathSource
RESIDENT [Adjacent Nodes]
;

Hierarchy and memory

johnw — Tue, 26 Jan 2010 23:13:50 GMT

If you keep the Adjacent Nodes table, then my guess is that the creation of synthetic keys between your two tables is causing the problem.

But I'm guessing that you don't keep the Adjacent Nodes table. It looks like you're only using it to load the hierarchy table. In that case, one big problem may be that you have both tables in memory at the same time during the load, essentially doubling your memory requirements. I'd instead combine these into a single load, doing the hierarchy load directly from the SQL data instead of creating a temporary table to load from. That might be sufficient to resolve the problem.

There might be other issues. It isn't necessarily a problem in this specific section of the script, even if adding this script is what caused you to go over your memory limit.

Hierarchy and memory

amien — Tue, 26 Jan 2010 23:17:42 GMT

John .. Thank for fast reply..

Adjacent Nodes table .. i'm not dropping this table .. i should right?

Hierarchy and memory

amien — Tue, 26 Jan 2010 23:23:36 GMT

dropping table gives same result

Hierarchy and memory

amien — Tue, 26 Jan 2010 23:24:55 GMT

John .. did you just build a Treeview in SQL and load that in QV?

Hierarchy and memory

johnw — Tue, 26 Jan 2010 23:37:42 GMT

The only hierarchy loads I've done have been samples using inline data. For the few applications where I might consider using this, I would just load the node and parent node from SQL. I'd start simple, and work my way up from there. Something like this:

[Hierarchy]:
HIERARCHY (NodeID,ParentID,NodeName)
LOAD *;
SQL SELECT
NodeID
,ParentID
,NodeName
FROM TABLE;

Hierarchy and memory

amien — Wed, 27 Jan 2010 14:33:27 GMT

thats what i'm doing now .. and that causes lake of memory .. i want to make something in SQL now..

or make a full hierarchie:

level1, level2, level3, level4, all in 1 row..

add an additional column with the level of the nodeID

nodeid, parentid, level

.....

any comments?

Hierarchy and memory

amien — Thu, 28 Jan 2010 23:28:44 GMT

John,

i'v found this example from you:

http://community.qlik.com/forums/p/19682/75132.aspx#75132

this is basicly what i need (last example) .. but i cant transform it into my data layout..

i dont have rowno() .. no BOM_ID .. i could add my Ultimate Parent for each NodeID if needed..

i only have NodeID, NodeName ,ParentID and Level (in relation to the UltimateParent)

Hierarchy and memory

johnw — Fri, 29 Jan 2010 01:04:55 GMT

OK, doing a little testing, I made a very simple hierarchy out of 2.3 million rows with this script:

[Hierarchy]:
HIERARCHY (NodeID,ParentID,L)
LOAD
recno() as NodeID
,if(recno()>1 and rand()>.3,ceil(rand()*(recno()-1))) as ParentID
,recno() as L
AUTOGENERATE 2300000;

During the load, memory usage peaked at about 820 MB, then dropped back to 210 MB when it was finished. For comparison, removing the hierarchy statement resulted in peak memory usage of 180 MB and 60 MB when finished. Baseline memory usage for QlikView looks to be about 15 MB, which I should probably subtract, giving us this:

Load Peak After
Hiearchy 805 MB 195 MB
Raw Data 165 MB 45 MB

Assuming this is representative, and it may well not be, QlikView is using four to five times as much memory to create and store a hierarchy AS a hierarchy instead of as simple parentage information. I suppose that shouldn't surprise me, as the hierarchy load generated separate field names for 25 levels of data.

And this was using about the simplest node IDs I can - sequential integers. Since you're using node names in practice, QlikView will likely require substantially more memory than this for your real application, so I can see how it could easily fail with only 2GB of memory available.

So I guess I can say that some brief testing supports the idea that it isn't something you're doing wrong, or if it is, it's something we're both doing wrong (certainly possible). But my test seems like about the simplest sort of hierarchy load possible, so it's hard for me to see what I might have messed up.

If you can use simple, integer node IDs rather than node names, it might use somewhat less memory. That's about the only thing I have to suggest other than abandoning the hierarchy load.

Perhaps someone else with actual experience in hierarchy loads can be of more help.

Hierarchy and memory

amien — Fri, 29 Jan 2010 17:19:20 GMT

ok // i have loaded only the fields nodeid, parentid en nodename .. loading is no problem .. the pivot is the problem

in one sheet i have a list of all nodeids .. and in sheet 2 the pivot .. i qlik a nodeid and then switch to sheet 2 to see the pivot chart with all the related node-ids

any comments?