I'm attempting to update a numeric field using an arcpy.da.UpdateCursor. I'm applying the update to a Feature Layer and Table, which participate in a One to Many Relationship. The Feature layer is quite large, with 1,219,495 Polygon records. The table has even more records at 4,735,679 rows.
The update should only be applied if certain conditions are met. If the Child Table's Provider field has a value of "Optimum", it needs updated. If the Parent Feature's MaxDownload field is 1,000, it needs updated. I'm doing the queries first via SearchCursor, and saving the relevant IDs that satisfy the query. Then, in an edit session, I'm attempting to update the relevant fields using a where_clause to select the OIDs I need.
import datetime as dt
import arcpy
def log_it(msg):
print(f"[{dt.datetime.now()}]: {msg}")
return
def query_str_in_list(field_name, values):
vals = "'" + "', '".join(values) + "'"
return f"{field_name} IN ({vals})"
def query_int_in_list(field_name, values):
vals = ", ".join([str(v) for v in values])
return f"{field_name} IN ({vals})"
def main():
start_time = dt.datetime.now()
print(f"Starting script at {start_time}")
arcpy.env.workspace = r"C:\dev\arcgis\NYS-DPS\NysDpsBroadbandMap\2022-06-01_Analysis\2022-06-01_Analysis\NysDpsBroadbandMap.gdb"
global_ids = []
provider_oids = []
with arcpy.da.SearchCursor(
"StandardProviders",
["ParentGlobalID", "OBJECTID"],
where_clause="Provider = 'Optimum'",
) as search_cursor:
for row in search_cursor:
global_ids.append(row[0])
provider_oids.append(row[1])
polygon_oids = [
row[0]
for row in arcpy.da.SearchCursor(
"StandardSummaryPolygons",
["OBJECTID"],
where_clause=query_str_in_list("GlobalID", global_ids)
+ " AND MaxDownload = 1000",
)
]
log_it(
f"Beginning to edit {len(provider_oids)} Provider Records and {len(polygon_oids)} Polygon Records"
)
with arcpy.da.Editor(arcpy.env.workspace) as edit_session:
with arcpy.da.UpdateCursor(
"StandardSummaryPolygons",
["OID@", "MaxDownload"],
where_clause=query_int_in_list("OBJECTID", polygon_oids),
) as polygon_update_cursor:
for idx, row in enumerate(polygon_update_cursor):
log_it(f"{idx} Polygon Iterations")
oid = row[0]
if oid in polygon_oids:
polygon_update_cursor.updateRow([oid, 940])
with arcpy.da.UpdateCursor(
"StandardProviders",
["OID@", "Download"],
where_clause=query_int_in_list("OBJECTID", provider_oids),
) as provider_update_cursor:
for idx, row in enumerate(provider_update_cursor):
log_it(f"{idx} Provider Iterations")
oid = row[0]
if oid in provider_oids:
provider_update_cursor.updateRow([oid, 940])
end_time = dt.datetime.now()
duration = end_time - start_time
print(f"Finished at {end_time} after executing for {duration}")
return
if __name__ == "__main__":
main()
The script is going quite slow, and it appears that the Polygon Feature Class is the root cause. When iterating through the cursor, each iteration is taking 2 seconds. Iterating through the table gives the performance I would expect.
Here's a log snippet:
## Parent Table Log (Polygon Features)
Starting script at 2022-07-20 14:13:51.937132
[2022-07-20 14:13:59.535107]: Beginning to edit 276149 Provider Records and 212815 Polygon Records
[2022-07-20 14:13:59.801700]: 0 Polygon Iterations
[2022-07-20 14:14:01.015486]: 1 Polygon Iterations
[2022-07-20 14:14:02.363565]: 2 Polygon Iterations
[2022-07-20 14:14:03.780736]: 3 Polygon Iterations
## Child Table Log (Table Rows)
[2022-07-20 14:19:10.037031]: 6740 Provider Iterations
[2022-07-20 14:19:10.037031]: 6741 Provider Iterations
[2022-07-20 14:19:10.038029]: 6742 Provider Iterations
[2022-07-20 14:19:10.038029]: 6743 Provider Iterations
Am I simply running into physical limits due to the volume of the data? Are there any performance gains that could be implemented here?
Thanks!
