ArcPro-Roads and Highways Event: What is best way to Field Calculate on an existing event with large number of records

[ElsitMandal1]

Hello,

Wondering if anyone able to do "Calculate Field" on an existing event with many rows( branch versioned feature service , conflict prevention enabled --about 400,000+ records ), by using some method that wouldn't take too long to complete? 

Appreciate if anyone can share any  experience with this kind of issues and what worked out for them to accomplish the task.

Background Info:

Recently we migrated to ArcPro 3.3 ( Pro 3.3.1, Server 11.3) Roads and Highways Branch versioned/conflict prevention enabled, feature service editing. After that we are not able to use field calculate in an existing event  in a timely manner ( takes too long even if successful; otherwise due to unknown/ambiguous reasons 99999 error pops up-possibly locks not retained because of long transaction pr default version changed since the start of edit) 

The only workaround that seem to accomplish bulk data update so far is by taking a copy of existing data into a filegeodatabase; delete existing records (about 400,000)  in the branch version and then append the processed records back in. We need to repeat this process, whenever we have data ready to update for other  fields in the event.

Thanks

[AyanPalit]

@ElsitMandal1 

Few questions:

1. What data attributes need this field calculate - LRS fields, non-LRS or a mix of both?
2. What is the frequency of such updates - bulk calculate with large record count (+100K)?
3. Is this field calculate performed by a single admin type user? Or is it for all LRS editors?

[ElsitMandal1]

1. non-LRS fields (Business data fields updates)

2. Consider once a month

3. LRS editors ( ArcPro desktop editors)

[RyanKoschatzky · Accepted solution]

A few strategies we have used, while not at Pro we will still most likely do a scheduled quarterly downtime. In traditional versioning we went to state zero but when we also would make database changes and take a data cut. That would be time period we could schedule such work.

Another method, with 7 lrs editors and 15 event editors in the system there can be lots of different locks in the system. We would break down the work by smaller boundary area where people are not working like a county or a collection of counties.  That can handle 95% or more of the work effort. Easy enough to have the business unit get out of their own way for an event update. Our lrs editors are posting every few days when complete with work and easy enough to work around a few exclude routes and updating the rest of county until a route lock is removed or a little coordination on needing a few minutes to update has work effectively in the past.

Instead of allowing the tool to grab a lock to edit, I would grab a route lock or an event lock at minimum first than process the action on that same selection. That might get around the 99999 issues.

If updating monthly, I would think about pulling out that field out of that feature class to another feature class to make doing append events a simpler effort. You can do the work outside R&H and add later. Just a thought. I am sure there is some other creative solutions to update data changing that often. 

[ElsitMandal1]

Thank you for the suggestions, Ryan.  We are now in the process of trying to coordinate with various editors about timing of posting to default.  

If I understand correctly what you are saying, is it possible to grab event lock for a specific event, how do we accomplish that. My understanding is that in ArcPro we cannot acquire lock explicitly on desktop (because the system acquires lock based on editing operation)  --or I am not aware.

About isolating the event: The other thing we are trying is to publish the feature that we want to update as a separate feature service to see if that makes a difference.

We are doing 2 counties at a time now about 10,000 records, it takes 2 hours. 

[RyanKoschatzky]

You should be able to acquire locks outside of a tool. 

https://pro.arcgis.com/en/pro-app/latest/help/production/roads-highways/lrs-locks-table.htm

[ElsitMandal1]

yes, the documentation states that it is possible to "acquire" locks, but the documentation team has agreed that statement is incorrect when pointed out--we cannot acquire locks using the locks table. They are going to update it in the next release of the documentation if I remember correct, according to communication I received probably a month ago. 

[RyanKoschatzky]

Well that is not great, thanks for finding the issue and helping to get the documentation changed. 

[ElsitMandal1]

If we could really acquire locks using the locks table, then I am guessing that would be helpful just you had initially suggested.

We just completed testing isolating the feature service as its own feature service and started field calculate on 5000 records in one county, then edited an entirely different event in a separate feature service in a different county and posted. Immediately the field calculate process failed with 99999 error. We were not expecting this behavior because the posted event has nothing to do with the field calculate event.

[KyleGonterwitz]

Hi Elsit!   

I believe you can do sql updates on a branch versioned database:

UPDATE MyBranchVersionedTable
SET Field1 = 'New Value'
WHERE ID = 123
-- Specify the branch version to update
FOR VERSION 'MyBranchVersion';

Another alternative, similar to your workaround, would be to save the table in the SQL database, use SQL update/set values rather than ArcGIS field calculator, then append processed records.  I think the database updates are way faster than field calculator.