Content Consistency Checker date validation range performance tuning

Content

In the case where a content consistency check is performed by using either a "Validate by Date Created" or "Validate by Date Modified" validation range, improvements in query performance might be achieved. Improvements are possible by appending an "Order by..." statement to the Consistency Checker SQL statement, and by adding database indexes to the DocVersion and Annotation tables. The amount of improvement can vary, depending on the data.

The AppendOrderBy preference was added in Content Platform Engine 5.2.1.2 for the purpose of appending the required "Order by DateCreated" or "Order by DateLastModified" (depending on the type of validation range selected) to the Consistency Checker SQL statement. Do not set this preference unless the required database indexes are also added to the DocVersion and Annotation tables.

Follow the steps below to set or clear the AppendOrderBy preference:

1. Run the Content Consistency Checker tool.
1. Open the Options window (File > Options).
   From the Report file location field, get the location of the ./plugins run-time directory, for example:
   C:/Users/Administrator/Desktop/workspace/.metadata/.plugins/com.ibm.filenet.ecm.consistencychecker
2. Use Windows Explorer to navigate to the org.eclipse.core.runtime/.settings subdirectory of the .plugins directory, for example:
   C:/Users/Administrator/Desktop/workspace/.metadata/.plugins/org.eclipse.core.runtime/.settings
3. Save a copy of the com.ibm.filenet.ecm.consistencychecker.prefs file that is contained in the .settings directory.
4. Modify the com.ibm.filenet.ecm.consistencychecker.prefs file.
   Add an AppendOrderBy=1 name and value property pair and save the file.
5. Exit the Content Consistency Checker tool.
2. Add indexes to the DocVersion and Annotation tables.
• For ".... Order by DateCreated":
  create index I_Comp1 on schema.DocVersion ( storage_area_id, create_date, object_id)
  create index I_Comp1 on schema.Annotation ( storage_area_id, create_date, object_id)
• For ".... Order by DateLastModified":
  create index I_Comp1 on schema.DocVersion ( storage_area_id, modify_date, object_id)
  create index I_Comp1 on schema.Annotation ( storage_area_id, modify_date, object_id)
For information about additional query indexes, see Improving queries that return more than a million rows.