On Wed, Jun 7, 2017 at 8:24 AM, Ted Roche tedroche@gmail.com wrote:
On Wed, Jun 7, 2017 at 9:04 AM, Michael Madigan mmadi10699@yahoo.com wrote:
Yes, I was just wondering since it doesn't have to do the calculation
every time it updates
But you have to do the calculation for the denormalized balance field you're putting into the table, every time you update any records principal, payments or adjustments. And denormalization brings along other dangers.
I *think* every index expression is evaluated each time a record is updated, for that one record only. But the amount of time to do a three-element addition of the record in memory is pretty inconsequential in the big scheme of things.
As always, the only way to determine how it works in your system on your hardware with your data is to test it in that environment. Book chapters and magazine columns have been written on performance optimization, and sometimes it's not as intuitive as you'd think. Reality has a funny way of not conforming to theory. DELETED() tags always speed up queries, until they don't. My favorite J Booth, "Sometimes you have to go slower to go faster."
Is there a performance problem you're trying to solve, or was this just idle curiousity?
Indexes are great when they are used and better when the index is optimized for the use. The simple PKey or FKey is a no brainer on both need and value. Complex indexes are a different breed. Just because you created it will Rushmore think it worthy of use? An index on a calculated column is a poor design choice.
I reality VFP is probably doing a table scan and it is real fast doing that with the volume of data you currently have.