Page 1 of 1

Doubts on usage of OnCalcFields

Posted: Mon 06 Feb 2012 15:26
by brace
I am using OnCalcFields to perform many operations, but tipically what I do is something like:

msQuery1.FieldByName('MyCalcField').asString := Trim(msQuery1.FieldByName('DESCRIPTION').asString);

or

msQuery1.FieldByName('MyCalcField').asString :=
msQuery1.FIeldByName('NAME') + ' ' + msQuery1.FIeldByName('SURNAME')

(this are simple examples, of course the same could be done directly in the sql query, anyway in my real case things are more complex).

While profiling the application I found that the OnCalcFields was executed more times that expected. I was expecting it to execute only at msquery1.open.

Now i read in the help that it executes more times, expecitally if a dataaware control is connected to it.

So i would like to ask: how can I keep "calculating fields" like that and being sure that the calculation is performed only once to avoid unnecessary waste of time and resources?

Posted: Mon 06 Feb 2012 16:38
by brace
Just to add more data: somehow the behaviour I want is that of looping all the dataset just after opening it.

THis may be can call for doinf

msquery1.first
while not msquery1.eof do
begin
msQuery1.FieldByName('MyCalcField').asString := Trim(msQuery1.FieldByName('DESCRIPTION').asString);
msquery1.next;
end;

in the after open event.

What do you suggest? May be this could be a solution, but that OnCalcFields firing many times it is a problem now for me.

I realize that this happens only when a DBControl is linked to the dataset, but this happens also when enablecontrols and disablecontrols is used.

Posted: Mon 06 Feb 2012 17:08
by AndreyZ
The OnCalcFields event is triggered many times to make calculated field values valid. Therefore, OnCalcFields is triggered not only when dataset is opened. You can make field values calculation more efficient by setting the TMSQuery.Options.CacheCalcFields property to True. CacheCalcFields can be useful for reducing CPU usage for calculated fields. For more information, please read the SDAC documentation.

Posted: Mon 06 Feb 2012 17:26
by brace
Hello, thanks for the reply.

CacheCalcFields is not in the Help.


Anyway I tried and it partially solved the problem.

For example in a dataset I have 311 records and the event is NOW (after setting AUtoCalcFields to False and CacheCalcFields to True) the event is fired 622 times (so "only" twice than needed).

Before it fired almost 3000 times.

If I could change 622 to 311 it would be perfect. DO you have an idea on how to do it?

Posted: Tue 07 Feb 2012 07:49
by brace
One more information. In the oncalcfield I also fire queries in some cases , dummy example:

MSQuery1OnCalcFIelds code:

TempMSQuery.ParamByName('EMPLOYEE_ID'):= myQuery1.FieldByName('EMPLOYEE_ID');

TempMSQuery.Open;

msQuery1.FieldByName('MyCalcField').asString :=
msQuery1.FIeldByName('NAME') + ', Department: ' + TempMSQuery.FIeldByName('DEPARTMENT') ;

TempMSQuery.Close;

In this example it is clear that opening twice TempMSQuery it is a waste of resources. THis is why I would like to fo from 622 to 311.

Can you suggest? Can you also comment on running queries in the OnCalcFields event?

Posted: Tue 07 Feb 2012 08:32
by AndreyZ
The description of the CacheCalcFields is in the SDAC documentation. Here is a screenshot from the SDAC documentation:Image
If you don't edit data, and set the TMSQuery.AutoCalcFields property to False and the TMSQuery.Options.CacheCalcFields property to True, then the number of triggering of the OnCalcFields event will be equal to the number of records in a dataset. For example, if you have 300 records in your dataset and use the following code:

Code: Select all

MSQuery.Open;
MSQuery.First;
while not MSQuery.Eof do
  MSQuery.Next;
, the number of triggering of the OnCalcFields event will be equal to 300.

Posted: Tue 07 Feb 2012 13:04
by brace
After your comment I double checked and I found a place were the event was triggere manually. So now it works as expected.

Anyway I realize now that the performance in my application is sometimes bad because of the wrong habit of using the OnCalcFields without knowing it can be recursive.

About the help. Yes the link is there.

The problem is that if I have focus on Object inspector / TMsQuery / Options the help of TCustomMSDataSet.Options Property is opened and not the one of TMSQuery, this is why I didn't found the topic.

Thanks.

Posted: Tue 07 Feb 2012 13:46
by AndreyZ
It's good to see that the problem was solved. Feel free to contact us if you have any further questions about SDAC.