Case of bad performance due to EntireTable CacheLookup

Recently I was looking at an issue where changing a quantity in Purchase order line is taking up to a minute long. Tracing it and found the root cause was due to the CacheLookup property is set to EntireTable.
(This happens to intercompany order)


One of the code triggered from the PurchLine.PurchQty change is the code below which do an insert into ‘InterCompanyEndpointActionPolicyTransfer’ table.

This table has a property of CacheLookup = EntireTable, which its default behaviour is cache the entire table into memory if less than 128KB or write them to disk if more than 128KB.

A check into the data size of this table, it is around ~58MB, hence, each insert into this table is flushing the cache (for this table), then rewrite them all into the disc again.

In my case, changing this 'CacheLookup' property to "Found" resolve it.
The .insert() now completed in less than a second.