QVConnect.exe causes out-of-memory error in QlikView using SAP OLAP Connector

Question

I often have to extract months and months of data from SAP Business Warehouse (BW) in one go in QlikView and to do this I use QlikView’s own SAP OLAP Connector.

Rather than have endless repeats of the same SAP Connector Script that changes only by the month and year parameters, I decided to put them inside of a for-next loop as follows:

CUSTOM CONNECT TO "Provider=QvSAPOLAPConnector.dll;MSHOST=server1;R3NAME=SBW;GROUP=;CLIENT=100;Log=1;XUserId=ABCDEFG;XPassword=ABCDEFG;";

FOR Month = 1 TO 12
    LET MonthValue = date(addmonths(StartDate, $(Month) - 1),'YYYYMM');

    MyData: 
    LOAD
    *;
    SELECT
    PseudoMDX D (
        Dimensions (
            [0MATERIAL] (),
            [0SALESORG] ()
        ),
        Measures (
            [4VHMY6XTAN8117F8FC78GMG72].[4VHMY7D6CKFG2GI4R0BX0QDMM], // Sales
        ),
        Variables (
            [S_CALM_N] (I = [0CALMONTH].[$(MonthValue)])
        ),
    From (CUBE01/QUERY01));
NEXT

Now, this works for relatively small result sets as the memory requirement is within reasonable limits (I monitor the QlikView connector qvconnect.exe/qvconnect64.exe - 64-bit).

However, I have found that if you are running a large number of loops, even though you are not returning many rows in each , QlikView will often stop loading with an "out-of-memory" message.

How can I avoid this happening without using separate documents with separate scripts?

Answer 1

The reason for this is that it appears that qvconnect.exe/qvconnect64.exe does not garbage-collect between loops (or indeed PseudoMDX statements) so as a result, its memory footprint will steadily increase until your loop/script finishes or your machine runs out of memory.

I am not sure why the process refuses to relinquish its memory, perhaps it is trying to cache some of the dimension values for later use. I have personally seen several server jobs fail because qvconnect.exe decided to eat all of 10GiB of memory!

I could not find any official documentation on this subject, but the simple solution is to move the CUSTOM CONNECT statement inside of the loop:

FOR Month = 1 TO 12
    LET MonthValue = date(addmonths(StartDate, $(Month) - 1),'YYYYMM');

    CUSTOM CONNECT TO "Provider=QvSAPOLAPConnector.dll;MSHOST=server1;R3NAME=SBW;GROUP=;CLIENT=100;Log=1;XUserId=ABCDEFG;XPassword=ABCDEFG;";

    MyData:
    LOAD
    *;
    SELECT
    PseudoMDX D (
        Dimensions (
            [0MATERIAL] (),
            [0SALESORG] ()
        ),
        Measures (
            [4VHMY6XTAN8117F8FC78GMG72].[4VHMY7D6CKFG2GI4R0BX0QDMM], // Sales
        ),
        Variables (
            [S_CALM_N] (I = [0CALMONTH].[$(MonthValue)])
        ),
    From (CUBE01/QUERY01));
NEXT

Observing the memory footprint of qvconnect.exe whilst the loop executes shows that the memory use increases whilst the data is returned from SAP BW, but it is then freed when the next loop cycle begins.