Is Index important for record update?

skevin.john

New Member
Hello All,

OE 11.2

We have a cron job that runs frequently and updates 2 fields in a table everytime and these 2 fields are part of 4 indexes of that table. My question, how progress works with this scenario? Will it pull all 4 indexes everytime? IS there any performance benefit because of it?

Please suggest.

Thanks.
 

TomBascom

Curmudgeon
Index updates do have a performance cost. But it generally more than offset by the benefits.

Indexes that are duplicates of other indexes can probably be eliminated to speed up updates and save disk space if they truly are not needed but the more usual problem is missing indexes that would better support your queries.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
We have a cron job that runs frequently and updates 2 fields in a table everytime and these 2 fields are part of 4 indexes of that table. My question, how progress works with this scenario?
You have said that the cron job updates two fields but it is not clear how many records are being updated. If it is just one record, Progress does something like the following, at a high level:
  • Reads an index to find the location of the record.
  • Reads that record.
  • Starts a transaction (this may be earlier, depending on how the code is written).
  • Updates the record with the two new field values.
  • Finds the entry in Index 1 for this record.
  • Deletes that entry.
  • Creates a new entry with the new field values.
  • Repeats the previous three steps for Indexes 2, 3, and 4.
  • Ends the transaction (again, depending on your code).

Will it pull all 4 indexes everytime?
I don't know what you mean by "pull all 4 indexes".
 

andre42

Member
Most important points have been made (searching the records is faster with a matching index, changing records is slower the more indexes include the fields you are changing).
One important point is to change both fields in one go within the same assign statement. If you are just doing
Code:
customer.indexfield1 = newvalue1.
customer.indexfield2 = newvalue2.
than Progress needs to update the indexes twice.
 

TomBascom

Curmudgeon
That's a really good point. It is generally a good idea to combine multiple consecutive assignments into a single ASSIGN but it is especially important to do so when indexed fields are involved. I like to write such statements like this:

Code:
assign
  customer.indexfield1 = newvalue1     /* watch out - a stray "." can silently defeat your efforts */
  customer.indexfield2 = newvalue2
.
It makes it very easy to see all of the sub-assignments. Putting the terminating "." on its own line aligned with the ASSIGN makes it easy to avoid future changes accidentally introducing stray periods.
 
Top