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

_______________________________________________

Post Messages to: ProFox@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/ca48d864eef48101a623b1e0b9e67e0f@mbsoftwaresolutions.com

** 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.

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]

_______________________________________________

Post Messages to: ProFox@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/9ab07317-d4fe-6c03-cba0-3190f0d58033@optonline.net

** 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.

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.

_______________________________________________

Post Messages to: ProFox@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/cc3f66db9807fa4d1219ea6cc2cafe1e@mbsoftwaresolutions.com

** 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.

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]

_______________________________________________

Post Messages to: ProFox@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/023701d39548$1698e860$43cab920$@tpcqpc.com

** 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.

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]

_______________________________________________

Post Messages to: ProFox@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/1516822508.1275799.1246932120.5664ABD6@webmail.messagingengine.com

** 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.

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]

_______________________________________________

Post Messages to: ProFox@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/10bea5ce89d4529241c443c5d9ef1cf0@mbsoftwaresolutions.com

** 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.

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]

_______________________________________________

Post Messages to: ProFox@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/CAJidMYJAuJudu5xfDem+MJsAHRA2NOOb4Gb7=C3S60mkRgZoCA@mail.gmail.com

** 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.

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

--

Ted Roche

Ted Roche & Associates, LLC

http://www.tedroche.com

_______________________________________________

Post Messages to: ProFox@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/CACW6n4vqfjiXAq93GoLMpmA3j8LD8_1magxRJQF3gaNWnGCk7Q@mail.gmail.com

** 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.

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.

_______________________________________________

Post Messages to: ProFox@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/9b37cc5cf6ffec039f406abd2d47f127@mbsoftwaresolutions.com

** 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.

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!

_______________________________________________

Post Messages to: ProFox@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/5d9cee85aa2f46822511932265fab73e@mbsoftwaresolutions.com

** 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.