OK, the problem is easily reproduced with a much smaller example. I don't have a lot of data points to support this, but equal increases in the number of groups appear to produce approximately equal increases in additional peak memory required over the storage for the base table. In my case, every million groups added about 200 MB of memory.
These additions also started at the memory for the base table, not at double as would be suggested by the method I mentioned before. So that's even further evidence that QlikView is not using that method. For a small number of groups, it's using a very small amount of memory, much more efficient than duplicating the full table. But for a large number of groups, it's using a huge amount of memory, much less efficient than duplicating the full table.
The group by also appears to follow a very specific, three phase pattern. In the first phase, no rows are being loaded into the final table, and memory use is increasing quickly and linearly over time. In the second phase, still now rows are being loaded into the final table, memory use remains flat at the peak, and a fair amount of time passes, perhaps approximately equal to the first phase. In the third phase, memory spikes a little higher than before as the rows are actually loaded into the final table.
Let's see, if I had data stored like QlikView and I had to code an algorithm to efficiently process SMALL numbers of groups, how would I do it? Well, in my case I'm only grouping by a single field. So maybe I'd move down through the values of that field, using my normal relational processing to return all the rows for that value, then sum those rows up, insert it into my final table, and move on to the next value.
But the observations are inconsistent with that. That shouldn't use much memory at all, since each group is processed independently, and then the working storage for it could be dropped. Also, it should result in a one phase memory profile instead of a three phase memory profile, as each group gets processed completely before moving on to the next.
For fun, I'm trying the experiment I mentioned earlier - making a chart do my "group by". Hah, no problem. It ran much faster, and had a much lower memory peak than doing the equivalent during the load. Charts are almost definitely handling it differently than the load. That seems peculiar, though perhaps it does have something to do with not having the full in-memory model until the end of the load. Even the chart used more peak memory than I think it should, though.
OK, the first phase of a script group by is linear increases in memory. That sounds like loading some sort of internal data structure. The second phase is flat, which sounds like just computation being done on that data structure. Finally, we spike as we load the real data, during which probably both the internal data structure and the final table exist at the same time. That's my theory, anyway.
If that is roughly what's going on, the internal data structure isn't actually holding the full table of data, as then it wouldn't use so little memory for few groups and so much memory for many groups. It uses approximately the same amount of memory per group, even though the number of rows per group is decreasing with the number of groups since I used the same size table each time. So the memory used has nothing to do with the number of rows in that group. Well, how many bytes per group? In my example, about 200 bytes of memory per group, regardless of size of group. Hmmm, does the size of a row in the underlying table affect it? That doesn't appear to affect it. What about having twice as many rows in the underlying table? That doesn't appear to affect it.
Well, this hypothetical data structure would have to have a key for the group, so at least the group value or a pointer to it. Since memory doesn't go up during the hypothetical calculation phase, we'll assume it's reserving space for the calculation. But in my example, that doesn't seem like more than a small fraction of the 200 bytes (well, 180 bytes). So what are all the other bytes? What data would help us to do the calculation? The obvious thing to me is some way at pointing at the "rows" of data, but we obviously can't be storing the rows or even pointers to the individual rows, or the size required would go up with the number of rows in each group.
No idea. I am not currently thinking of any useful internal data structure consistent with what I've observed. Perhaps I'm off track, and there's no such structure. Or perhaps I'm just not thinking of what it would include.
My script, in case it's useful. I was mostly fiddling with the mod number and the autogenerate number.
A:
LOAD
mod(recno(),3000000) as GroupID
,1 as Value
AUTOGENERATE 5000000
;
B:
LOAD
GroupID
,sum(Value) as Value2
RESIDENT A
GROUP BY GroupID
;
I'm giving up for now. Maybe someone else can think of something, or maybe a developer can comment.
