Memory Usage: Memory Limit Issues

[sjhussain]

Hello,

We are facing problems with reloading the data and the memory reaching its limit. (32GB).

Following is the configuration of the machine and the data we are trying to load

* Virtual Machine

* Intel Xeon CPU E5-2665 @ 2.40GHz 2.40 GHz (2 Processors)

* Memory : 32 GB

* OS: Windows Server 2012 R2 Standard

* OS Type: 64 Bit

* Data Size: We already read 400 million rows from SQL Server and made it into a QVD (DataLog04JUN2015.qvd) - 14GB

* Now we are creating another QVD to further reduce the size - by using the correct data type in Qlikview - Autonumber, Date/Time, Num etc.

What is happening on reloading:

* The memory is gradually increasing and becomes full (31.9GB) during the reload and around 100 million rows

* Then the memory comes down to 25 GB and stay around this size for some time and then when it reaches 180 million row the memory becomes full again

* The remote session disconnects and the application hangs up

What are some of the best practices we can do to keep things under control.  We do not really want to increase the memory because we feel that 32GB is sufficient enough.

Will appreciate if someone can assist us with this.

Thanks.

//-------- Start Multiple Select Statements ------
DataLog:
LOAD 
  AutoNumberHash256("log_id_pk") as log_id_pk,
    num("log_tun_id") as log_tun_id,
    num("log_gpsvalid") as log_gpsvalid,
  Year(Date(Date#(log_datetimeindata,'YYYYMMDDHHmmss'),'DD/MM/YYYY HH:mm:ss')) as log_datetimeindata_y,
  Month(Date(Date#(log_datetimeindata,'YYYYMMDDHHmmss'),'DD/MM/YYYY HH:mm:ss')) as log_datetimeindata_m,
  Day(Date(Date#(log_datetimeindata,'YYYYMMDDHHmmss'),'DD/MM/YYYY HH:mm:ss')) as log_datetimeindata_d,
  Hour(Date(Date#(log_datetimeindata,'YYYYMMDDHHmmss'),'DD/MM/YYYY HH:mm:ss')) as log_datetimeindata_h,
  Minute(Date(Date#(log_datetimeindata,'YYYYMMDDHHmmss'),'DD/MM/YYYY HH:mm:ss')) as log_datetimeindata_mi,
  Second(Date(Date#(log_datetimeindata,'YYYYMMDDHHmmss'),'DD/MM/YYYY HH:mm:ss')) as log_datetimeindata_sec,
  num("log_speed") as log_speed,
    num( "log_mileage") as log_mileage,
    num("log_altitude") as log_altitude ,
    "log_eventtype",
    num("log_di1") as log_di1,
    num("log_cst_id") as log_cst_id,
    num("log_dpt_id") as log_dpt_id,
    num("log_drv_id") as log_drv_id,
    num("log_ass_id") as log_ass_id
FROM (qvd);
//-------- End Multiple Select Statements ------

[hic]

My guess is that 32GB just is too little RAM for your data. 400 million records is quite a lot.

HIC

[Anonymous]

You could Floor()  what you want as date data :

     Year(Date(FLOOR(Date#(log_datetimeindata,'YYYYMMDDHHmmss')),'DD/MM/YYYY HH:mm:ss')) as log_datetimeindata_y,

and Frac() what you want as time data :

     Hour(Date(FRAC(Date#(log_datetimeindata,'YYYYMMDDHHmmss')),'DD/MM/YYYY HH:mm:ss')) as log_datetimeindata_h, 

and see how it performs RAM & QVD size wise.

[marcus_sommer]

Maybe you could have a change to load these data with only 32 GB RAM if you could remove

AutoNumberHash256("log_id_pk") as log_id_pk

which is probably quite distinct or at least replaced it with

AutoNumber("log_id_pk") as log_id_pk

which returned a number and not a string. Alos you should check your other fields. If this not worked you will need more RAM.

The Importance of Nothing

- Marcus