Poor multi-version SQL performance when doing versioned queries

Description

Poor multi-version SQL performance when doing versioned queries. The following procedures are suggestions to maintain SQL performance with versioned queries.

Cause

A multi-versioned table is analyzed when the table is first registered as versioned. From that point on ArcSDE does not analyze the table, the DBA is responsible for it.

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

If the database's 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.

Solution or Workaround

• In ArcCatalog:
  
  ¤ Use the analyze command on the feature dataset or feature classes' context menu.
  ¤ Use the ArcSDE command utility sdetable -o update_dbms_stats.
  ¤ Use the database's native SQL syntax.
  
  Examples:
  
  

  Code:
  sdetable -o update_dbms_stats -t <table_name> -u <DB_user_name> -p <DB_User_password> 

  
  ¤ Connect as the table owner in SQL and issue the following statement -
  
  

  Code:
  ANALYZE TABLE <table_name> COMPUTE STATISTICS; 

  
  
• Set the DBA to create a database job that runs nightly to ensure the statistics remain current. Nightly updates of the statistics are only required on tables that are frequently updated.