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?
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.
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:
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.
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
