Why is this QVD Load not optimized?

[rob_insley]

I am attempting to improve the performance of a particular load script by ensuring QVD Loads are optimised. 

Load Script as follows:

The following code ensures optimized load.

LET OrgId = 81;

UNQUALIFY;

OrgLoad:

LOAD * INLINE

[OrgId

$(OrgId) ];

TempAP:

LOAD *

FROM

..\2_QVD\AP_INVOICES_ALL.qvd (qvd)

WHERE EXISTS (OrgId);

The following code does not use optimized load when naming the fields individually.  There are no calculations or other tranformations that would cause this to be an unoptimized load as far as I understand.

LET OrgId = 81;

UNQUALIFY;

OrgLoad:

LOAD * INLINE

[OrgId

$(OrgId) ];

TempAP:

LOAD

    %AP_INVOICES_KEY,

    %AP_CALENDAR_KEY,

   APInv.InvoiceId,

    APInv.InvoiceNum, 

    APInv.Currency,

    APInv.InvoiceDate,

    APInv.Source,

    APInv.InvoiceType,

    APInv.Description,

    APInv.TermsDate,

    APInv.PaymentMethod,

    APInv.PayGroup,

    APInv.PaymentStatusFlag,

    APInv.CreationDate,

    APInv.XMLReceivedDate,

    APInv.CreatedBy,

    APInv.BaseAmount,

   APInv.GoodsReceivedDate,

    APInv.InvoiceReceivedDate,

    APInv.ApprovedAmount,

    APInv.VoucherNum,

    APInv.DocSequence,

    APInv.DFF1,

    APInv.DFF2,

    APInv.DFF3,

    APInv.DFF4,

    APInv.DFF5,

    APInv.InDispute,  /*CM010500*/

   APInv.InDisputeReason,  /* Ctes 2017/04 Added*/

    APInv.GLDate,

    APInv.CancelledDate,

    APInv.ApprovalStatus,

    APInv.EnteredBy,

    APInv.VendorNumber,

    APInv.VendorName,

    APInv.VendorSmallBusinessFlag,    /*REL2015/04*/

    APInv.VendorMinorityType,         /*REL2015/04*/

    APInv.VendorSitePaymentFax,       /*REL2015/04*/

    APInv.VendorSitePostCode,         /*REL2015/04*/

    APInv.VendorSitePaymentPriority, /*REL2015/06*/

     APInv.VendorSiteSensitiveData,  /*RELEASE 2018/01 */

    APInv.VendorSiteCode,

    APInv.VendorSiteTaxCode,  //Added REL1304

    APInv.VendorType,         //Added REL1304

    APInv.VendorSitePaymentEmail, /*REL2015-01*/

    APInv.BatchName,

    APInv.BatchDate,              /*RELEASE 2018/01 */

    APInv.OrgShortCode,           /*RELEASE 2018/01 */

    APInv.FutureDatedInvoiceFlag,       /*RELEASE 2018/01 */

    APInv.LastUpdateDate,

    APInv.LastUpdatedBy, /* ADDED 201702 CTES */

    APInv.CancelledAmt, /* ADDED 201702 CTES */

    JOIN_APINV_ORGID, 

    APInv.CertificateNumber,    /*Added Release 2017/03*/

    APInv.CertificateType,        /*Added Release 2017/03*/

    APInv.Comments,                /*Added Release 2017/03*/

    APInv.TaxName,                /*Added Release 2017/03*/

    APInv.BatchCreatedBy,   /*Added in 2017/04 CTES*/

    APInv.BatchCreatedByName ; /*Added in 2017/04 CTES*/

    FROM

..\2_QVD\AP_INVOICES_ALL.qvd (qvd)

WHERE EXISTS (OrgId);

The following simplified load script also results in a non optimized oad

LET OrgId = 81;

UNQUALIFY;

OrgLoad:

LOAD * INLINE

[OrgId

$(OrgId) ];

TempAP:

LOAD

    %AP_INVOICES_KEY

   FROM

..\2_QVD\AP_INVOICES_ALL.qvd (qvd)

WHERE EXISTS (OrgId);

[marcus_sommer]

Maybe it's your unqualify-statement which missed any fields or a wildcard. Therefore try:

UNQUALIFY *;

OrgLoad: LOAD 81 as OrgId autogenerate 1;

TempAP:

LOAD

    %AP_INVOICES_KEY

   FROM

..\2_QVD\AP_INVOICES_ALL.qvd (qvd)

WHERE EXISTS(OrgId);

- Marcus

[rob_insley]

Thanks for your response Marcus.

Good spot there, but unfortunately that doesn't seem to make any difference.

[marcus_sommer]

Normally the above should work. Just put this load into a complete new qvw without any other code and try it again. I assume it will work then and this means that any other stuff within your other qvw prevents the optimized loading.

I never noticed such behaviour before but I could imagine that some outside-logics in regard of a load-statement might enforce a processing of the data, for example NULL-variables like NULLASVALUE or BUFFER statements and probably some other rarely used options more.

- Marcus