Insert Update Incremental Load

m_perreault — Sat, 16 Nov 2024 06:36:38 GMT

Hi All,

I have file structure such as below. Each Folder will have a number of different files. Currently I have a load that loops through each file in the folder to make a QVD and I want to improve this process by making an Incremental Load. Note it is possible for files in past Months folders to change.

I am utilizing an Insert Update, incremental methodology.

I've implemented the below Incremental Script and it reduces the load from ~10 minutes to ~6 minutes but I am wondering if anyone has any suggestions on how I could optimize it to make it even faster?

FilesHistTemp:
Load * inline
[
x
];

let vPeriod = Num('$(vMinPeriod)');

let vLastExecution = TimeStamp(QvdCreateTime('$(vHistoricalQVD)'),'YYYY-MM-DD hh:mm:ss');
let vThisExecution = Timestamp(Now(),'YYYY-MM-DD hh:mm:ss');

do while vPeriod < Year(Today())*100+Month(Today())

let vYear = Num(left(Text(vPeriod),4));
let vMonth = Num(right(Text(vPeriod),2));
let vFolder = left(Text(vPeriod),4)&'-'&right(Text(vPeriod),2);
let vMonthEndNum = num(date#(monthend(date#('$(vPeriod)','YYYYMM'))));

FOR Each vFile in filelist('$(vHistoricalPath)\$(vFolder)\$(vHistoricalFile)')

let vFileSize=FileSize('$(vFile)');
Let vFileExists=if(vFileSize>0,-1,0);
let vFileTime= Timestamp(FileTime('$(vFile)'),'YYYY-MM-DD hh:mm:ss');

if $(vFileExists) and ('$(vFileTime)' >= '$(vLastExecution)' and '$(vFileTime)' < '$(vThisExecution)') then

Concatenate(FilesHistTemp)
LOAD *,
$(vMonthEndNum) as MonthEndNum,
FileTime() as FileTimeStamp,
Port & Num(Floor(Date)) as PrimarKey
FROM [$(vFile)]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

EndIf;

NEXT vFile;

if vMonth = 12 then
let vPeriod = (vYear+1)*100 + 1;
else
vPeriod = vPeriod + 1;
endif;

loop;

Concatenate(FilesHistTemp)
Load *
From [$(vHistoricalQVD)] (qvd)
Where not Exists(PrimaryKey,Port & Num(Floor(Date)));

drop fields x,PrimaryKey;

Store * from FilesHistTemp into [$(vHistoricalQVD)] (qvd);
Drop Table FilesHistTemp;

Thanks,
Mark

topic Insert Update Incremental Load in App Development

Insert Update Incremental Load