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?
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.