It depends on various parameters which loading-type - resident or per file (qvd) - is more suitable. If the RAM is short and the data are swapped to the virtual RAM it's usually better to store the origin table into a qvd and drop it then and the following steps load then new from the qvd. If your network/storage performance is rather poor you may intentionally use the reverse approach. Also the capabilties of the CPU to process all the loading/transforming will have an impact. This means you need to measure which part is your biggest bottleneck to bypass it as much as possible.
Beside this by your mentioned amount of data you should consider to apply a multi-stage data-architecture and/or to implement an incremental load-logic.
Personally I use very seldom joins especially not if the datasets are rather big and/or there are risks to double/reduce the number of records in regard to the table-relation of the keys respectively to the data-quality. The much better approach is the already mentioned mapping - it's simple and fast, you could define the values of non-matching keys and with concatenated values you could also use a single mapping for multiple fields. Here an example:
map: load KEY, F1 & '|' & F2 & '|' & F3 inline [
KEY, F1, F2, F3
1, a, b, c
....
];
bigTable:
load
*,
subfield(applymap('map', KEY, 'YourDefault1|'), '|', 1) as F1
subfield(applymap('map', KEY, '|YourDefault2'), '|', 2) as F2
subfield(applymap('map', KEY, '||YourDefault'), '|', 3) as F3
from Source;
- Marcus
