Is there any advantage for indexing on balance rather than on principle - payments +adjustments?
When creating an index , Is there any advantage for indexing on balance rather than on principle - payments +adjustments?
Index on balance tag balanceindex on principle - payments + adjustments tag balance Right now we don't have a balance field, would there be any advance to add a balance fields?
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
Nope.
On Tue, Jun 6, 2017 at 4:35 PM, Michael Madigan mmadi10699@yahoo.com wrote:
When creating an index , Is there any advantage for indexing on balance rather than on principle - payments +adjustments?
Index on balance tag balanceindex on principle - payments + adjustments tag balance Right now we don't have a balance field, would there be any advance to add a balance fields?
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
It's not the index that is the problem, it is how you structure the query based on the index that really matters.
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@mail.leafe.com] On Behalf Of Michael Madigan Sent: 06 June 2017 21:36 To: ProFox Email List profox@leafe.com Subject: Is there any advantage for indexing on balance rather than on principle - payments +adjustments?
When creating an index , Is there any advantage for indexing on balance rather than on principle - payments +adjustments?
Index on balance tag balanceindex on principle - payments + adjustments tag balance Right now we don't have a balance field, would there be any advance to add a balance fields?
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
_______________________________________________ Post Messages to: ProFox@mail.leafe.com Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/1725843443.3868491.1496781341558@mail... ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Yes, I was just wondering since it doesn't have to do the calculation every time it updates
From: Dave Crozier DaveC@Flexipol.co.uk To: ProFox Email List profox@mail.leafe.com Sent: Wednesday, June 7, 2017 8:05 AM Subject: RE: Is there any advantage for indexing on balance rather than on principle - payments +adjustments?
It's not the index that is the problem, it is how you structure the query based on the index that really matters.
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@mail.leafe.com] On Behalf Of Michael Madigan Sent: 06 June 2017 21:36 To: ProFox Email List profox@leafe.com Subject: Is there any advantage for indexing on balance rather than on principle - payments +adjustments?
When creating an index , Is there any advantage for indexing on balance rather than on principle - payments +adjustments?
Index on balance tag balanceindex on principle - payments + adjustments tag balance Right now we don't have a balance field, would there be any advance to add a balance fields?
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
[excessive quoting removed by server]
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?
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.
... the index should be designed based on what the query will be analysing not the other way around.
Dave
-----Original Message----- From: ProFox [mailto:profox-bounces@mail.leafe.com] On Behalf Of Michael Madigan Sent: 06 June 2017 21:36 To: ProFox Email List profox@leafe.com Subject: Is there any advantage for indexing on balance rather than on principle - payments +adjustments?
When creating an index , Is there any advantage for indexing on balance rather than on principle - payments +adjustments?
Index on balance tag balanceindex on principle - payments + adjustments tag balance Right now we don't have a balance field, would there be any advance to add a balance fields?
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
_______________________________________________ Post Messages to: ProFox@mail.leafe.com Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/1725843443.3868491.1496781341558@mail... ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
-
Dave Crozier -
Michael Madigan -
Stephen Russell -
Ted Roche