If you’ve worked with ArcGIS data for a while, you’re no doubt familiar with feature datasets. These are an ArcGIS data model construct that allow feature and relationship classes to be logically grouped together. However, for what purposes would one wish to do this?
There are a number of ArcGIS functions for which feature datasets are absolutely required. Geometric networks can only be created against a feature dataset, and every feature class that is intended to participate in the network must be in the feature dataset. The same goes for topologies, network datasets, and terrain datasets. However, some people use feature datasets simply as an organizational tool, to group together logically related feature and relationship classes (e.g. an electric dataset that contains all of the electric structures and energized assets, plus any tangentially related items…whether or not any of those items participate in geometric networks, topologies, network datasets, or terrain datasets). While this may make it somewhat easier to find certain content (using, say, ArcCatalog), is this a good idea? Our recent experience at one client indicates that it is not.
Our client had an EpochSync process running nightly to synchronize Smallworld data into an ArcGIS SDE database (SQL Server). This synchronization inserts, updates, and deletes records in ArcGIS feature classes and tables as required to maintain the data. However, with some of the feature classes and tables, the deletion times were running so long as to be prohibitive. A bit of testing showed this to not be isolated to EpochSync, as any ESRI methodology used to delete these records (e.g. arcpy, ArcObjects, ArcGIS Desktop user interface) took the same amount of time. What was happening?
To determine why these records were taking so long to delete, we decided to use SQL Server Profiler to find out what was happening on SQL Server when a delete call was made against one of these items. After getting the profile we were interested in capturing set up and ready to go, we fired up a Python command-line prompt, started an edit session, and got a handle on the table row we wished to delete. Once everything was in-place, we started the profile recording and fired off the deleteRow() call, then turned off the profiler after deleteRow() was done. The results proved that feature datasets were NOT to be used simply as an organizational tool.
What we saw in the recorded profile was schema locking of many feature classes, the majority of which seemed to be unrelated to the table from which we were deleting a record. However, one of the SQL calls we saw provided a clue as to the reason for this. It was a call that was retrieving the names of the feature classes in a feature dataset, after which all those tables were being schema-locked. Why was this happening? After all, we were deleting a record from a table, which cannot, by definition, be included in a feature dataset.
The answer was found in the following link…
The key to the whole problem is found in the last 2 points in this page, in the Locks in workgroup or enterprise geodatabases section.
- If you acquire a lock on a feature class within a feature dataset, the lock applies to the entire feature dataset and its contents.
- Locks also apply to both sides of a relationship class.
The table from which we were deleting records had a relationship to a feature class in a feature dataset. The feature dataset, while it did have a geometric network defined, was being used mostly as an organizational tool, as only about 20% of the feature classes in the feature dataset participated in the geometric network. Therefore, what was happening was this…when a request to delete a record from the table came in, a schema lock was acquired on the table, then on the feature class on the other side of the relationship (which happened to be in a feature dataset)…and then on every other item in the feature dataset. Hence, the extremely long-running delete calls.
The solution was to remove all but the feature classes used in the geometric network from the feature dataset. Once this was done, the delete performed in a timely manner.
The conclusion here is…do not use ArcGIS feature datasets simply to organize feature classes! Feature datasets should only be used if one of those ArcGIS constructs requiring a feature dataset is to be implemented (e.g. a geometric network), and then only those feature and relationship classes needed for the construct should be included in the feature dataset.