Feedback on the Prune Branch History Tool

[PierreloupDucroix]

Hello,

I'd like to share my experience with the prune branch history tool.

After a year of using Utility Network in production, we've accumulated a lot of versions and branch versioning history.

I've therefore started a process to delete versions (which will be automated in the future) and prune the history.

I have several observations and questions.

First, here are some raw statistics.
Total processing time: 8 hours and 34 minutes.

table_name	records_count  before_prune	records_count  after_prune	duration_logs  postgre (min)	delete  count	delete  stats	row_per  min_ratio
un_6_eidmappings	7983639	957829	68	7025810	88%	103932
electricjunctionobject	527421	157201	20	370220	70%	18831
un_6_associations	473888	368256	14	105632	22%	7819
un_6_dirtyareas	425856	110420	6	315436	74%	49133
electricdevice	397987	211428	34	186559	47%	5502
un_6_subnetworks	385461	138946	167	246515	64%	1477
electricsubnetline	374137	138036	200	236101	63%	1183
electricline	241855	132954	3	108901	45%	40334
un_6_junctions	223761	45008	0	178753	80%	940805
structurejunction	151429	127753	1	23676	16%	20952
un_6_aggregations	148090	55346	0	92744	63%	713415
un_6_systemjunctions	137440	17326	1	120114	87%	176638
electricjunction	103885	76691	1	27194	26%	28625
un_6_edges	98835	19959	0	78876	80%	985950

1- We can see that the main table to prune is the eidmappings table, which isn't unusual, given that I've had to disable/enable the topology several times to improve the data model, which is known to increase the size of this table.

2- We can see that the time spent on certain tables compared to the number of rows deleted is very long, mainly subnetworks and subnetline. These two tables alone account for 70% of the processing time, with a very low ratio of rows processed. Is this normal?

3- It's not visible in the statistics, but the size of the database tables doesn't seem to have decreased. For example, the eidmapping table is still around 2GB after processing, just like before. Are there any additional processes that should be run on the database to reduce the allocated space (Vacuum, for example)?

4- Surprisingly, I first pruned one month of branch history (January 2025, no previous history), then one year (all of 2025), and the time spent was roughly the same (about 8 hours total, with 70% of that time spent on the two tables, subnet and subnetline).

Context : Pro 3.5.7, AGE and SDE 11.5, UN v6

[VishApte_NGIS]

Thanks @PierreloupDucroix for sharing the statistics. We are looking to prune the archived data in near future. We are contemplating whether we should only prune system tables (UN_6_*  tables) or all objects. 

We are in a similar situation and some of our tables have grown enormously since going live, even if we didn't add much new asset data. In particular UN_6_EIDMAPPINGS , SUBNETWORKS* and ElectricJunction have grown with approx. 10% of the data as current, rest as archived. We have had to disable and enable topology to fine tune our connectivity and association rules over the period. So growth in UN_6_EIDMAPPINGS and SUBNETWORKS table is understandable. And we have a process to link electric junctions representing customer connection point to Customer Information Systems that causes some continuous edits.

Cheers,

Vish

[VishApte_NGIS]

Hi @PierreloupDucroix , did the data editing, validating and network tracing workflows experience performance improvement after pruning? If so, can you please indicate the magnitude?

Cheers,

Vish

[PierreloupDucroix]

Hi, we were in the same situation, with an EID_mapping table that was growing due to changes in the UN model and other tables synchronized with a client system (this explains why electricjunctionobject is the second-largest table in my table).

Following my support ticket, the Esri team was very responsive and identified possible improvements in pruning the system tables. I don't have much more information to share, but I would recommend waiting for a patch if you're using PostgreSQL (I don't know if other DBMSs are affected). That said, if you prune at night (don't do it in your garden ;-)) or test in a staging environment, it shouldn't necessarily be a problem.

Regarding performance improvements, I'm still analyzing the potential gains, but they're offset by the upgrade to 11.5 + the database pruning + the improved attribute rules. My current opinion is that the gains in data entry will be more substantial through improved efficiency of attribute rules. Gains in terms of tracing, subnet export, etc., still need to be validated, but I will not hesitate to share my results.

[SamDeLore]

Hi @VishApte_NGIS,

In our case a prune and Index / statistics cleanup did improve tracing performance. We had traces getting timed out by our load balancer at 60 seconds and this brought them back under the timeout to around 20 seconds.

I'm sure we'll regain more performance with a disable / enable topo cycle as well as we haven't done one in over 12 months

[RobertKrisher]

@SamDeLore a >3x gain in performance from tracing is a lot more than what we would expect to see and implies that there may be something else that needs reviewed/tuning in your environment. @TomBrown ?

[SamDeLore]

Agree, it blew out probably 5x or more to get above the timeout, then roughly 3x reduction with the prune / index rebuilds. I don't think the fundamental issue was solely the archiving bloat, but either the prune and/or index work brought it back to functional to stop the timeouts in PROD.

Worth noting this was only happening with Isolation traces on very large and complex subnetworks.

