idea Trim Archive History Tool for Branch Versioned Data in Data Management Ideas

Trim Archive History Tool for Branch Versioned Data

ScottNoldy — Fri, 20 Jan 2023 01:48:22 GMT

To add another use case:

Feature class is versioned/archived/branch versioned simply to allow offline use in Collector.

Feature class is updated nightly from another database so every night, another row is added for each record to the dataset. The dataset eventually grows extremely large killing performance.

Something similar to the Trim Archived feature class would be ideal.

Re: Support the D(elete) in CRUD for Branch Versioning

premsweb — Mon, 28 Mar 2022 19:47:00 GMT

We have a similar issue with the dataset growing very large. It would be really great to be able to trim obsolete features.

Another issue is that if you are trying to use SQL queries for geometries duplicates make it difficult to query the default geometry.

Even some insight into the branch versioning geodatabase model to allow users to do this on their own would be very beneficial as an interim solution.

Re: Support the D(elete) in CRUD for Branch Versioning

DrewDowling — Thu, 15 Dec 2022 23:14:14 GMT

Another up vote for this. Worried that parcel lines in the parcel fabric will grow to tens of millions of records as we deal with fixing densified lines. A single cul-de-sac can produce hundreds of parcel lines when the build parcels tool is run. This is replicated across several parcel types. Even after the densified lines are converted to curves I'm worried that the hundreds/thousands of really short segments will stick around in perpetuity slowing queries and eating up space.

Re: Trim Archive History Tool for Branch Versioned Data - Status changed to: Under Consideration

SSWoodward — Fri, 20 Jan 2023 01:45:54 GMT

Re: Trim Archive History Tool for Branch Versioned Data

KeithMenia — Thu, 16 Mar 2023 20:10:46 GMT

I just go to this issue in our evolution. We use SQL Server as the back end DB. Traditional versioned DB starting to slow down? Just consolidate it. Worked great for the last 15 years. Branch Versioning is the only method being used by the Utility Networks and there's no way to get rid of old rows? That's totally unacceptable in my book. You can't tell me that all that edit history hanging around won't affect performance. Our DB's are growing at an exponential rate! Not to mention that any SQL drilling returns the garbage rows too (as was said above).

Pretty sure I can make a view in SSMS to find the latest record on the base table, drop the garbage, but resolving the branches is harder.

Come on ESRI, I WANT to like Pro and the direction we're going, but they sure are making it harder than it needs to be.

Re: Trim Archive History Tool for Branch Versioned Data

AshParker1 — Tue, 10 Oct 2023 09:52:55 GMT

Upvote. Cleaning up is part of maintenance.

Re: Trim Archive History Tool for Branch Versioned Data

NickN — Fri, 16 Feb 2024 21:20:08 GMT

I just contacted ESRI about this because we manage a parcel fabric that nominally has 30,000 parcels in it. Well, almost 2 years of editing and running models to update attributes used in 3rd party integrations our fabric parcel table now has 7.9 million rows in it.

Support has this logged as ENH-000137295, which just celebrated it's 3rd birthday last week 😐, to allow the Trim Archive History tool to be run on branch versioned data.

Their recommendation to work around this limitation was to unregister as versioned then delete the records using the Trim Archive History tool, then re-register. Has anyone tried this workflow?

Re: Trim Archive History Tool for Branch Versioned Data

MarcWilkes — Mon, 19 Feb 2024 08:00:02 GMT

A customer of us used the unregister and then re-register workaround to reduce rows and it worked for them. They did not use the Trim Archive History tool, though.

Re: Trim Archive History Tool for Branch Versioned Data

lah — Tue, 12 Mar 2024 13:59:11 GMT

PLEASE add this!! We just "upgraded" to branch versioning from traditional versioning, since this is the supported model for our location referencing/APR tools. We have multiple models and scripts built that automate data loading for us from various forms every night. The Derive Event Measures tool, for example, doesn't work on feature selections, so as part of our general event loading workflow it must run on the entire table. This is duplicating every single record in the event tables every night. This is not sustainable, and I agree - a tool like this is a necessary part of database maintenance.

Re: Trim Archive History Tool for Branch Versioned Data

TorrinHultgren2 — Tue, 12 Mar 2024 22:39:43 GMT

Agree that this situation is unsustainable. Why do the archive rows have to be stored in the same table? Wouldn't it be more practical all around to have the main table contain only the active rows, and have archive rows copied to a separate table? Would dramatically improve performance on the main table and make cleanup less disruptive.

