topic Memory Peak when concatenating ? in QlikView

Memory Peak when concatenating ?

Tue, 24 Jan 2012 13:55:49 GMT

Hello,

I'm getting a little frustrated about an application i'm developping (financial purposes).

I have multiple fact tables which i want to concatenate to a unique one, linked to various dimensions; tables can be grouped in 2 categories : pure financial & purchases, the biggest one is about 25 millions rows (financial).

When i reload my script, the financial ones are concatenated correctly, but as soon as QV reaches the first purchase table, there is a RAM consumption peak (from 2.5GB to 10GB, and there is max. 300000lines in each purchase table) : do you have an idea where it could come from ?

I know my concaternation aren't optimized since there are explicitly forced and tables don't have same numbers of columns; but i doubt it could come from here considering the number of lines in new tables to concat and the giant RAM consumption peak; it reminds me the behaviour of a cartesian product !

Script is attached, problem appears in tab "Faits", line 43. This app is next binary loaded by.a qvw which contains charts & other visualizations items.

I already tried to concat them differently, one by one or with intermediate concats :this is the current solution in attached file,in which i try to concat a 33millions lines table (finance) with a 300839 lines table (purchase).

Thanks for the help.

Memory Peak when concatenating ?

stephencredmond — Tue, 24 Jan 2012 14:23:38 GMT

Hi,

In my experience, when you force concatenate, QlikView has to generate the columns that are new but then goes off and creates the links to the existing rows that have already been loaded.

I always will load the largest table first and, if I am having problems like yours, make sure that I load the fields that I know will be added later with null values:

Load

SalesID,

ProductID,

SalesValue,

Quantity,

null() as PurchaseField1,

null() as PurchaseField2

...

That way, the fields are already created and you shouldn't see the spike.

Regards,

Stephen

Memory Peak when concatenating ?

Tue, 24 Jan 2012 14:33:31 GMT

I think i'm gonna try, i really don't understand what's going on : +7GB for 300.000 lines, even if QV has to generate columns it seems REALLY huge !

Re: Memory Peak when concatenating ?

Tue, 24 Jan 2012 15:38:38 GMT

Solved !

QV is really strange sometimes -> i simply changed the order of concatenation and it worked like a charm 🙂

[Before - doesn't work]:

LOAD F902

CONCATENATE

LOAD F911

CONCATENATE

LOAD F1602

CONCATENATE

LOAD F43121

CONCATENATE

LOAD F4311

[Now - totally work]:

LOAD F43121

CONCATENATE

LOAD F4311

CONCATENATE

LOAD F902

CONCATENATE

LOAD F911

CONCATENATE

LOAD F1602

If someone is able to explain this to me...