Problem: Poor multi-version SQL performance when doing queries
Maintaining SQL performance with versioned queries when having a large number of edits and long lineages.
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
Based on the frequency of new database states created during editing:
- Set the DBA to analyze the SDE.STATE_LINEAGES table. For example:
Connect as the SDE user in SQL and issue the following statement:
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.