Salesforce

"IDXFIX All" with option 2 or 3 is slower when compare to "IDXFIX by table" with option 2 or 3

« Go Back

Information

 
Title"IDXFIX All" with option 2 or 3 is slower when compare to "IDXFIX by table" with option 2 or 3
URL NameIDXFIX-All-with-option-2-or-3-is-slower-when-compare-to-IDXFIX-by-table-with-option-2-or-3
Article Number000198670
EnvironmentProduct: OpenEdge
Version: All supported versions
OS: All supported operating systems
Other: Database, IDXFIX
Question/Problem Description

Why does the IDXFIX All is slower than IDXFIX by "table" for all tables?
Why does IDXFIX All take such a long time to run?
 

Steps to Reproduce
Clarifying Information
Performance difference can be observed when running IDXFIX as mentioned below:
1. IDXFIX with options [ 3 , t , <tablenumber>, g , t , g , Y , Y , Y , ALL ,Y ] Run this for all tables one by one.
2. IDXFIX with options  [3 , v , 1, t (All Active Indexes) , g, y, y, y, all, y ] ." 

Operation1 above takes lesser time than Operation 2.

The two operations use two different paths in locking; the faster option only locks one table, the other locks all tables that are involved. So IDXFIX ALL results in a huge MTL_LKP latch and the performance difference.
 
Error Message
Defect Number
Enhancement Number
Cause
Resolution
It was determined that the Progress Product is functioning as designed.

An enhancement request has been submitted as an Idea on the Progress Community. To promote the Idea, click on this link: https://progresssoftware.aha.io/ideas/ideas/OPENEDGE-I-846. Customer feedback is valuable and Idea submissions are monitored by our Product Management team. Enhancement requests are reviewed during the planning phase of each new product release and a list of the enhancements chosen for implementation can be found in the Release Notes documents that accompany each release. Once an Idea is submitted the Progress Software Community will have the opportunity to comment on and vote for the Idea. It's priority will be evaluated as customer demand weighs in through Article Feedback and through vote count in the Ideas Portal

For detailed information on how to submit an Idea, please refer to Knowledge Base article How to submit an enhancement request for a Progress product?.

This enhancement has not been implemented in the product. As an alternative use the workaround below.
 
Workaround
Option 1:
Split the operation by using the IDXFIX by Table or by Area Options, then run these concurrently.
Option 2:
Use -NL with the IDXFIX ALL option online, but make sure no other operations are being performed on the database.
Otherwise, first run without locking: IDXFIX -NL -Bp 32 -cpinternal [db codepage]
Then verify specifically on errors from no-lock: IDXFIX -lkwtmo 10 -Bp 32 -cpinternal [db codepage]

Other Considerations:
  1. Run an IDXCOMPACT beforehand.
  2. Exclude WORD indexes where possible as these will always take a lot longer.
Notes
Keyword Phrase
Last Modified Date1/20/2021 9:10 AM

Powered by