I used CacheCalcFields quite a lot for base tables and the performance are quite good when the tables are small. However, when I try on a bit larger tables, its performance is strangely slow.
I did a fresh new application with 2 tables. First table with 8000 records. The 2nd had 10000 records lookup on the first table with 1 field.
I watch the Duration on MySQL Monitor.
All the not mentioned properties are default ones.
CacheCalcFields disabled :
Both table load in around 0.1 seconds
CacheCalcFields enabled for both tables :
1st table load in 1.8 seconds (strange as no lookup and calculated field)
2nd table load in 28 seconds!
CacheCalcFields enabled for 2nd table :
1st table load in 0.1 seconds and 2nd table load in around 28 seconds again.
I had tried to change FetchRows property
200 : no change 28 seconds
1000, 5000, 10000 : 2nd table load in 22 seconds.
I also try to add a limit on the 2nd table (fetchRows 1000)
1000 : 0.6 secs
2500 : 1.5 secs
5000 : 5.6 secs
7500 : 12.9 secs
10000 : 22 secs
I tried quite many times and result are more or less the same.
It seems that the CacheCalcFields do many recalculation agina and again while loading the records. Also When I try to lookup on a calculated field on 1st table and I set a break in the oncalfield event of 1st table after 1st table load (CacheCalcFields true) and b4 2nd table set active. When 2nd table start to load, the onclfield triggered many times which should not be as those value should have been cached, which further make the 2nd table load time extended to around 40 seconds.
I hoped this problem can be fixed so I don't need to do a major reprogramming for more loading table core. Thank you.
I am using MyDAC professional 5.90.0.57 with Delphi 7 Enterprise SP2 in WinXP.