I'm preparing for another round of maintenance now, I'll include a disable/enable topo this time but if there's any other recommendations then I'm keen to explore. (UN:7, 11.3 (11.5 egdb), SQL Server 2019)

Is there any significance of running Update Is Connected other than the obvious?

[VishApte_NGIS]

@SamDeLore 

In my experience, isolation trace is always slower than all other trace types, especially in water networks where the pressure system is very looping and a location in a network has many possible paths to the subnetwork controller(s). Water Network Isolation trace in a CBD area of a large city of Australia takes about 45 to 75 seconds. And this is a brand new UNM model with no history. Same trace type in a rural area takes less than 10 seconds and traces more features. It is just a nature of the trace.

All other trace types in my experience takes less than 10 seconds including Water, Sewer and Electric networks, for a reasonably speced database and server.

Cheers,

Vish

[SamDeLore]

Thanks @VishApte_NGIS. 

I'm seeing the same, we have a few very large pressure networks with loops everywhere that take around 25s. The initial upstream trace returns 94k features if run on its own, and the whole isolation trace operation traverses 308757 elements. It's feeling like the network is just big and there aren't many directional elements to rely on to easily interpret flow direction.

It was around 30s before running disable / enable topo and cleaning up the indexes. Breakdown below

Smaller networks I'm seeing from 400ms to 2s.

Step 1: Load configuration, locations, and attributes (0.007 seconds)

Step 2: Propagation [values propagated from 0 controllers] (0.000 seconds)

Step 3: Perform upstream trace before filtering, validate consistency, setting results, etc. (15.063 seconds)

Trace initial tier (15.057 seconds)

Trace additional tiers (0.000 seconds)

Calculate flow directions (14.420 seconds)

Query adjacencies (4.153 seconds)

Apply functions (0.000 seconds)

Check condition barriers (0.716 seconds)

Check function barriers (0.000 seconds)

Check propagator filters (0.000 seconds)

Check is controller (0.000 seconds)

Get asset info (0.032 seconds)

Traverse across controllers (0.027 seconds)

Skip directional traversal (0.207 seconds)

Check internal edges (0.000 seconds)

Validate controllers (0.000 seconds)

Query 'from' 'to' junctions (3.531 seconds)

Filter bitset (0.000 seconds)

Step 4: Apply filters (0.015 seconds)

Step 5: Exclude barriers (0.000 seconds)

Step 6: Get info from result types (0.000 seconds)

Step 7: Prefetch connectivity results (0.022 seconds)

Step 8: Update function results (0.000 seconds)

Step 9: Validate locatability (0.000 seconds)

Step 10: Prefetch association results (0.000 seconds)

Step 11: Include associations (0.000 seconds)

Get association info (0.000 seconds)

Get network source ID (0.000 seconds)

Check is spatial (0.000 seconds)

Step 12: K-nearest neighbors (0.000 seconds)

Step 13: Apply output filters (0.000 seconds)

Step 14: Validate consistency (9.092 seconds)

Get dirty areas (0.053 seconds)

Use heuristic - convert dirty areas to elements instead of converting elements to global IDs (0.000 seconds)

Do not use heuristic - convert trace result elements to global IDs (8.913 seconds)

Check associations (0.025 seconds)

Step 15: Convert result network elements into features and get weights (0.009 seconds)

Get global IDs and oids for elements (0.000 seconds)

Get weights for elements (0.009 seconds)

Step 16: Aggregate geometries (0.000 seconds)

Step 17: Get connectivity (0.000 seconds)

Step 18: Get feature elements (0.011 seconds)

Step 19: Get associations (0.000 seconds)

Step 20: Get network source mapping (0.000 seconds)

Step 21: Get index statistics (0.001 seconds)

Total # Traversed Elements: 308757 (including 308757 Junction/Edge Elements and 0 Associations), # Junction Result Elements: 2, # Edge Result Elements: 0 (including 0 Connectivity Associations)

Total Trace Time (24.222 seconds)

@Thomas_Brown , @RobertKrisher 

[Thomas_Brown]

Please rerun the trace and set validate consistency to false... you should see a significant improvement. 

Then you can consider... do I really need to validate consistency? By setting to false, you're essentially saying... I'm tracing the network "as is" and just want results... the fact that its a transactional system which generates dirty areas and you may or may not yet have executed the validate network topology command to incorporate those edits becomes the driving factor.

Please let us know how much faster the trace is with this option set to false.

Thank you.

[Thomas_Brown]

Sam,

20 seconds is still extremely slow (but still dependent on how many features are being traced). For 20 seconds, hundreds of thousands?

A typical subnetwork trace for an electric distribution circuit, three seconds (10-15K features). Large pressure zones (50K features maybe 10 seconds).

The only way to understand is by placing the server logs in verbose or debug mode, perform the trace and extract the instrumentation which will highlight each step of the trace operation (note this will not include the time after the trace completes and the Pro client receives the results and selects the features to display, it’s the time to solve the trace itself).
If you need further guidance I strongly recommend a support case where we can provide better support and recommendations if necessary. 
Please let us know.

Tom