topic OUT OF VIRTUAL AND/OR LOGICAL MEMORY, allocationg 4096 MB in QlikView

OUT OF VIRTUAL AND/OR LOGICAL MEMORY, allocationg 4096 MB

Tue, 15 Jun 2010 19:20:36 GMT

Can anybody explain what's wrong is going on with this:

This is MS Server 2008 Enterprise x64. I have QV 9.00.7440.8 SR3 64-bit Edition on it.

As you can see, it has 98GB RAM. At the error time, it cached 15.6 GB and 48.8 GB of free memory in page file. But it still have about 60GB in RAM as free memory. Why does it say what it says?

If it loads about 100 millions records for main table there is no problem at all. But I need more data for longer period. And it starts to happen when it loads 500 millions records for main table. The dashboard doesn't have any synthetic keys. Here is part of the script:


Let vFromDate=num(monthstart(addmonths(now(), - 13)));
Let vToDate = num(floor(now() - 4));
Let vTempDate = vFromDate;
set ErrorMode = 0;
do while vTempDate < vToDate

 Let FileFilter = date(vTempDate, '-YYYY-MMM-D');
 AggrSession:
 load
 TZHourStationId,
 AggrCountry,
 AggrTLH,
 AggrSS,
 AggrES
 from ..\qvd\AggrSession$(FileFilter).qvd (qvd);

 Let vTempDate = vTempDate + 1;

loop
set ErrorMode = 1;
TZStationCalendar:
load distinct
 TZHourStationId
resident AggrSession;

left join (TZStationCalendar)
load
 TZHourStationId,
 num(left(TZHourStationId, 1)) as TZId,
 num(right(TZHourStationId, 6)) as StationId,
 num(mid(TZHourStationId, 2, 7)) as dmNumDateHour
resident TZStationCalendar;

It runs OK till TZStationCalendar creation statement. When it tries to do the last LEFT JOIN (TZStationCalendar) the error is popping up.

Does anybody have any idea what's wrong with it?

Thanks,

Nick

OUT OF VIRTUAL AND/OR LOGICAL MEMORY, allocationg 4096 MB

johnw — Tue, 15 Jun 2010 20:46:24 GMT

Not sure why the server won't grab more memory.

I don't expect this to solve your memory problem, but it does eliminate the problematic join and should run somewhat faster:

TZStationCalendar:
LOAD
TZHourStationId
,num(left(TZHourStationId,1)) as TZId
,num(right(TZHourStationId,6)) as StationId
,num(mid(TZHourStationId,2,7)) as dmNumDateHour
;
LOAD fieldvalue(TZHourStationID,iterno()) as TZHourStationID
AUTOGENERATE 1
WHILE len(fieldvalue(TZHourStationID,iterno()))
;

OUT OF VIRTUAL AND/OR LOGICAL MEMORY, allocationg 4096 MB

Tue, 15 Jun 2010 21:52:20 GMT

It works. Thank you so much.

So in other words fieldvalue function is auto distinguish mechanism for each field, isn't it?

But why it doesn't work with regular approach with "LOAD DISTINCT" ?

OUT OF VIRTUAL AND/OR LOGICAL MEMORY, allocationg 4096 MB

johnw — Tue, 15 Jun 2010 21:56:22 GMT

Yeah, the fieldvalue() function just loads distinct field values without having to reference a source table. So if your source table is large, and your number of field values is somewhat smaller, it generally works faster to do the load that way.

As for why your original script would have such serious problems, I honestly have no idea. It certainly LOOKS correct to me. There's nothing technically wrong with the LOAD DISTINCT then LEFT JOIN approach that I'm aware of. I assume we're both missing something; I just can't see what.