Calculating fields in hosted feature layers VERY SLOOOOWWWWW ....

[GISUser74305830]

• I have a hosted feature layer (706 features) and i want to update some rows based on an external table
• I have a file geodatabase table on my C: drive
• I have both the hosted feature layer and the table in an ArcPro session
• I joined the table to the hosted feature layer in Pro and am trying to calculate a bunch of fields
• EXTREMELY SLOW (10 minutes for each field)
• I've tried using Arcade and Python as my Expression Types and they are both extremely slow

What is going on?

[Clubdebambos · Accepted solution]

It is a slow process using the Calculate Field tool in ArcGIS Pro with a hosted feature service.

This can rapidly be sped up using a combination of ArcPy and ArcGIS for Python API.

Use ArcPy to create a dictionary of your gdb data with the join key field as the key in the dictionary and the other attributes as a list for the dictionary value.

Use ArcGIS for Python API to update your feature service data. Workflow could look similar to below.

from arcgis import GIS
import arcpy

fc = r"path\to\gdb\fc"

## connect to AGOL
agol = GIS("home")

# feature service item from AGOL
item = agol.content.get("FS_ITEM_ID")

# get the layer object by layer name in the feature servic
lyr = [lyr for lyr in item.layers if lyr.properties.name == "NAME_OF_LAYER_TO_UPDATE"][0]

# create join dictionary to be used below
join_dict = {}

with arcpy.da.SearchCursor(fc, ["KEYFIELDNAME1", "FIELDNAME2", "FIELDNAME3", "FIELDNAME4", "FIELDNAME5"]) as cursor:
    for row in cursor:
        join_dict[row[0]] = [row[1], row[2], row[3], row[4]]

# querying the layer to return a feature set object
query = lyr.query()

# populating field edits for layer update and field calculations
edit = [f for f in query.features]
for f in edit:
    join_key = f.attributes["KEYFIELDNAME1"]

    if join_key in join_dict:
        f.attributes["FIELDNAME2"] = join_dict[join_key][0]
        f.attributes["FIELDNAME3"] = join_dict[join_key][1]
        f.attributes["FIELDNAME4"] = join_dict[join_key][2]
        f.attributes["FIELDNAME5"] = join_dict[join_key][3]

# applying field calculation updates to the layer
print(lyr.edit_features(updates=edit))

[GISUser74305830]

I finally dug into this and applied it and it worked perfectly! Thank you very much!

[Kev]

What is duct_dict here? Should I create an empty dictionary with that name? 

[Clubdebambos]

Apologies, it was a copy and paste job from one of my workflows and forgot to replace. Code has been updated to reflect. 

[ChristopherCounsell · Accepted solution]

Use SQL in the calculate field tool. Calculating 10,000-30,000 records will go from 2 hours (arcade) to a matter of minutes (SQL). It's highlighted in the documentation:

https://doc.arcgis.com/en/arcgis-online/manage-data/calculate-fields.htm

• SQL—Use SQL for the fastest performance with calculations that can be performed with standardized SQL (SQL-92) expressions on nonspatial attributes. You can run SQL on sync-enabled hosted feature layers and layers configured to track feature creators and editors, whereas you cannot run Arcade expressions on such layers.

I believe it'll be faster if the 'undo' option is disabled, so the edits are committed immediately. I don't have a reference to back this up.

[Clubdebambos]

Far superior! Forgot about that one. It will reduce updating 10000 records in a hosted feature service from hours to seconds. One of the versions of ArcGIS Pro (in the 2.6 series) kept crashing on us when using SQL in the Calculate Field tool and we used the code in my previous post to overcome at the time. 

[GISUser74305830]

I have a join, so my only options are Python and Arcade.

Calculation options

[Clubdebambos]

@GISUser74305830 

The Python script is a great alternative so. It will be a lot faster than the CalculateField tool when using a Feature Service in ArcGIS Pro.