Caching Customer and Inventory Information during Sales Order Processing SQL Optimized Integration on 6.0

Question: In the options tab of the destination mapping for the Sales Transaction collection of a Sales Order Processing SQL Optimized integration, there is a selection to cache Customer and/or Inventory information during the integration. I am wondering what information will be cached, when it is cleared out and how it will impact the performance of the integration?

Answer: When caching is enabled the following logic is applied:

1. Check the cache using the key(s)*.
2. If the key does not exist in the caching object, the information (customer or inventory) is retrieved from the database.
3. The information retrieved is placed into the cache and then returned from the function for processing.
4. On subsequent iterations, if the same key is encountered, the information will already be cached in local memory. The cached information is returned without requiring an additional round trip to the database.

All caching objects are cleared at the start of the integration.


Cached Customer Fields


Table - RM00101
CUSTNAME, SLPRSNID, DISGRPER, DUEGRPER, PRCLEVEL, CUSTDISC, TAXEXMT1,
TAXEXMT2, TXRGNNUM, SALSTERR, HOLD, RMCOSACC, RMSLSACC, PRBTADCD, PRSTADCD, RMTAKACC, RMAVACC, INACTIVE, RMARACC, CRCARDID, CRCRDNUM,

Table - RM00102
ADDRESS1, ADDRESS2, CITY, STATE, ZIP, COUNTRY, PHONE1, PHONE2, FAX,
TAXSCHID, UPSZONE, SHIPMTHD, CNTCPRSN,

Table - SY03300
PYMTRMID, SALPURCH, DISCNTCB, FREIGHT, MISC, TAX, DSCPCTAM, DSCDLRAM, DISCTYPE, DISCDTDS, DUEDTDS, DSCLCTYP, DUETYPE

* Caching key
CUSTNMBR

Cached Inventory Fields
Table - IV00101
ITEMNMBR, ITEMDESC, DECPLQTY, PRCLEVEL, SELNGUOM, UOMSCHDL, VCTNMTHD, DECPLCUR, IVIVINDX, DPSHPIDX, IVCOGSIX, IVSLDSIX, IVSLSIDX, IVSLRNIX, IVRETIDX, IVINUSIX, IVINSVIX, IVDMGIDX, ITMTSHID, TAXOPTNS, ITEMTYPE, ITMTRKOP, PRICMTHD, CURRCOST, STNDCOST

Table - IV00104
CMPITQTY

Table - IV00108
UOFM, UOMPRICE

Table - IV00107
RNDGAMNT, ROUNDHOW, UMSLSOPT, ROUNDTO

Table - IV00105
LISTPRCE

Table - IV40202
QTYBSUOM

* Caching keys
CurrencyID, PriceLevel, UOFM, Quantity, ITEMNMBR


Note: For caching to be effective, the same key must be encountered multiple times in the integration. If caching is enabled but the key(s) is/are not encountered multiple times, then performance can degrade. Performance will also be impacted by the amount of physical memory (RAM) on the computer and the amount of disk space used for paging. At some point the amount of memory used for caching could reach the limit where the operating system will start paging to the hard drive. If paging occurs performance could degrade and eliminate any benefit from caching.





This article was TechKnowledge Document ID: 19282