Why is this QVD Load not optimized?

rob_insley — Sun, 04 Nov 2018 22:41:00 GMT

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);

Re: Why is this QVD Load not optimized?

marcus_sommer — Mon, 05 Nov 2018 15:04:18 GMT

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

Re: Why is this QVD Load not optimized?

rob_insley — Mon, 05 Nov 2018 20:45:43 GMT

Thanks for your response Marcus.

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

Re: Why is this QVD Load not optimized?

marcus_sommer — Tue, 06 Nov 2018 08:09:18 GMT

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

topic Re: Why is this QVD Load not optimized? in QlikView

Why is this QVD Load not optimized?

Re: Why is this QVD Load not optimized?

Re: Why is this QVD Load not optimized?

Re: Why is this QVD Load not optimized?