19 Replies Latest reply: May 18, 2015 10:12 PM by Simon Marles RSS

    Incremental load from QVD is not being optimised with single where clause

    Simon Marles

      Is anyone able to spot the issue with my load script? Everything is loading as expected, but the Fact_Sales load from QVD is not optimised. I've read through other posts and they all refer to ensuring a single where clause is included in the load script. One of the loads I'm doing uses about 5 million rows of data and it is quite noticeable.

       

      Note: I'm forcing an audit record of 1 in the incremental data load for testing purposes.

       

      I've tried using a Where Exist, Where Not Exists.I have also tried using Concatenate instead of same table name with no luck.

       

       

      LET vStore = 'Store1';

      LIB CONNECT TO '$(vStore1DB)';

       

      // Grab Max Audit

      $(vStore)_MaxAudit:

      LOAD MAXAUDIT

      FROM [lib://QVD/$(vStore)_MaxAudit.QVD]

      (qvd);

       

      Let $(vStore)_Audit = peek('MAXAUDIT',-1,'$(vStore)_MaxAudit');

       

      DROP TABLE $(vStore)_MaxAudit;

       

      //Load incremental data

      $(vStore)_FACT_SALES:

      LOAD

      DATEID&TIMEID&PRODUCTID&STOREID&REGISTERID&CLERKSHIFTID&PROMOTIONID&CUSTOMERID&TRANSACTIONNUMBER&SALESEQUENCE as PRIMEID,

      DATEID,

      TIMEID,

      DATEID&'-'&TIMEID AS DATETIMEID,

      '$(vStore)'&'-'&PRODUCTID AS PRODUCTID,

      '$(vStore)'&'-'&STOREID AS STOREID,

      '$(vStore)'&'-'&REGISTERID AS REGISTERID,

      '$(vStore)'&'-'&CLERKSHIFTID AS TRANDATEID,

      '$(vStore)'&'-'&CLERKSHIFTID AS CLERKSHIFTID,

      '$(vStore)'&'-'&PROMOTIONID AS PROMOTIONID,

      '$(vStore)'&'-'&CUSTOMERID AS CUSTOMERID,

      '$(vStore)'&'-'&TRANSACTIONNUMBER AS TRANSACTIONNUMBER,

      '$(vStore)'&'-'&SALESEQUENCE AS SALESEQUENCE,

      AUDITID,

      '$(vStore)'&'-'&EJHEADERID AS EJHEADERID,

      '$(vStore)'&'-'&EJPLUOBJECTID AS EJPLUOBJECTID,

      ISSECONDPRICESALE,

      ISCUSTOMPRICESALE,

      ISRETURNED,

      QUANTITY;

      SQL SELECT

      DATEID,

      TIMEID,

      PRODUCTID,

      STOREID,

      REGISTERID,

      CLERKSHIFTID,

      PROMOTIONID,

      CUSTOMERID,

      TRANSACTIONNUMBER,

      SALESEQUENCE,

      AUDITID,

      EJHEADERID,

      EJPLUOBJECTID,

      ISSECONDPRICESALE,

      ISCUSTOMPRICESALE,

      ISRETURNED,

      QUANTITY

      FROM "FACT_SALES"

      WHERE AUDITID > 1//'$($(vStore)_Audit)'

      ORDER BY AUDITID;

       

      LET vNoOfRows = NoOfRows('$(vStore)_FACT_SALES');

       

      IF  $(vNoOfRows) > 0 THEN

       

      // Update Max AuditID from latest increment.

      Let $(vStore)_Audit = peek('AUDITID',-1,'$(vStore)_FACT_SALES');

       

      $(vStore)_MaxAudit:

      LOAD * INLINE [

      MAXAUDIT

      $($(vStore)_Audit)

      ];

       

      Store * from $(vStore)_MaxAudit into [lib://QVD/$(vStore)_MaxAudit.QVD];

       

      DROP table $(vStore)_MaxAudit;

       

      // Loading STORE_FACT_SALES Data from QVD

      $(vStore)_FACT_SALES:

      LOAD

      PRIMEID,

      DATEID,

      TIMEID,

      DATETIMEID,

      PRODUCTID,

      STOREID,

      REGISTERID,

      TRANDATEID,

      CLERKSHIFTID,

      PROMOTIONID,

      CUSTOMERID,

      TRANSACTIONNUMBER,

      SALESEQUENCE,

      AUDITID,

      EJHEADERID,

      EJPLUOBJECTID,

      ISSECONDPRICESALE,

      ISCUSTOMPRICESALE,

      ISRETURNED,

      QUANTITY

      FROM [lib://QVD/$(vStore)_FACT_SALES.QVD]

      (qvd)

      WHERE NOT EXISTS (PRIMEID);