[OT] Frustrated with OnCalcFields
Eric Schreiber
>Oh, now the truth comes out! This is a no-win situation for you <vbg>,
>and like all "family" programming, you are of course thinking about it
>over the holidays <vvbg>.
[...]
>At least you did use the word "hired".
Hired, and paid handsomely for the work. The real problem is that in
addition to being the largest application I've ever done and being
very non-linear in design, I simply have no knowledge of the project
domain. So it's been an extreme learning experience in several
respects.
On the plus side, there are hints of a much larger possible project,
which could cover half of my next year. That would be nice.
>Don't be too timid about using SQL. It is faster and cleaner than any
>table-related method for getting summaries.
Thanks for the gentle push on using SQL. I took a few minutes to
actually read the help file and figure out how it works, and you're
absolutely right - it's a lot cleaner to code, and so far does seem
faster.
Now starts the rather fun task of converting my increasingly
convoluted code into neat SQL oriented routines.
Thanks again!
Eric Schreiber
comments or suggestions would be appreciated.
I'm writing a financial application which does a lot of interest
calculations, and in which calculated values in the master level table
are often sums of associated record fields in the detail level table.
Following suggestions here, I've added 'workspace' copies of the
detail tables, which are what is actually used in calculations for the
master table (as has been noted, navigating and calculating with a
detail table can be frustrating). All that is going well.
However, I've found that my OnCalcField events are firing a lot. I've
added logging to each of the OnCalcField events so that I can see how
often it fires, and which record it's working on each time.
I'm rather amazed to find that the very first thing the program does
it does after opening the tables is to hit a specific OnCalcField
seven times *for the same record*!! It then goes on to do the rest of
the records, but only three times each.
Setting AutoCalcFields to False for every table in my application
makes no difference.
Naturally, this makes for a pretty sluggish program. On my Very Fast
Machine, using a local FF server, with just a few test records, it's
annoying. I fear putting it into production on a slower computer with
lots of data.
Normally I'd assume I'm screwing up somewhere, but I get the
impression from the net that OnCalcField really does fire a lot more
than seems reasonable.
I've considering moving the calculations to the AfterScroll event, but
having put a log message on that event, I'm not sure it would gain me
much. After the program goes through its initial throes of OnCalcField
events, it automatically scrolls through every record (perhaps at the
behest of my QuantumGrid components), triggering yet more unwelcome
OnCalcField events.
I'm thinking I might be best off hopping on the next south bound
freight train that goes by and spending a few years being a hobo, just
until this whole computer fad wears out.
Eric Schreiber
>I went back to your initial post from last week, and noticed that you
>don't want to use SQL for the sum from the detail table. Is there any
>reason why you are placing this restriction?
Not a valid reason, no :)
I'm not a stranger to SQL by any means. I wrote a large C/S database
program in VB using lots of SQL. But I've just never had occasion to
use it in Delphi, and the few attempts I've made (without reading any
documentation, naturally) haven't worked.
But I can see where, especially for things like summaries, SQL would
yield significant speed improvements.
>I try to always use OnCalc for a "flat" structure, or one not
>associated directly with an M/D relationship.
I'm definitely trying to get too much out of the OnCalc, no doubt. The
calculations I'm performing are a lot more processing than should be
put into that event.
>2) Create some "behind the scenes" tables. It's amazing how much
>performance one can get with this approach, and relatively simple
>tables.
I've done this in a limited way, but not to the extent you're
describing, nor with the goal of speeding up calculations. I've done
it to avoid the problems that arise when doing calcs in an active
detail table - I do them in the behind-the-scenes table using ranges,
and in the 'live' detail table I use simple lookups to this table (a
trick I learned in this newsgroup).
I'll give the summary tables you discussed some thought to see how to
apply it to my application, thanks.
Want to talk pressure? My mother-in-law will be the primary user of
this application - the company she works for hired me to replace a
huge array of spreadsheets that she currently uses, to help simplify
her rather daunting accounting tasks. I really want the end product to
be perfect for her.
Rick Brodzinsky
I went back to your initial post from last week, and noticed that you
don't want to use SQL for the sum from the detail table. Is there any
reason why you are placing this restriction? I try to always use
OnCalc for a "flat" structure, or one not associated directly with an
M/D relationship.
I've gone about similar items from you a couple of ways:
1) Use SQL queries based on the master-key field. You can key the
field change off of many of the relevant events as noted
2) Create some "behind the scenes" tables. It's amazing how much
performance one can get with this approach, and relatively simple
tables. I have a major application where I specifically keep many
summary tables (and flags to note whether or not the data should be
refreshed). These are typically items such as running balances by
account, etc. When the form which is displaying the data with the
calculated fields is first brought up, the underlying queries which
update the summary tables runs. These, too, provide a M/D
relationship, but the calcs are now done. The flags are updated to
show the data is current, and the form has a button to manually
"refresh" the calcs, if the flags change (such as a new receipt or
charge is entered, etc.)
Just some thoughts....
--
Rick Brodzinsky
http://home.earthlink.net/~rbrodzinsky
Rick Brodzinsky
<er...@kobayashi.com> wrote:
>Want to talk pressure? My mother-in-law will be the primary user of
>this application - the company she works for hired me to replace a
>huge array of spreadsheets that she currently uses, to help simplify
>her rather daunting accounting tasks. I really want the end product to
>be perfect for her.
Oh, now the truth comes out! This is a no-win situation for you <vbg>,
and like all "family" programming, you are of course thinking about it
over the holidays <vvbg>.
Sounds like the programming quagmire I walked into one day at my
temple (years ago, now), where I asked if there wasn't some better way
they could keep track of dues and payments from members. Now it is a
huge C/S app, all done by me. At least you did use the word "hired".
Don't be too timid about using SQL. It is faster and cleaner than any
table-related method for getting summaries.
--
Rick Brodzinsky
http://home.earthlink.net/~rbrodzinsky