Improving Content Consistency Checker queries

You can tune your Content Consistency Checker queries to improve performance.

About this task

Consider tuning Content Consistency Checker queries that return a million or more records and use a date range.

This information applies to the Content Consistency Checker tool, and not to the Consistency Check sweep job.

Improving Content Consistency Checker queries that return more than a million rows

Consider creating an index if the queries that are associated with Content Consistency Checker document re-indexing by document class return a million or more records.

Procedure

Create the index by using the following SQL command: 
create index I_STORAGE_OBJID on docversion (storage_area_id, object_id)

To improve the performance of document insertions and document updates, as well as reduce storage costs, you can remove this index after you have completed the associated tasks. However, if the operations are performed on a regular basis, you might want to keep the index to avoid having to create it more than once.

Improving Content Consistency Checker queries that use a date range

Content consistency checks that are run with a date range selected can be optimized for better query performance by adding indexes to the DocVersion and Annotation database tables.

About this task

You can improve query performance for content consistency checks that are run by using a Validate by Date Created or Validate by Date Modified validation range. To improve query performance, you append an Order by statement to the Consistency Checker SQL statement and add database indexes to the DocVersion and Annotation tables. The amount of improvement can vary, depending on the data.

You must be running Content Platform Engine 5.2.1.2-P8CPE-FP002 or later to use this option.

Important: Do not set the AppendOrderBy option without also creating the related indexes.

Procedure

To tune date validation for Content Consistency Checker:

  1. Set the preference to add an Order by statement to the Consistency Checker SQL statement:
    1. In Content Consistency Checker, click File > Options.
    2. In the Report file location field, get the location of the ./plugins runtime directory.
      For example, C:/Users/Administrator/Desktop/workspace/.metadata/.plugins/com.ibm.filenet.ecm.consistencychecker.
    3. Save the file location and exit Content Consistency Checker.
    4. Use Windows Explorer to go 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.
    5. Save a copy of the com.ibm.filenet.ecm.consistencychecker.prefs file that is in the .settings directory.
    6. Add the following line to the com.ibm.filenet.ecm.consistencychecker.prefs file: AppendOrderBy=1.
  2. Add database indexes to the DocVersion and Annotation tables.
    1. Connect to the object store database by using the database tool that is supplied with your database.
    2. Run the following commands for the OrderByDateCreated statement:
      • DocVersion table: create index I_Comp1 on schema.DocVersion ( storage_area_id, create_date, object_id)
      • Annotation table: create index I_Comp1 on schema.Annotation ( storage_area_id, create_date, object_id)
    3. Run the following commands for the OrderByDateLastModified statement:
      • DocVersion table: create index I_Comp1 on schema.DocVersion ( storage_area_id, modify_date, object_id)
      • Annotation table: create index I_Comp1 on schema.Annotation ( storage_area_id, modify_date, object_id)