Re: Trim Archive History Tool for Branch Versioned Data

dstrigl — Wed, 12 Jun 2024 12:16:30 GMT

@AshParker1

> Upvote. Cleaning up is part of maintenance.

Totally agree about!

Re: Trim Archive History Tool for Branch Versioned Data

dj — Wed, 26 Jun 2024 19:25:43 GMT

+1 from another developer concerned about database bloat.

Re: Trim Archive History Tool for Branch Versioned Data

WillBooth2 — Mon, 08 Jul 2024 01:31:47 GMT

The Utility Network implementation I help look after has only been live for about six months and the rows in the branch tables for the subnetline feature classes are already over 100 times the number you see in DEFAULT. This matters a lot as the doing spatial intersects on these are very inefficient due to the subnetwork line extents (a SQL optimiser issue), which makes their map display and using the Find Subnetworks tool incredibly slow.
Also DIRTYAREAS already has over 400,000 rows compared to the ones needing attention in DEFAULT will be well less than 100.
I can't see a useful business case for keeping history for these feature classes in particular for the life of this implementation.
Being able to periodically trim this these will be a necessity sooner rather than later.

Re: Trim Archive History Tool for Branch Versioned Data

Kevin_MacLeod — Mon, 19 Aug 2024 14:35:08 GMT

Hi folks, anyone know if this is implemented yet?

I am at a cross-roads and want to get a handle on a lot of field data coming in. We are at SDE/Enterprise 10.9.1 and updating imminently. Branched is the "future" but without simple ability to clean up I am hesitant. I see the workarounds above but I do not have time to experiment or use unsupported things that may or may not continue to work.

Esri team- any updates? Will this be officially implemented at 11.3.x or 11.4?

Re: Trim Archive History Tool for Branch Versioned Data

SSWoodward — Mon, 19 Aug 2024 15:36:16 GMT

@Kevin_MacLeod This idea is still under consideration and has not been implemented. I'll be sure to update this idea when more information is available.

Re: Trim Archive History Tool for Branch Versioned Data

BogdanHristozov — Thu, 12 Sep 2024 05:42:33 GMT

Hello,

I don't see any benefits of having the archive records stored in the base table. Over time, the amount of archive records would significantly increase. This can cause troubles in backup management decisions and performance. There was a comment about copying the archiving records into a separate table, which sounds more relevant - the archive records should be separated from the current state of edits and to be used only in designated workflows.

Re: Trim Archive History Tool for Branch Versioned Data

JeffBoykin — Wed, 30 Oct 2024 15:10:28 GMT

Upvote! We have now surpassed 42 million feature rows across 3 UN feature classes with branch versioning (PIPELINELINE, PIPELINEDEVICE, PIPLINEJUNCTION) due to data updates and we are seeing 10-30 minute reconcile times and falling behind on rec and post. We are looking at taking a backup and nuking the old records ourselves and seeing what happens. We only have about 20 million actual features.

Re: Trim Archive History Tool for Branch Versioned Data

CarlVon_Stetten1 — Fri, 17 Jan 2025 17:45:33 GMT

Upvote! Our SEWERSUBNETLINE feature class is now 1.5 GB for approximately 70 defined subnetworks.

While I understand the value of archive records (if you want to be able to do time-lapse comparisons of the data), our organization has never really needed this functionality with our utility data.

Re: Trim Archive History Tool for Branch Versioned Data

Zagorki — Tue, 21 Jan 2025 21:14:51 GMT

We'd like to see this feature/tool implemented as well. We use branch versioned data on a number of feature classes and tables unrelated to UN, Parcel Fabric, etc. Daily loads from our ETLs over the past year have turned what should be a few thousand records in one table to several million. This ETL run time went from an hour to 24 hours over this period.

The un-version and re-version option is not desirable as it removes existing views in the database and services are not happy.

We decided to run a stored procedure weekly to truncate the archival history directly in Postgres by looking at the gdb_branch_id, gdb_is_delete, last_edited_user, and gdb_from_date columns. This works for us for now but is also not preferable.

With the introduction of the Advanced Editing license being required for branch versioned data, this is enough to reconsider our use of branch versioning and return to traditional. If you only use Professional seats, this won't matter to you, but if you have 100s+ editors your costs have now more than tripled per user starting at Enterprise 11.2.

Re: Trim Archive History Tool for Branch Versioned Data

EstherSmith_Dev — Wed, 29 Jan 2025 00:32:47 GMT

This is long overdue. There should be an option/tool to purge the history older than X days/months/years.