How to speed up OnCalcFields containing lookups
Posted on 2006-05-30
I have a (simplified) table setup like this:
where A.VAL1 and A.VAL2 are lookups into B and C, respectively.
Now in my 3-tier app, I have a TClientDataSet for each table and A should
also contain two calculated fields CALC1 and CALC2.
Expressed as Pseudo-SQL those fields should have the following values:
CALC1 = A.SIZE * B.VALUE where A.VAL1=B.ID
CALC2 = A.SIZE * C.VALUE where A.VAL2=C.ID
So, for my TClientDataSet cdsTableA I wrote the following OnCalcFields (also somewhat simplified):
procedure OnCalcFields( DataSet: TDataSet );
with DataSet do
cdsTableB.Locate( 'ID', FieldValues['VAL1'],  );
FieldValues['CALC1'] := cdsTableB.FieldValues['VALUE'] * FieldValues['SIZE'];
cdsTableC.Locate( 'ID', FieldValues['VAL2'],  );
FieldValues['CALC2'] := cdsTableC.FieldValues['VALUE'] * FieldValues['SIZE'];
So far this works quite fine for few records, but as soon as TABLE A contains about
6000 records, calculating those values for all records is painfully slow.
And I have to show all records, due to application requirements, so there is no way
to execute OnCalcFields only for a subset of all records at once.
It's definitely the Locate() operations that take so much time, when using static values
it gets lightning fast.
Is there any way to speed this up considerably or should I take a completely other approach here?
I thought about including the lookup values in the query for cdsTableA in the
first place, but that gets ugly when it comes to more than 2 lookup values (and I have more than 2).
Also, the users wouldn't see new calculated values until they applied their changes to the database.
thx in advance