topic Re: Load data in chunks from resident table in QlikView

Load data in chunks from resident table

Thu, 06 Mar 2014 02:08:52 GMT

Hi guys,

i have a requirement to shift data from from one resident table to another after applying some where clauses.

[T1]:

Load * resident T2

where data < '12/31/2014'

drop table T2;

T2 is really big table with millions of rows in it. I get too much of memory consumption because both tables exist in memory at some point of time.

So I think if I extract data from chunks from T2 to T1 and at the same time delete that chunk from T2 then i might not have that much of memory consumption.

Can you guys help me with that?

Regards,

Saurabh

Re: Load data in chunks from resident table

Thu, 06 Mar 2014 04:39:39 GMT

much better way would be to create new QVW which generate a new QVD (your chunk), then use your existing QVD directly in your QVW that will save your memory drastically

Re: Load data in chunks from resident table

Alejandro_Hernández — Thu, 06 Mar 2014 05:42:51 GMT

if your table is really big you can try breaking it down into several QVD's (e.g. T1_MMYYYY.qvd) and load only the ones you need (e.g. if an incremental load process load only the latest MMYYYY). Also, try to read directly from QVD vs resident as much as possible, it is x-times faster without RAM spikes.

Re: Load data in chunks from resident table

Thu, 06 Mar 2014 06:45:30 GMT

Are you saying that reading from QVD is faster than reading from resident?

Re: Load data in chunks from resident table

Thu, 06 Mar 2014 06:46:10 GMT

Can you please one small example?

Re: Load data in chunks from resident table

Thu, 06 Mar 2014 07:30:09 GMT

Loading from QVD is always preferred as it's in the main, always quicker than hitting a datasource directly in the end application.

I usually run a qvd loader QVW where all the datasources are polled into individual QVD's, this runs as per schedule and the main reporting application uses these QVD's as the source. If you try it with your T2 data you will see a huge improvement even by using the where clause for the datefield.

Load the T1 data from the newly created qvd file.

QVD_LOADER_APP.qvw

[T2]

Load *

FROM source_data;

STORE [T2] into 'location_share\T2_QVD.qvd'(qvd);

Re: Load data in chunks from resident table

Thu, 06 Mar 2014 07:44:07 GMT

your first QVW

load * from T2 where data < '12/31/2014';

store * from T2 into T21.qvd (qvd);

your second qvw

load * from T21.qvd (qvd);