Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
Are you saying that reading from QVD is faster than reading from resident?
Can you please one small example?
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);
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);