SDE Performance Degradation Using Topology with Large Datasets

[CoryBowlin]

Currently ESRI'S SDE geodatabase has performance degradation issues when adding, deleting, or making attribution changes on features when the SDE utilizes a Topology on a large dataset (example: 30,000 polygons representing land parcels and five rules involving three Feature Classes).   According to ESRI, this slowness should be expected on SQL Server, Oracle, and PostgreSQL when a topology feature class is used. The database is performing checks on "dirty areas" when edits are made causing the process to be slow.

I suggest making a modification to the enterprise geodatabase design to handle edits in a way that does not degrade speed while using toplogies or add the Disable Network Topology functionality to non-network topologies. A enhancement request has been logged with ESRI for such a tool:  ENH-000122376    

[CoryBowlin]

Temporarly turning off the topolgy rules should do it. Unfortunately with the environment that I had this sde deployed in, it would cause the sde replication to break if you modify the topolgy rules. So turning them off wasn't an option in my situation. 

Another idea I had, even with the replication, was to deploy the replication using sde binary storage type. One problem with this possibility is sde binary is deprecated in pro. I never got to test this theory but several clues led me to believe it may work. I did try other storage types that did not make a difference. 

[CoryBowlin]

I just tried using the data in an SDE Binary storage type, and it made no difference. 

[CoryBowlin]

I just tried using the data in an SDE Binary storage type, and it made no difference. 

[JoshMcCracken]

This became such a drag on performance that we had to escalate the request to premium support, and they were able to find the resolution for us a couple of months ago.  What worked for us was in SQL Server under database properties for the enterprise geodatabase, we had to change two properties under Options.  Within the Miscellaneous heading there are the following properties:

Allow Snapshot Isolation

Is Read Committed Snapshot On

It appears that both of these options default to False.  The support analyst changed them to true and tested the workflow successfully.  We were able to confirm that these settings were in fact the issue, at least for our environment.

[JoshMcCracken]

This became such a drag on performance that we had to escalate the request to premium support, and they were able to find the resolution for us a couple of months ago.  What worked for us was in SQL Server under database properties for the enterprise geodatabase, we had to change two properties under Options.  Within the Miscellaneous heading there are the following properties:

Allow Snapshot Isolation

Is Read Committed Snapshot On

It appears that both of these options default to False.  The support analyst changed them to true and tested the workflow successfully.  We were able to confirm that these settings were in fact the issue, at least for our environment.