Problem: Poor multi-version SQL performance when doing versioned queries
Poor multi-version SQL performance when doing versioned queries. The following procedures are suggestions to maintain SQL performance with versioned queries.
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
Based on the frequency of edits to the mulit-versioned table during editing, the DBA will need to analyze both the add and delete tables. This can be accomplished in a number of ways.
- 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.
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 -
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.