Take out where condition

[has35526]

I have the below where condition in the QVD I need to take this out and optimized the QVD. Can you please let me know how can I achieve this. 

FROM [lib://$(vTransformationPathName)/T_package.qvd]
(qvd)
where category = 'ACCEPTED' or
category = 'REJECTED';

[TheLazyDeveloper]

Create an Inline load with those two values and change your where condition to a where exists to keep it an optimized load:

Temp:

Load * INLINE [

category

ACCEPTED

REJECTED

];

FROM [lib://$(vTransformationPathName)/T_package.qvd]
(qvd)
where exists(category);

Drop table Temp;

[Anil_Babu_Samineni]

@has35526 How do you mean by optimize, In theory you are not loading full QVD and giving already some restrict data to load, this case this is already optimized. If i miss anything, please explain your issue with performance so we will see the possibility. 

[has35526]

@Anil_Babu_Samineni 

2024-03-28 15:42:39 0134 
2024-03-28 23:13:21 39 fields found: 

2024-03-28 23:13:21 268,511,700 lines fetched

so it took about 7 hrs to fetched 268,511,700. so I was thinking it could be because of the where condition 

[marcus_sommer]

Beside the where exists() you may consider any incremental approach and/or accesses to sliced data which may in your case maybe: T_package_CATEGORY_YYYYMM.qvd and then looping with a filelist() and/or further for- and if-loops over the data.

[Anil_Babu_Samineni]

@has35526 It is important at the same time how many concurrent reloads happening on the same scheduler/engine nodes? did you explored all the options from admin side? If so, please share more about how granular data and is this one time load or the same table/directory used more than one time with the help of resident/preceding load etc.

[has35526]

@Anil_Babu_Samineni The is was the only job was running at that time. I am attaching the report report script for your reference. 

[marcus_sommer]

To keep a qvd-load optimized there mustn't any processing be included which means only a where clause like: where exists(FIELD) or a renaming like: Field_X as Field_Y is possible because it are checks/transformations against the system-tables respectively the meta-data and not the data itself.

This means you need to remove the following from your load:

ceil((Row_Number / 100)) as Pages,
...,
Date(Monthstart(processing_date),'MMM-YYYY') as MonthYear

by moving these measurements in a previous ETL-step in which the qvd was created. 

Beside this take also a look on the content of the processing_date - is it really a date or it's a timestamp. If it's a timestamp you should split it into dates and times in the qvd-creating layer.