Mapping load takes too much time to reload

[kishorj1982]

Hi All,

In my script mapping load takes too much time to reload.

DIRECTORY $(RootFolderForInputQVD3);

// Retrieves the latest actual goods issue date for sales document item
[Actual Goods Issue Date Map]:
//first 1000
MAPPING LOAD [Sales Document Item Key],
Date (Max ([Actual Goods Issue Date])) as [Actual Goods Issue Date]
FROM
[SalesOrderDeliverySSL.qvd]
(qvd)
// Do not retrieve deleted lines
WHERE [Order Schedule Line Delivery Deleted] <> 'X'
GROUP BY [Sales Document Item Key];

DIRECTORY $(RootFolderForInputQVD);

// Retrieves the OTCC date for sales document item
[First OTCC Date Map]:
//first 1000
MAPPING LOAD [Sales Document Item Key],
Date (Min ([First OTCC Date])) as [First OTCC Date]
FROM
[ItemStatus.qvd] (qvd)
WHERE Not IsNull ([First OTCC Date])
GROUP BY [Sales Document Item Key];

[Item Category Map]:
MAPPING LOAD [Sales Document Item Key],
[%Item category_ITEM_CATEG]
FROM
[SalesOrder.qvd] (qvd)
// Do not retrieve offers (sales document types ZQT and ZQH)
WHERE Match ([%Sales doc. type_DOC_TYPE], 'ZQT', 'ZQH') = 0;

// Retrieves the latest goods receipt date for sales document item
[Goods Receipt Date Map]: //Required
//first 1000
MAPPING LOAD [Sales Document Item Key],
Date (Max ([Goods Receipt Date])) as [Goods Receipt Date]
FROM
[ScheduleLine.qvd] (qvd)
GROUP BY [Sales Document Item Key];

Could you please suggest how to get rid of this.

Thanks

[hopkinsc]

Hi

How long is too much time?

How much data is involved?

Can you instead store a QVD out (at the time the SalesOrderDeliverySSL.QVD is created) with the WHERE clause already done, so you can then use that QVD for the mapping load?

[kjhertz]

If you cannot segment and map the files incrementally in a Transform step then at least make sure all your QVD reads are Optimized (and distinct). It will take some scripting to achieves this but your script should run much faster, try this for the first mapping load and if it reduces your load time use the same idea on the subsequent loads:

//make sure no other field exists with the name [Order Schedule Line Delivery Deleted] in the script so far!
OrderScheduleLineDeliveryDeleted_temp:
Load * Inline [
Order Schedule Line Delivery Deleted
X
];

//This should be optimized
ActualGoodsIssueDateMap_temp:
Load distinct //distinct only necessary if it reduces the number of rows.
[Sales Document Item Key], 
[Actual Goods Issue Date]
FROM
[SalesOrderDeliverySSL.qvd]
(qvd)
WHERE not exists([Order Schedule Line Delivery Deleted]) //removes X but keeps the load optimized
;

drop table OrderScheduleLineDeliveryDeleted_temp;

[Actual Goods Issue Date Map]:
//first 1000
MAPPING LOAD [Sales Document Item Key],
Date (Max ([Actual Goods Issue Date])) as [Actual Goods Issue Date]
Resident ActualGoodsIssueDateMap_temp //hopefully resident is fast once the rows are loaded.
GROUP BY [Sales Document Item Key];

drop table ActualGoodsIssueDateMap_temp;