Disable Network Topology takes too long, up to 10 hours

VishApte_NGIS

Hi,

I am working with a small-to-mid size Electric Distribution utility running UNM V5 and ArcGIS Enterprise 10.9.1. We have total ~500 HV subnetworks and ~30,000 LV subnetworks.

From time to time we need to disable network topology for UNM version upgrade, update topological rule etc. Every time we try to disable network topology, it takes really long time, between 3 hours to 10 hours. ArcGIS Pro machine is barely busy during this time and so is SQL Server 2022 where the UNM model is hosted.

We tracked SQL queries and it seems to inserting records in various tables such as ElectricSubnetLine, UN_xxxxx_SUBNETWORKS etc. Example below:

(@P1 smallint,@P2 nvarchar(255),@P3 datetime2,@P4 datetime2,@P5 int,@P6 int,@P7 datetime2,@P8 int)INSERT INTO SDO.ELECTRICSUBNETLINE (OBJECTID,SUBNETWORKNAME,TIERNAME,SUBNETWORKCONTROLLERNAMES,LASTUPDATESUBNETWORK,LASTACKEXPORTSUBNETWORK,SHAPE,GLOBALID,CreatedBy,CreationDate,ISDIRTY,LastUpdatedBy,LastUpdatedDate,GDB_GEOMATTR_DATA,GDB_FROM_DATE,GDB_BRANCH_ID) SELECT OBJECTID,SUBNETWORKNAME,TIERNAME,SUBNETWORKCONTROLLERNAMES,LASTUPDATESUBNETWORK,LASTACKEXPORTSUBNETWORK,SHAPE,GLOBALID,CreatedBy,CreationDate,@P1,@P2,@P3,GDB_GEOMATTR_DATA,@P4, @P5 FROM SDO.ELECTRICSUBNETLINE WHERE GDB_ARCHIVE_OID IN (SELECT GDB_ARCHIVE_OID FROM (SELECT GDB_ARCHIVE_OID ,ROW_NUMBER() OVER (PARTITION BY OBJECTID ORDER BY GDB_FROM_DATE DESC) rn,GDB_IS_DELETE FROM SDO.ELECTRICSUBNETLINE WITH (INDEX(gdb_ct1_1183)) WHERE OBJECTID= @P6 AND ((GDB_BRANCH_ID = 0 AND GDB_FROM_DATE <= @P7) OR GDB_BRANCH_ID = @P8)) MB_ WHERE rn = 1 AND GDB_IS_DELETE = 0) OPTION (MAXDOP 1)

SQL Server instance has not shown any stress while the disable tool is running. Is the time taken for disabling network topology expected to be that long (~10 hours)? How can we expedite this?

Thanks,

Vish

JohnAlsup

Many things could cause this.

First, are you using a Service or Database Connection?

If a database connection, are you on the same subnetwork as the database? If not, this may be the cause.

If you are, then what database RDBMS is this?

It could be a RDBMS resource issue, such as disk performance, memory constrains, etc.

There have been many improvements to this process in more recent releases. It could be this issues has been addressed in current software. Not all of these changes can be back ported. If you are using a database connection, you could try using a current ArcGIS Pro client to see if this resolves the issue.

John Alsup
jalsup@esri.com

VishApte_NGIS

@JohnAlsup Thanks for your reply. Here are replies to your questions/suggestions.

First, are you using a Service or Database Connection? Direct database (SDE) connection.

If a database connection, are you on the same subnetwork as the database? If not, this may be the cause. Yes same subnet for ArcGIS Pro and SQL Server machine, both on-premise. The latency between the Pro workstation and SQL DB is virtually zero.

If you are, then what database RDBMS is this? SQL Server 2022, in Full Logging mode being a production system (we will attempt same process in a different SQL environment with logging mode Simple and see if that makes a difference)

It could be a RDBMS resource issue, such as disk performance, memory constrains, etc. Nothing indicated stress on SQL Server instance. But we could do more monitoring.

There have been many improvements to this process in more recent releases. It could be this issues has been addressed in current software. Not all of these changes can be back ported. If you are using a database connection, you could try using a current ArcGIS Pro client to see if this resolves the issue. The reason for disable network topology was for the ArcGIS Enterprise upgrade from v10.9.1 to v11.5 and UNM version upgrade from UNM v5 to UNM v7. To upgrade UNM dataset from V5 to V7, we needed to perform disable network topology. ArcGIS Pro v3.5.2 was used.

Hopefully, the issue is fixed in UNM v7. The downside of such a long disable network topology times is the outage of the system for ArcGIS Enterprise in-place upgrade blew out considerably impacting 24x7 systems that rely on network tracing etc.

VishApte_NGIS

And our subnetworks are without eventing in a default version.

GrahamWood-HWC

We’re having a very similar issue that impacts our ability to introduce changes overnight. We have about 10000 subnetworks.
I think branch versioning creates a pretty big overhead on the subnetworks too.

Thomas_Brown

Graham,

Can you please expand on your comment with specific examples of your workflow and what might be preventing you from being successful. Given the details, we may be able to either identify issues in the software or the workflow.

Thank you.

RobertKrisher

That query is happening because when you disable the network topology, we mark all the subnetworks (and subnetwork lines) as dirty. If your subnetworks are already dirty and you are seeing that query, this is a known issue that is being patched through the network management release plan. BUG-000180226.

VishApte_NGIS

@RobertKrisher When we were disabling topology, none of the subnetwork is dirty. All ~30,500 subnetworks are clean and ready.

And we were running the Disable Network topology tool using direct SQL geodatabase connection to a default version.

Thomas_Brown

Vish,

Can you please confirm what operation you were performing when you captured the SQL statement?

Looking at the predicate filter - AND ((GDB_BRANCH_ID = 0 AND GDB_FROM_DATE <= @P7) OR GDB_BRANCH_ID = @P8) implies this was being performed in a version (such as update subnetwork).

We would not expect to see these queries when disabling the network topology in a version, only when disabling in the DEFAULT version.

Thank you for any additional details.

We do have an issue in disable itself dealing with existing dirty subnetworks, it will help reduce the time and we are working to release this in the next round of Pro 3.x.x releases.

Thank you.

Tom

VishApte_NGIS

@Thomas_Brown We just executed Disable Network Topology geoprocessing tool with a single parameter of UN dataset that was using direct SQL database connection. The tool sat at 2% for 9+ hours. We were wondering if the tool is stuck and not doing anything and to confirm, we made queries in SQL Server using SSMS to find currently executing SQL statements. The query I showed above is one example. The currently executing query kept changing, which confirmed that the Disable Network Topology geoprocessing tool was doing something and not hung.