Doubts on usage of OnCalcFields

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Posts: 212
Joined: Wed 14 Feb 2007 08:26

Doubts on usage of OnCalcFields

Post by brace » Mon 06 Feb 2012 15:26

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);


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

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?

Posts: 212
Joined: Wed 14 Feb 2007 08:26

Post by brace » Mon 06 Feb 2012 16:38

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

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

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.


Post by AndreyZ » Mon 06 Feb 2012 17:08

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.

Posts: 212
Joined: Wed 14 Feb 2007 08:26

Post by brace » Mon 06 Feb 2012 17:26

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?

Posts: 212
Joined: Wed 14 Feb 2007 08:26

Post by brace » Tue 07 Feb 2012 07:49

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');


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


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?


Post by AndreyZ » Tue 07 Feb 2012 08:32

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

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

Posts: 212
Joined: Wed 14 Feb 2007 08:26

Post by brace » Tue 07 Feb 2012 13:04

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.



Post by AndreyZ » Tue 07 Feb 2012 13:46

It's good to see that the problem was solved. Feel free to contact us if you have any further questions about SDAC.

Post Reply