HOW TO
A versioned query's performance (response time) can be impacted by the number of rows that must be filtered from the adds and deletes tables.
There is no set number representing the number of rows in the adds or deletes tables that indicate when a versioned query's performance is impacted. Logically, the cost of filtering rows in the delta tables during the execution of the version query impacts performance.
This article provides a reporting mechanism for the administrator to execute a report that shows how many rows are present in the DEFAULT version's lineage for a specific object class.
If the version query's performance is being impacted by the number of rows in the delta tables, the solution is to reconcile all versions with the DEFAULT version, or use the ArcObjects method recommended reconcile order to reconcile just the versions, which are preventing the DEFAULT version from being compressed, and compress the database. By allowing the rows in the delta tables to be compressed back to the object class's base table, less work must be performed by the version query when it is executed, improving performance.
Instructions provided demonstrate how to obtain a versioned table's registration_id, define local variables in SQL*Plus, set the variable values from a SQL statement, and execute a statement to return the number of rows in the delta tables for the DEFAULT version's lineage, the percentage of rows in the adds and deletes tables for the DEFAULT version's lineage, and the total number of rows in the adds and deletes table.
In the following example, the objective is to understand how many rows are present in the DEFAULT version's lineage for the feature class, Primary Overhead, delta tables.
SQL> SELECT registration_id
2 FROM sde.table_registry
3 WHERE owner = 'ADMIN' AND table_name = 'PRIMARYOH';
REGISTRATION_ID
---------------
67
SQL> VARIABLE lineage_id NUMBER;
SQL> VARIABLE lineage_name NUMBER;
SQL> BEGIN
2 SELECT state_id INTO :lineage_id FROM sde.versions WHERE owner = 'SDE' AND name = 'DEFAULT';
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> BEGIN
2 SELECT lineage_name INTO :lineage_name FROM sde.states WHERE state_id = :lineage_id;
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> SELECT
2 (SELECT COUNT(*) FROM admin.a67
3 WHERE sde_state_id IN
4 (SELECT lineage_id FROM sde.state_lineages
5 WHERE lineage_name = :lineage_name AND lineage_id <= :lineage_id)) AS "NUMBER OF ROWS",
6 ROUND((SELECT COUNT(*) FROM admin.a67
7 WHERE sde_state_id IN
8 (SELECT lineage_id FROM sde.state_lineages
9 WHERE lineage_name = :lineage_name AND lineage_id <= :lineage_id))
10 /(SELECT COUNT(*) FROM admin.a67) * 100, 2) AS "PERCENTAGE OF ROWS",
11 (SELECT COUNT(*) FROM admin.a67) AS "TOTAL ROWS"
12 FROM dual;
NUMBER OF ROWS PERCENTAGE OF ROWS TOTAL ROWS
-------------- ------------------ ----------
17413 26.78 65032
SQL> SELECT
2 (SELECT COUNT(*) FROM admin.d67
3 WHERE deleted_at IN
4 (SELECT lineage_id FROM sde.state_lineages
5 WHERE lineage_name = :lineage_name AND lineage_id <= :lineage_id)) AS "NUMBER OF ROWS",
6 ROUND((SELECT COUNT(*) FROM admin.d67
7 WHERE deleted_at IN
8 (SELECT lineage_id FROM sde.state_lineages
9 WHERE lineage_name = :lineage_name AND lineage_id <= :lineage_id))
10 /(SELECT COUNT(*) FROM admin.d67) * 100, 2) AS "PERCENTAGE OF ROWS",
11 (SELECT COUNT(*) FROM admin.d67) AS "TOTAL ROWS"
12 FROM dual;
NUMBER OF ROWS PERCENTAGE OF ROWS TOTAL ROWS
-------------- ------------------ ----------
16361 27.09 60384
Article ID: 000010102
Get help from ArcGIS experts
Download the Esri Support App