TTable and lookup fields performance issue

Question

I have a TTable (actually a UniDac TUniTable) which has the table fields plus 2 lookup fields from another table (Mysql). I have set up the correct indexes and the table loads very fast. The problem is when I manually edit a field like

Table1.FieldByName('discount_value').AsInteger := 10;

Everytime this command is executed even without Post() it goes very slow. If I remove the 2 lookup fields everything is fine - its super fast.

It looks like that somehow the lookup field loaded on every record edit even before the Post() is executed.

Is there any way to prevent this or somehow retrieve the lookup fields once and then cache without loading again and again?

Answer 1

You can experiment with the TField.LookupCache property, which controls whether the values of the lookup field are cached or not.

Determines whether the values of a lookup field are cached or looked up dynamically every time the current record in the dataset changes.

Set LookupCache to true to cache the values of a lookup field when the LookupDataSet is unlikely to change and the number of distinct lookup values is small. Caching lookup values can speed performance, because the lookup values for every set of LookupKeyFields values are preloaded when the DataSet is opened. When the current record in the DataSet changes, the field object can locate its Value in the cache, rather than accessing the LookupDataSet. This performance improvement is especially dramatic if the LookupDataSet is on a network where access is slow.

There is more information available in the documentation linked above, including information about some performance considerations and manually refreshing the LookupList at runtime.

Answer 2

From the Embarcadero WiKi the AutoCalcFields must be set to False in order to calculate only then the record is opened.

http://docwiki.embarcadero.com/Libraries/XE6/en/Data.DB.TDataSet.AutoCalcFields

When AutoCalcFields is False, Lookup fields are recalculated and the OnCalcFields event occurs only when:
* The dataset is opened.
* The dataset is put into dsEdit state.
* A record is retrieved from database.