Question for you legends of the Fox (data):
Say Table1 has "X" number of indexes. Program code does something like the following:
IF Condition1 THEN REPLACE Field1 with SomeValue in Table1 ENDIF
IF Condition2 THEN REPLACE Field48 with Datetime() in Table1 ENDIF
Are each of the "X" indexes updated for each replace, even if those indexes do not include any reference to Field1 or Field48, or only indexes that are affected by the data value change?
I understand that ADDing and DELETEing records updated all indexes; I would think the UPDATEs--regardless of what fields updated--would cause index updates for every indexed field on every update as well.
tia, --Michael
Mike,
If I was to look at this from a totally Logical stand point - I would think the Indexes are ONLY update if you update a field in a record where that field is contained in one or more of the Indices.
However, Why not do a simple test - and generate your Own answer!??
Take a chunk of the data from your table in question - or even take a copy of the Whole thing, along with the Indices, drop into another folder. Then - simply do some updates based upon your criteria below - and then you can confirm Exactly what happens!
Right???
:-)
-K-
On 1/24/2018 1:29 PM, mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
Question for you legends of the Fox (data):
Say Table1 has "X" number of indexes. Program code does something like the following:
IF Condition1 THEN REPLACE Field1 with SomeValue in Table1 ENDIF
IF Condition2 THEN REPLACE Field48 with Datetime() in Table1 ENDIF
Are each of the "X" indexes updated for each replace, even if those indexes do not include any reference to Field1 or Field48, or only indexes that are affected by the data value change?
I understand that ADDing and DELETEing records updated all indexes; I would think the UPDATEs--regardless of what fields updated--would cause index updates for every indexed field on every update as well.
tia, --Michael
[excessive quoting removed by server]
On 2018-01-24 13:48, Kurt at VR-FX wrote:
Mike,
If I was to look at this from a totally Logical stand point - I would think the Indexes are ONLY update if you update a field in a record where that field is contained in one or more of the Indices.
However, Why not do a simple test - and generate your Own answer!??
Take a chunk of the data from your table in question - or even take a copy of the Whole thing, along with the Indices, drop into another folder. Then - simply do some updates based upon your criteria below - and then you can confirm Exactly what happens!
Right???
:-)
-K-
But if I did that, think of the discussion and thread that would be missed here. LOL
This question relates to VFP CDX files but is something in theory too for other systems (although I realize they may do things differently).
I just dropped a boatload of nonsense indexes from a client's legacy app database and believe that it (theoretically) should improve network traffic by lessening these unneeded updates.
It would only be missed if you didn't report your results and included a spreadsheet and a graph! :)
Paul
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of mbsoftwaresolutions@mbsoftwaresolutions.com Sent: Wednesday, January 24, 2018 12:58 PM To: profoxtech@leafe.com Subject: Re: Indexes on VFP tables--when do they get updated
On 2018-01-24 13:48, Kurt at VR-FX wrote:
Mike,
If I was to look at this from a totally Logical stand point - I would think the Indexes are ONLY update if you update a field in a record where that field is contained in one or more of the Indices.
However, Why not do a simple test - and generate your Own answer!??
Take a chunk of the data from your table in question - or even take a copy of the Whole thing, along with the Indices, drop into another folder. Then - simply do some updates based upon your criteria below - and then you can confirm Exactly what happens!
Right???
:-)
-K-
But if I did that, think of the discussion and thread that would be missed here. LOL
This question relates to VFP CDX files but is something in theory too for other systems (although I realize they may do things differently).
I just dropped a boatload of nonsense indexes from a client's legacy app database and believe that it (theoretically) should improve network traffic by lessening these unneeded updates.
[excessive quoting removed by server]
Only the indexes that involve a 'touched' field. And even then it's just a small update.
Thanks. In this app, they indexed EVERY field, and the table in question had a CDX size of 233 MB before I pruned it. Now it's down to 56 MB.
On 2018-01-24 14:35, Alan Bourke wrote:
Only the indexes that involve a 'touched' field. And even then it's just a small update.
-- Alan Bourke alanpbourke (at) fastmail (dot) fm
On Wed, 24 Jan 2018, at 6:29 PM, mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
Question for you legends of the Fox (data):
Say Table1 has "X" number of indexes. Program code does something like the following:
IF Condition1 THEN REPLACE Field1 with SomeValue in Table1 ENDIF
IF Condition2 THEN REPLACE Field48 with Datetime() in Table1 ENDIF
Are each of the "X" indexes updated for each replace, even if those indexes do not include any reference to Field1 or Field48, or only indexes that are affected by the data value change?
I understand that ADDing and DELETEing records updated all indexes; I would think the UPDATEs--regardless of what fields updated--would cause index updates for every indexed field on every update as well.
tia, --Michael
[excessive quoting removed by server]
I expect the update of .cdx to be at the same time that the .dbf is done.
223 meg is a tiny index size and reducing it down to 1/4 probably didn't make anything faster, did it? Now an index of 20 gig that would probably be noticed if you brought it in line to only 5 gig.
On Wed, Jan 24, 2018 at 2:11 PM, < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
Thanks. In this app, they indexed EVERY field, and the table in question had a CDX size of 233 MB before I pruned it. Now it's down to 56 MB.
On 2018-01-24 14:35, Alan Bourke wrote:
Only the indexes that involve a 'touched' field. And even then it's just a small update.
-- Alan Bourke alanpbourke (at) fastmail (dot) fm
On Wed, 24 Jan 2018, at 6:29 PM, mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
Question for you legends of the Fox (data):
Say Table1 has "X" number of indexes. Program code does something like the following:
IF Condition1 THEN REPLACE Field1 with SomeValue in Table1 ENDIF
IF Condition2 THEN REPLACE Field48 with Datetime() in Table1 ENDIF
Are each of the "X" indexes updated for each replace, even if those indexes do not include any reference to Field1 or Field48, or only indexes that are affected by the data value change?
I understand that ADDing and DELETEing records updated all indexes; I would think the UPDATEs--regardless of what fields updated--would cause index updates for every indexed field on every update as well.
tia, --Michael
[excessive quoting removed by server]
On 2018-01-24 15:19, Stephen Russell wrote:
I expect the update of .cdx to be at the same time that the .dbf is done.
223 meg is a tiny index size and reducing it down to 1/4 probably didn't make anything faster, did it? Now an index of 20 gig that would probably be noticed if you brought it in line to only 5 gig.
VFP files can't be more than 2 GB, Stephen.
I thought that the dbf had that limit. What if you had 25 indexes on a 50 column table. You would probably blow out the .cdx space before the dbf.
On Wed, Jan 24, 2018 at 4:53 PM, < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
On 2018-01-24 15:19, Stephen Russell wrote:
I expect the update of .cdx to be at the same time that the .dbf is done.
223 meg is a tiny index size and reducing it down to 1/4 probably didn't make anything faster, did it? Now an index of 20 gig that would probably be noticed if you brought it in line to only 5 gig.
VFP files can't be more than 2 GB, Stephen.
[excessive quoting removed by server]
Forgive Stephen - as he now plays with the Big Boys like M$ SQL!
:-)
-K-
On 1/24/2018 5:53 PM, mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
On 2018-01-24 15:19, Stephen Russell wrote:
I expect the update of .cdx to be at the same time that the .dbf is done.
223 meg is a tiny index size and reducing it down to 1/4 probably didn't make anything faster, did it? Now an index of 20 gig that would probably be noticed if you brought it in line to only 5 gig.
VFP files can't be more than 2 GB, Stephen.
[excessive quoting removed by server]
I have some indexes larger than your last hard drive. :) Table Name # Records Reserved (KB) Data (KB) Indexes (KB) Unused (KB) dbo.ttfgld495600 95,953,505 251,547,152 138,105,072 111,772,128 1,669,952
dbo.ttfgld482600 105,142,512 146,331,720 100,144,320 45,854,776 332,624
dbo.ttfgld465600 100,724,986 135,428,712 134,551,896 859,840 16,976 dbo.ttfgld106600 85,175,413 78,139,496 46,399,096 31,710,944 29,456
On Wed, Jan 24, 2018 at 10:25 PM, Kurt at VR-FX vrfx@optonline.net wrote:
Forgive Stephen - as he now plays with the Big Boys like M$ SQL!
:-)
-K-
On 1/24/2018 5:53 PM, mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
On 2018-01-24 15:19, Stephen Russell wrote:
I expect the update of .cdx to be at the same time that the .dbf is done.
223 meg is a tiny index size and reducing it down to 1/4 probably didn't make anything faster, did it? Now an index of 20 gig that would probably be noticed if you brought it in line to only 5 gig.
VFP files can't be more than 2 GB, Stephen.
[excessive quoting removed by server]
First off, don't do that. REPLACE (or better, UPDATE) once. Especially in high network traffic situations, the amount of time it takes to assemble a field and value list to issue one:
UPDATE TableName SET &lcSetFieldsToValues WHERE FilterCriteria
saves an enormous amount of I/O and CPU cycles: one lock, one transaction, one row re-write, one set of reindexes.
Second, in VFP, all indexes have to be evaluated, as there's no backlink to which fields are used in which expressions. Indexes are defined at the table level, not the row, so can have multiple field names, functions (UPPER() or DELETED(), for example), concatentations or just random stuff.
<OldWarStory> I once worked on an app where the original developer thought it would be a "clever" idea to define index expressions as UDFs. Yes, it's possible. In his UDFs, he would switch work areas, open tables if not already opened, look up values, and then return the value, cleaning up work areas and tables as he went. For every index definition for every record. A reindex with more than a couple hundred records brought the entire system to its knees. </OldWarStory>
On Wed, Jan 24, 2018 at 1:29 PM, mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
I understand that ADDing and DELETEing records updated all indexes; I would think the UPDATEs--regardless of what fields updated--would cause index updates for every indexed field on every update as well.
I don't know if it actually caused *UPDATES* but I think VFP would have to go through each index expression and test to see if the value had changed. Perhaps they'd skip the write if it was unchanged.
Other database engines do this differently
On 2018-01-24 15:21, Ted Roche wrote:
First off, don't do that. REPLACE (or better, UPDATE) once. Especially in high network traffic situations, the amount of time it takes to assemble a field and value list to issue one:
UPDATE TableName SET &lcSetFieldsToValues WHERE FilterCriteria
saves an enormous amount of I/O and CPU cycles: one lock, one transaction, one row re-write, one set of reindexes.
Yes, I retooled some code to replace (pun not intended) a whole bunch of REPLACE commands in the same area with a SCATTER to object, do the voodoo, then just ONE REPLACE afterwards for that very purpose. I specifically listed that "multiple conditions" example thinking if I didn't, someone would say as you did to avoid the multiple REPLACEs. :-)
Second, in VFP, all indexes have to be evaluated, as there's no backlink to which fields are used in which expressions. Indexes are defined at the table level, not the row, so can have multiple field names, functions (UPPER() or DELETED(), for example), concatentations or just random stuff.
So you're contending that ALL of the indexes--not just those affected by the field that was changed--would have to be reevaluated?
<OldWarStory> I once worked on an app where the original developer thought it would be a "clever" idea to define index expressions as UDFs. Yes, it's possible. In his UDFs, he would switch work areas, open tables if not already opened, look up values, and then return the value, cleaning up work areas and tables as he went. For every index definition for every record. A reindex with more than a couple hundred records brought the entire system to its knees. </OldWarStory>
YIKES!!!! Thanks for sharing that one!
When the index is evaluated initially, VFP knows exactly what fields affect what index, so really just affected indexes are updated.
It's easy to test it. just make an old index (IDX) on two different fields:
CREATE TABLE test (field1 C(10), field2 I) INDEX on field1 TO test_f1 additive INDEX on field2 TO test_f2 additive
Add some data, wait a minute, replace one of the fields and do a FLUSH FORCE, and you will see that only the affected index changes his timestamp.
2018-01-24 23:57 GMT+01:00 mbsoftwaresolutions@mbsoftwaresolutions.com:
On 2018-01-24 15:21, Ted Roche wrote:
First off, don't do that. REPLACE (or better, UPDATE) once. Especially in high network traffic situations, the amount of time it takes to assemble a field and value list to issue one:
UPDATE TableName SET &lcSetFieldsToValues WHERE FilterCriteria
saves an enormous amount of I/O and CPU cycles: one lock, one transaction, one row re-write, one set of reindexes.
Yes, I retooled some code to replace (pun not intended) a whole bunch of REPLACE commands in the same area with a SCATTER to object, do the voodoo, then just ONE REPLACE afterwards for that very purpose. I specifically listed that "multiple conditions" example thinking if I didn't, someone would say as you did to avoid the multiple REPLACEs. :-)
Second, in VFP, all indexes have to be evaluated, as there's no backlink to which fields are used in which expressions. Indexes are defined at the table level, not the row, so can have multiple field names, functions (UPPER() or DELETED(), for example), concatentations or just random stuff.
So you're contending that ALL of the indexes--not just those affected by the field that was changed--would have to be reevaluated?
<OldWarStory> I once worked on an app where the original developer thought it would be a "clever" idea to define index expressions as UDFs. Yes, it's possible. In his UDFs, he would switch work areas, open tables if not already opened, look up values, and then return the value, cleaning up work areas and tables as he went. For every index definition for every record. A reindex with more than a couple hundred records brought the entire system to its knees. </OldWarStory>
YIKES!!!! Thanks for sharing that one!
[excessive quoting removed by server]
On 2018-01-24 18:39, Fernando D. Bozzo wrote:
When the index is evaluated initially, VFP knows exactly what fields affect what index, so really just affected indexes are updated.
It's easy to test it. just make an old index (IDX) on two different fields:
CREATE TABLE test (field1 C(10), field2 I) INDEX on field1 TO test_f1 additive INDEX on field2 TO test_f2 additive
Add some data, wait a minute, replace one of the fields and do a FLUSH FORCE, and you will see that only the affected index changes his timestamp.
I don't think that's a fair test to use IDX files since this example uses a CDX file, and it's all in the same file.
IN theory - the same test should be viable - just see if the CDX Timestamp was updated!
On 1/25/2018 11:15 AM, mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
On 2018-01-24 18:39, Fernando D. Bozzo wrote:
When the index is evaluated initially, VFP knows exactly what fields affect what index, so really just affected indexes are updated.
It's easy to test it. just make an old index (IDX) on two different fields:
CREATE TABLE test (field1 C(10), field2 I) INDEX on field1 TO test_f1 additive INDEX on field2 TO test_f2 additive
Add some data, wait a minute, replace one of the fields and do a FLUSH FORCE, and you will see that only the affected index changes his timestamp.
I don't think that's a fair test to use IDX files since this example uses a CDX file, and it's all in the same file.
[excessive quoting removed by server]
On 2018-01-25 13:24, Kurt at VR-FX wrote:
IN theory - the same test should be viable - just see if the CDX Timestamp was updated!
Yes, it would be, but my point is that the entire CDX would come over the line, if I understand correctly. The entire DBF doesn't come over the wire (network), but the CDX does?
-
Alan Bourke -
Fernando D. Bozzo -
Kurt at VR-FX -
mbsoftwaresolutions＠mbsoftwaresolutions.com -
Paul H. Tarver -
Stephen Russell -
Ted Roche