Bug: Poor performance using st_geometry spatial relational operators when multiple grids are present
When executing a SQL statement with a st_geometry spatial relational operator (st_intersects, st_within, etc.) and the table with the st_spatial_index contains multiple grid levels, the query's performance is slower because of the presence of multiple grid levels.
The following example demonstrates a query using the st_geometry operator st_intersects with two tables: parcels and waterways. The spatial index on the parcels shape attribute contains multiple grid levels and is used by the optimizer as the access path.
SELECT a.owner, a.address FROM
parcels a, waterways b
WHERE b.name = 'South Fork Branch'
AND sde.st_intersects(a.shape,b.shape) = 1;
The query requires over 40 seconds to execute, but once the multiple grid levels are removed from the parcels spatial index, the query now only requires a few seconds to execute and finish.
The cause of the problem is specific to an issue introduced in ArcGIS 9.3.1 Service Pack 1, where unnecessary looping impacts performance when multiple grid levels are present to delete Oracle PL/SQL collections in the termination of sde.spx_util.grid_search_execute.
To avoid the performance degradation when executing a SQL statement that utilizes the spatial index, remove the second and third level grids, if possible.
One limiting factor that may prevent the removal of the second and third level grids is if a feature being indexed requires more then the maximum 8,000 grid cells per feature limit. If such features exist in the table, multiple grid levels must be used when creating the spatial index.
To remove the second and third level grids requires dropping the spatial index and creating the index by specifying just one single grid level value.