Thanks for the help Richard Fairhurst. Although your code did not solve my performance issue the discussion was helpful. I discovered that the iteration error I mentioned in my original post was due to the lack of a FOR loop following the declaration of the updateCursor (uc), now added at line 62 below.
I reverted back to my original code using a WITH statement as that will release the cursors automatically (according to the documentation), thus negating the need for extra code to delete it during processing. I appreciate the education of using a dictionary but I don't see an improvement with performance while using it here.
To answer your last questions, we are using SQL Server on a versioned enterprise database. And using edit.startEditing(Boolean,Boolean) combinations did not affect the insertCursor performance. The documentation indicates some overrides depending on the DB environment.
My script will run once a year at the end of our inspection cycle, so I can live with the 45 minutes processing time, I have other items that need attention now. I will mark your answer to give you credit for the assistance.
My final version is posted below in case others may find it useful. It was slightly changed so I can post it here without sharing some details of our system, so formatting may be off. But the bulk of it is here for others to consume.
import arcpy, os, time, sys, traceback
import Utils
import MM
logFileName = "C:/GISData/Utilities/Logs/ValveInspections.log"
mmapp = MM.InitializeLicense()
previousAUMode = MM.DisableAUs()
workspace = 'Database Connections/OSAuth.sde'
sourceFC = 'Database Connections/OSAuth.sde/Inspection/gValveInspection'
targetTable = 'Database Connections/OSAuth.sde/GVALVEINSPECTION'
targetFC = 'Database Connections/OSAuth.sde/Gas/gGasValve'
fieldsToInsert = ['FACILITYID','REPAIRSMADE','CONDITIONFOUND','CONDITIONLEFT','MAINTENANCENOTES','TIMEWORKED','LUBRICATEDVALVE','WORKEDBY','INSPECTOR','INSPECTIONID','INSPECTIONDATE','INSPECTIONSTATUS','PAINTED','VACUUMED','TAGGED','WORKEDBY','FLAGGED','MARKER','INSECTICIDE','CGIBEGIN','CGIEND','MAPCORRECT','EXERCISED','GASVALVEOID']
fieldsToUpdate = ['FACILITYID','VALVEBOX','INSPECTIONDATE','INSPECTIONSTATUS','TAGGED','FLAGGED','MARKER']
logFile = Utils.Log("<PROCESS name='ValveInspections.py'><TIMESTAMP position='start'>" + time.strftime("%Y%m%d.%H%M%S") + "</TIMESTAMP>\n", None, logFileName)
print("PROCESS ValveInspections.py start " + time.strftime("%Y%m%d.%H%M%S"))
try:
Utils.Log("<COMMENT>Starting edit operation</COMMENT>\n", logFile)
print("Starting edit operation")
edit = arcpy.da.Editor(workspace)
edit.startEditing(False,True)
edit.startOperation()
except Exception as err:
# Get the traceback object
tb = sys.exc_info()[2]
tbinfo = traceback.format_tb(tb)[0]
# Concatenate information together concerning the error into a message string
pymsg = "PYTHON ERRORS:\nTraceback info:\n" + tbinfo + "\nError Info:\n" + str(sys.exc_info()[1])
msgs = "ArcPy ERRORS:\n" + arcpy.GetMessages(2) + "\n"
print pymsg
print msgs
Utils.Log("<ERROR>Failed to start edit operation</ERROR>\n", logFile)
try:
Utils.Log("<COMMENT>Searching sourceFC to insert rows into targetTable<TIMESTAMP position='start'>" + time.strftime("%Y%m%d.%H%M%S") + "</TIMESTAMP></COMMENT>\n", logFile)
print("Searching sourceFC to insert rows into targetTable start " + str(time.strftime("%Y%m%d.%H%M%S")))
#ic = arcpy.da.InsertCursor(targetTable, fieldsToInsert)
with arcpy.da.SearchCursor(sourceFC, fieldsToInsert, sql_clause=(None, 'ORDER BY INSPECTIONDATE')) as sc:
with arcpy.da.InsertCursor(targetTable, fieldsToInsert) as ic:
for scRow in sc:
arcpy.AddMessage("Inserting inspection for valve number " + (scRow[0]) + " with inspection date " + str((scRow[10])))
print("Inserting inspection for valve number " + (scRow[0]) + " with inspection date " + str((scRow[10])))
Utils.Log("<ACTION>Inserting inspection for " + (scRow[0]) + " with inspection date " + str((scRow[10])) + "</ACTION>\n", logFile)
ic.insertRow((scRow))
Utils.Log("<COMMENT>Stopped searching sourceFC to insert rows into targetTable<TIMESTAMP position='end'>" + time.strftime("%Y%m%d.%H%M%S") + "</TIMESTAMP></COMMENT>\n", logFile)
print("Stopped searching sourceFC to insert rows into targetTable end " + str(time.strftime("%Y%m%d.%H%M%S")))
Utils.Log("<COMMENT>Searching sourceFC to update rows in targetFC<TIMESTAMP position='start'>" + time.strftime("%Y%m%d.%H%M%S") + "</TIMESTAMP></COMMENT>\n", logFile)
print("Searching sourceFC to update rows in targetFC start " + str(time.strftime("%Y%m%d.%H%M%S")))
with arcpy.da.SearchCursor(sourceFC, fieldsToUpdate, sql_clause=(None, 'ORDER BY INSPECTIONDATE')) as sc2:
for sc2Row in sc2:
valveNumber = (sc2Row[0])
arcpy.AddMessage("Updating gGasValve " + (sc2Row[0]))
print("Updating gGasValve " + (sc2Row[0]))
Utils.Log("<ACTION>Updating gGasValve " + (sc2Row[0]) + "</ACTION>\n", logFile)
try:
uc = arcpy.da.UpdateCursor(targetFC, fieldsToUpdate, where_clause="FACILITYID = '" + (sc2Row[0]) + "'")
for ucRow in uc:
uc.updateRow(sc2Row)
except Exception as err:
# Get the traceback object
tb = sys.exc_info()[2]
tbinfo = traceback.format_tb(tb)[0]
# Concatenate information together concerning the error into a message string
pymsg = "PYTHON ERRORS:\nTraceback info:\n" + tbinfo + "\nError Info:\n" + str(sys.exc_info()[1])
msgs = "ArcPy ERRORS:\n" + arcpy.GetMessages(2) + "\n"
print pymsg
print msgs
Utils.Log("<ERROR>Failed updating gGasValve FacilityID " + valveNumber + "</ERROR>\n", logFile)
Utils.Log("<COMMENT>Stopped searching sourceFC to update rows in targetFC<TIMESTAMP position='end'>" + time.strftime("%Y%m%d.%H%M%S") + "</TIMESTAMP></COMMENT>\n", logFile)
print("Stopped searching sourceFC to update rows in targetFC end " + str(time.strftime("%Y%m%d.%H%M%S")))
except Exception as err:
# Get the traceback object
tb = sys.exc_info()[2]
tbinfo = traceback.format_tb(tb)[0]
# Concatenate information together concerning the error into a message string
pymsg = "PYTHON ERRORS:\nTraceback info:\n" + tbinfo + "\nError Info:\n" + str(sys.exc_info()[1])
msgs = "ArcPy ERRORS:\n" + arcpy.GetMessages(2) + "\n"
print pymsg
print msgs
finally:
edit.stopOperation()
edit.stopEditing(True)
mmapp.Shutdown
del mmapp
Utils.Log("<PROCESS name='ValveInspections.py'><TIMESTAMP position='end'>" + time.strftime("%Y%m%d.%H%M%S") + "</TIMESTAMP>\n", logFile)
print("ValveInspections.py end " + str(time.strftime("%Y%m%d.%H%M%S")))