Archive classes do not utilize spatial indexes.

Anonymous User — Sat, 11 Dec 2021 19:53:08 GMT

Hello all,

Would anyone know why spatial indexes are not being utilized when I load an archive feature class?

I am using ArcGIS Desktop/Server 10.2.1, I have a PostgreSQL 9.2 instance hosting a 10.2.1 enterprise geodatabase.

In my database, I have some relatively large feature classes (millions of records per feature class). When I load the default view of a versioned feature class into ArcMap, and zoom in to a small region on the map, the features render very fast. With the Geodatabase History toolbar, I can then add the archive class to the map for the same feature class, the same spatial extent will take a very long time to refresh.

I have logged the statements being issued to the underlying database, and it seems pretty clear to me that the entire table is being scanned every time ArcMap refreshes the layer.

Here's what the database log looks like when the default view is refreshed:

2014-01-24 14:39:42 EST LOG:  execute sde_1390592382_0_4402: SELECT table_name, time_last_modified FROM sde.sde_tables_modified
2014-01-24 14:39:42 EST LOG:  statement: DEALLOCATE sde_1390592382_0_4402
2014-01-24 14:39:42 EST LOG:  statement: DEALLOCATE sde_1390592382_0_4404
2014-01-24 14:39:42 EST LOG:  execute sde_1390592382_0_4404: DECLARE sdecur_4404_16241 BINARY CURSOR WITH HOLD FOR  select  V__32.shape,  objectid,  representation,  override  from ( {very_long_sql_statement} ) V__32  where  ((V__32.shape && $7) = 't') 
2014-01-24 14:39:42 EST DETAIL:  parameters: $1 = '18029', $2 = '18047', $3 = '18029', $4 = '18047', $5 = '18029', $6 = '18047', $7 = '380000000500000008001000E610000020000000000000009BA3A78092108FECAD96921A9AEA98020000A9869C01DAEA98020000E9869C01'
2014-01-24 14:39:42 EST LOG:  statement: FETCH FORWARD 100 from sdecur_4404_16241
2014-01-24 14:39:42 EST LOG:  statement: FETCH FORWARD 1000 from sdecur_4404_16241
2014-01-24 14:39:42 EST LOG:  statement: CLOSE sdecur_4404_16241
2014-01-24 14:39:42 EST LOG:  statement: DEALLOCATE sde_1390592382_0_4404

It is easy to spot the geometry being passed into the statement as parameter '$7', which I believe is the map extent being used to limit the query results to just include features that should be currently visible in the display.

Now, when the archive class is refreshed at the same map extent, the logged statements look like this:

2014-01-24 14:38:31 EST LOG:  execute sde_1390592311_0_4394: SELECT table_name, time_last_modified FROM sde.sde_tables_modified
2014-01-24 14:38:31 EST LOG:  statement: DEALLOCATE sde_1390592311_0_4394
2014-01-24 14:38:31 EST LOG:  statement: DEALLOCATE sde_1390592311_0_4396
2014-01-24 14:38:31 EST LOG:  execute sde_1390592311_0_4396: DECLARE sdecur_4396_16241 BINARY CURSOR WITH HOLD FOR  select  buildings_h.shape  from  test.SDE.BUILDINGS_H  
2014-01-24 14:38:31 EST LOG:  statement: FETCH FORWARD 100 from sdecur_4396_16241
2014-01-24 14:38:31 EST LOG:  statement: FETCH FORWARD 1000 from sdecur_4396_16241
... many more 'fetch forward' statements ...
2014-01-24 14:38:43 EST LOG:  statement: FETCH FORWARD 1000 from sdecur_4396_16241
2014-01-24 14:38:43 EST LOG:  statement: CLOSE sdecur_4396_16241
2014-01-24 14:38:43 EST LOG:  statement: DEALLOCATE sde_1390592311_0_4396
2014-01-24 14:38:43 EST LOG:  statement: COMMIT
2014-01-24 14:38:43 EST LOG:  statement: BEGIN
2014-01-24 14:38:43 EST LOG:  statement: COMMIT
2014-01-24 14:38:43 EST LOG:  statement: BEGIN

If I add up all of the 'fetch forward' statements (1000 records each), the total is equivalent to the number of records in the table. I'm not sure if maybe I've done something wrong, but I definitely don't want this to be happening...it takes about 10-15 seconds just for the one feature class in this case, every time the map refreshes.

I have found a workaround. I created a database view of the archive feature class (with a simple definition specified as 'select * from tablename_h'). When I load that view into ArcMap, and answer the prompt for the objectid field (for which I pick gdb_archive_oid), it appears ArcMap will use a spatial extent to limit features when it queries the view:

2014-01-24 15:16:19 EST LOG:  execute sde_1390594579_0_17731: SELECT lineage_name, time_last_modified FROM sde.sde_lineages_modified WHERE lineage_name = $1
2014-01-24 15:16:19 EST DETAIL:  parameters: $1 = '-1'
2014-01-24 15:16:19 EST LOG:  statement: DEALLOCATE sde_1390594579_0_17731
2014-01-24 15:16:19 EST LOG:  statement: SAVEPOINT sp_sde_1390594579_0_17730
2014-01-24 15:16:19 EST LOG:  execute sde_1390594579_0_17730: DECLARE sdecur_17730_16241 BINARY CURSOR WITH HOLD FOR  select shape from ( {very_long_sql_statement}) a where (shape && sde.ST_GeomFromWKB($1,$2)) = 't'
2014-01-24 15:16:19 EST DETAIL:  parameters: $1 = '\x0103000000010000000500000064bd625724ad5ec0d85e3b20e28e484064bd625724ad5ec0c06768ccfc8e4840309eba520cad5ec0c06768ccfc8e4840309eba520cad5ec0d85e3b20e28e484064bd625724ad5ec0d85e3b20e28e4840', $2 = '4326'
2014-01-24 15:16:19 EST LOG:  statement: FETCH FORWARD 100 from sdecur_17730_16241
2014-01-24 15:16:19 EST LOG:  statement: RELEASE SAVEPOINT sp_sde_1390594579_0_17730
2014-01-24 15:16:19 EST LOG:  statement: FETCH FORWARD 1000 from sdecur_17730_16241
2014-01-24 15:16:19 EST LOG:  statement: CLOSE sdecur_17730_16241
2014-01-24 15:16:19 EST LOG:  statement: DEALLOCATE sde_1390594579_0_17730

At this point, I have two questions. First, is this a known/expected behaviour of ArcGIS when working with archive classes, or might it be considered a bug? Second, is there any inherent problem with the workaround of using a view?

topic Archive classes do not utilize spatial indexes. in Data Management Questions

Archive classes do not utilize spatial indexes.