Update Old qvd's with data

[Prajna]

Hi,

 I have an application which loads 94 qvd files  and we keep getting new columns added into our qvd . In current code ,all columns existing  in the qvd from the beginning are assigned a name and set to a variable called Fields. for the new columns that come up ,first  the header is loaded and then a check is made to see if the column exists in a particular qvd. If not then its updated with 'NA' values and these new fields are then added to the table. Now the problem is that the no. of new columns are increasing day by day and the number of checks for the new columns are slowing down the app. How can i improve this code to make the application work faster.

LET vExtractCount=0;
LET vMaxFileCount=94;
LET vFileName = '';
LET vCurrentDate = Today();
LET v2MonthCutOff = AddMonths(today(),-2);
LET v6MonthCutOff = AddMonths(today(),-6);
LET v2YearCutOff = AddMonths(today(),-24);
LET vSubFolder = 'QVD_Folder\';

Do while vExtractCount < vMaxFileCount
// Extract for current Date
let vFileName = 'BTR_'&Date#(vCurrentDate,'YYYY-MM-DD')&'.qvd';
IF len(FileSize(vFilePath &vSubFolder &vFileName)) > 0 THEN

// setting initially with always included variables

SET fields =
"COL_A as [A],
COL_B as [B],
COL_C as [C],
COL_D as [D],
COL_E as [E],
";

// Load only the header row
COLS:
FIRST 1 LOAD *
FROM $(vFilePath)\$(vSubFolder)\$(vFileName) (QVD);

//Check if the column exists in the temp table 'COLS'
IF (FieldNumber('COL_X','COLS')>0) THEN
SET fields = $(fields),[COL_X] as [X] ;
ELSE
SET fields = $(fields),'N/A' as [X];
END IF;

IF (FieldNumber('COL_Y','COLS')>0) THEN
SET fields = $(fields),[COL_Y] as [Y] ;
ELSE
SET fields = $(fields),'N/A' as [Y];
END IF;



DROP TABLE COLSIN;

//Load the fields
Table1:
Concatenate(Table1)
LOAD
$(fields)
From $(vFilePath)\$(vSubFolder)\$(vFileName) (QVD);

//Update the count
let vExtractCount = vExtractCount + 1 ;

endif;

//Swich to the next QVD
if vCurrentDate > v2MonthCutOff THEN
LET vCurrentDate = Date(vCurrentDate-1);
elseif vCurrentDate > v6MonthCutOff THEN
LET vCurrentDate = Date(vCurrentDate-7);
elseif vCurrentDate > v2YearCutOff THEN
LET vCurrentDate = AddMonths(vCurrentDate,-1);
else
LET vExtractCount = 1000;
endif;

LOOP;

[Miguel_Angel_Baeyens]

Without deep diving into your code, but based on your description, I don't see any "easy" way to dramatically improve performance here. 

If I understood right, you cannot just CONCATENATE QVDs since you need that "NA" value for the field which will not exist into older files and you cannot join to previous QVDs and reduce the number of QVDs since you don't know if or when new fields will be coming.

I would suggest, though, why the number of columns in such QVDs must increase versus why the new data cannot have a value to be linked in the data model with older QVDs.

Additionally, I would suggest loading an empty table including the new field names and concatenating this with older fields, and storing this new table into a new QVD, so the number of checks tomorrow will be the same or very similar, i.e.: you are not increasing one field every day, since the previous day has already all previous fields added.

If you really need that "NA" you could reload that new QVD with both new and old fields and check the length of every field and assign 'NA' If(Len(Field) = 0).

Even if the script took the same time to execute, making any table bigger means more values, more resource consumption and higher footprint, which you cannot avoid, and probably, over time, longer response times and poorer user experience.