Spatial Index Management behind the scenes with DeleteFeatures and Append

1081
2
Jump to solution
01-18-2021 06:00 AM
RobertHooley
New Contributor II

We have a SQL Server Enterprise Geodatabase on version 10.4.1, which uses the SQL Geometry storage type.

We have some python tools which run overnight and use the DeleteFeatures and Append tools to refresh some Feature Classes with change only updates. The tools use Layer objects (arcpy.MakeFeatureLayer_management) and selection sets to only delete/append a few features at once.

The scripts were written in ArcMap/Python2.7, and it was noticed that behind the scenes the DeleteFeatures and Append tools were running DROP INDEX and CREATE INDEX statements behind the scenes, this is related to the MaintainSpatialIndex environment variable. We do not want this behavior as it is affecting DB performance. 

Maintain Spatial Index (Environment setting)—Help | ArcGIS for Desktop

To try and mitigate this the scripts were tweaked to run from ArcGIS Pro/Python 3, because it seems like the Pro/Python 3 DeleteFeatures and Append tools do not try and Drop/Create Indexes behind the scenes, but recently we have noticed some Spatial Indexes got dropped from the database.

Maintain Spatial Index (Environment setting)—ArcGIS Pro | Documentation

Has anyone else encountered such problems with DeleteFeatures and Append affecting Spatial Indexes adversely in SQL Server?

Am I right in thinking that ArcGIS Pro DeleteFeatures and Append tools do not try and Drop/Create Indexes behind the scenes?

0 Kudos
1 Solution

Accepted Solutions
DavidBlanchard
Esri Contributor

I have not encountered the issue with ArcGIS Pro/Python 3.x and spatial indexes. But when I encountered this issues with ArcMap/Python 2.7, the solution was to use a database user who didn't have permission to modify the indexes (e.g. data_writer). You could try the same approach and see if the performance improves when using ArcGIS Pro/Python 3.x.

View solution in original post

2 Replies
DavidBlanchard
Esri Contributor

I have not encountered the issue with ArcGIS Pro/Python 3.x and spatial indexes. But when I encountered this issues with ArcMap/Python 2.7, the solution was to use a database user who didn't have permission to modify the indexes (e.g. data_writer). You could try the same approach and see if the performance improves when using ArcGIS Pro/Python 3.x.

RobertHooley
New Contributor II

Thanks David, using an Operating System auth account with no DDL permissions seems faster and has not caused a blocking CREATE/DROP INDEX statement in the DB. Evidence suggests that the ArcGIS Pro version of arcpy.Append with an account which has the necessary privileges is still running these CREATE/DROP INDEX statements behind the scenes, more investigation needed to know for sure.

0 Kudos