Poor multi-version SQL performance when doing queries

Description

Maintaining SQL performance with versioned queries when having a large number of edits and long lineages.

Cause

ArcSDE analyzes the SDE.STATE_LINEAGES table when the table is created during ArcSDE installation or during an upgrade. From that point on, ArcSDE does not analyze the table; the DBA is responsible of it.

Once the database is in a production environment, the number of rows in the table can grow significantly.

If the database statistics for the table are not current and accurate, the instance's optimizer might choose a sub-optimal execution plan for fetching rows. This can have a negative affect on all version queries, because the query performs a selection from the table to detect a version's lineage.

Solution or Workaround

• Set the DBA to analyze the SDE.STATE_LINEAGES table. For example:
  
  Connect as the SDE user in SQL and issue the following statement:
  
  

  Code:
  ANALYZE TABLE state_lineages COMPUTE STATISTICS; 

  
  
• Set the DBA to create a database job that runs periodically (for example, nightly) to ensure the statistics remain current. Nightly updates are only required on tables that change frequently.