Very important to get a good load-performance is to ensure that the qvd's are within the optimized mode. This means each file must have the same data-structure and there are no measures included which require a processing of the data - to make it short: only a renaming of fields and a where exists-clause with a single parameter are allowed to keep a load optimized.
In your case it means you need to adjust the where-condition - for the date-part this might be done with something like this:
[DateForExists]: load date(floor(today(1) - 1 + recno())) as [store date]
autogenerate today(1) - addmonths(today(1), -24);
and then loading from the qvd with:
load ... where exists([store date]);
If there is more as a single condition the multiple conditions must be merged in beforehand. In your case it could mean to create an extra field within the routine which creates the qvd maybe with something like this:
if(batch<>"", [store date], null()) as [store date for exists]
Another essential point is only to access the relevant qvd's. This means if there are daily files from the last 20 years your wildcard-load will access each file and checking the date. Means it touched 20 years, checked them, skipped 18 years and loads finally 2 years. Better then this is to access only the wanted 2 years and this could be done with a filelist() loop, like:
for each file in filelist('YourPathWithFilePattern.qvd')
if keepchar(subfield('$(file)', '\', -1), '0123456789') >= addmonths(today(1), -24) then
load ...
end if
next
This requires of course that the date/period information is included within the filename which is often the case and if not you should consider to adjust them appropriate. Depending of your final pattern you may need a few adjustments to the format- and/or picking-logic but it's not difficult and could be easily tried within a few text-boxes.
Beside this you should only load those fields and records which are really needed - means no record-id's and if there are timestamp fields they should be divided into a date and a time field (of course already in beforehand by creating the qvd).
- Marcus
