Optimize Python Code

Anonymous User — Sat, 11 Dec 2021 08:47:53 GMT
I wrote a python script that creates a point feature class using the data from all of the related table information. The general work flow is creating a pole feature class and then updating the fields from any table that is related to each pole. For example if a transformer and light are attached to the pole in the field then the related table data for that pole is updated. It takes about 7 hours to complete. Can anyone provide tips on how to optimize the code so it runs faster? I read a post on Richard Fairhurst's Blog‌ about performance improvements when using Dictionaries. I will try that as well, but any pointers in the mean time will be appreciated.
#todo fix 'eSupportStructure_YEARMANUFACTU'
import arcpy,time

startTime = time.time()

# Poles use shape@ to create point feature for damage assessment
PoleFldsOrig = ['GLOBALID','SUBTYPE','OWNER','FACILITYID','POLESIZE','MATERIAL','COLOR','TREATMENTTYPE','YEARMANUFACTURED','STUBDATE','BILLINGGROUP','FEEDERIDS','INSTALL_NUM','UNIT_TYPE','STREETADDRESS','BACKBONEINDICATOR','SHAPE@']
PoleFldsDest = ['eSupportStructure_GLOBALID','eSupportStructure_SUBTYPE','eSupportStructure_OWNER','eSupportStructure_FACILITYID','eSupportStructure_POLESIZE','eSupportStructure_MATERIAL','eSupportStructure_COLOR','eSupportStructure_TREATMENTTYPE','eSupportStructure_YEARMANUFACTURED','eSupportStructure_STUBDATE','eSupportStructure_BILLINGGROUP','eSupportStructure_FEEDERIDS','eSupportStructure_INSTALLNUM','eSupportStructure_UNITTYPE','eSupportStructure_STREETADDRESS','eSupportStructure_BACKBONE','SHAPE@']

# feature classes that have 1:1 relationship with poles
CapBankFldsOrig = ['eSupportStructure_GLOBALID','DEVICEID','SUBTYPE','STRUCTUREID','GLOBALID']
CapBankFldsDest = ['eSupportStructure_GLOBALID','eCapacitorBank_DEVICEID','eCapacitorBank_SUBTYPE','eCapacitorBank_STRUCTUREID','eCapacitorBank_GLOBALID']
RecloserFldsOrig = ['eSupportStructure_GLOBALID','DEVICEID','FEEDERID','FEEDERID2','PHASEDESIGNATION','SUBTYPE','AMPRATING','BACKBONEPASSTHRU','CUSTOMERCOUNT','STRUCTUREID','INTERRUPTINGTYPE','INTERRUPTRATING','CONTROLLERTYPE','BACKBONEINDICATOR','GLOBALID']
RecloserFldsDest = ['eSupportStructure_GLOBALID','eRecloser_DEVICEID','eRecloser_FEEDERID','eRecloser_FEEDERID2','eRecloser_PHASE','eRecloser_SUBTYPE','eRecloser_AMPRATING','eRecloser_BACKBONEPASSTHRU','eRecloser_CUSTOMERCOUNT','eRecloser_STRUCTUREID','eRecloser_INTERRUPTINGTYPE','eRecloser_INTERRUPTRATING','eRecloser_CONTROLLERTYPE','eRecloser_BACKBONE','eRecloser_GLOBALID']
SectionalizerFldsOrig = ['eSupportStructure_GLOBALID','DEVICEID','FEEDERID','FEEDERID2','BACKBONEPASSTHRU','CUSTOMERCOUNT','STRUCTUREID','GLOBALID']
SectionalizerFldsDest = ['eSupportStructure_GLOBALID','eSectionalizer_DEVICEID','eSectionalizer_FEEDERID','eSectionalizer_FEEDERID2','eSectionalize_BACKBONEPASSTHRU','eSectionalizer_CUSTOMERCOUNT','eSectionalizer_STRUCTUREID','eSectionalizer_GLOBALID']
VoltRegBankFldsOrig = ['eSupportStructure_GLOBALID','DEVICEID','FEEDERID','STRUCTUREID','GLOBALID']
VoltRegBankFldsDest = ['eSupportStructure_GLOBALID','eVoltRegBank_DEVICEID','eVoltRegBank_FEEDERID','eVoltRegBank_STRUCTUREID','eVoltRegBank_GLOBALID']

# the following five feature classes have 1:n relationship with poles
# Fuse
FuseFldsOrig = ['eSupportStructure_GLOBALID','DEVICEID','FeederID','FeederID2','PhaseDesignation','SUBTYPE','RATING_A','RATING_B','RATING_C','LOADBREAK','BACKBONEEND','BACKBONEPASSTHRU','CUSTOMERCOUNT','STRUCTUREID','GLOBALID']
FuseFldsDest = ['eSupportStructure_GLOBALID','eFuse_DEVICEID','eFuse_FeederID','eFuse_FeederID2','eFuse_Phase','eFuse_SUBTYPE','eFuse_RATING_A','eFuse_RATING_B','eFuse_RATING_C','eFuse_LOADBREAK','eFuse_BACKBONEEND','eFuse_BACKBONEPASSTHRU','eFuse_CUSTOMERCOUNT','eFuse_STRUCTUREID','eFuse_GLOBALID']
FuseFldsDest2 = ['eSupportStructure_GLOBALID','eFuse_DEVICEID2','eFuse_FeederID_2','eFuse_FeederID22','eFuse_Phase2','eFuse_SUBTYPE2','eFuse_RATING_A2','eFuse_RATING_B2','eFuse_RATING_C2','eFuse_LOADBREAK2','eFuse_BACKBONEEND2','eFuse_BACKBONEPASSTHRU2','eFuse_CUSTOMERCOUNT2','eFuse_STRUCTUREID','eFuse_GLOBALID2']
FuseFldsDest3 = ['eSupportStructure_GLOBALID','eFuse_DEVICEID3','eFuse_FeederID3','eFuse_FeederID23','eFuse_Phase3','eFuse_SUBTYPE3','eFuse_RATING_A3','eFuse_RATING_B3','eFuse_RATING_C3','eFuse_LOADBREAK3','eFuse_BACKBONEEND3','eFuse_BACKBONEPASSTHRU3','eFuse_CUSTOMERCOUNT3','eFuse_STRUCTUREID','eFuse_GLOBALID3']
FuseFldsDest4 = ['eSupportStructure_GLOBALID','eFuse_DEVICEID4','eFuse_FeederID4','eFuse_FeederID24','eFuse_Phase4','eFuse_SUBTYPE4','eFuse_RATING_A4','eFuse_RATING_B4','eFuse_RATING_C4','eFuse_LOADBREAK4','eFuse_BACKBONEEND4','eFuse_BACKBONEPASSTHRU4','eFuse_CUSTOMERCOUNT4','eFuse_STRUCTUREID','eFuse_GLOBALID4']
FuseFldsDest5 = ['eSupportStructure_GLOBALID','eFuse_DEVICEID5','eFuse_FeederID5','eFuse_FeederID25','eFuse_Phase5','eFuse_SUBTYPE5','eFuse_RATING_A5','eFuse_RATING_B5','eFuse_RATING_C5','eFuse_LOADBREAK5','eFuse_BACKBONEEND5','eFuse_BACKBONEPASSTHRU5','eFuse_CUSTOMERCOUNT5','eFuse_STRUCTUREID','eFuse_GLOBALID5']
FuseFldsDest6 = ['eSupportStructure_GLOBALID','eFuse_DEVICEID6','eFuse_FeederID6','eFuse_FeederID26','eFuse_Phase6','eFuse_SUBTYPE6','eFuse_RATING_A6','eFuse_RATING_B6','eFuse_RATING_C6','eFuse_LOADBREAK6','eFuse_BACKBONEEND6','eFuse_BACKBONEPASSTHRU6','eFuse_CUSTOMERCOUNT6','eFuse_STRUCTUREID','eFuse_GLOBALID6']

# Lights
LightFldsOrig = ['eSupportStructure_GLOBALID','SUBTYPE','LAMP','COLOR','ARMDIRECTION','ARMLENGTH','STRUCTUREID','GLOBALID']
LightFldsDest = ['eSupportStructure_GLOBALID','eLight_SUBTYPE','eLIGHT_LAMP','eLight_COLOR','eLight_ARMDIRECTION','eLight_ARMLENGTH','eLight_STRUCTUREID','eLight_GLOBALID']
LightFldsDest2 = ['eSupportStructure_GLOBALID','eLight_SUBTYPE2','eLIGHT_LAMP2','eLight_COLOR2','eLight_ARMDIRECTION2','eLight_ARMLENGTH2','eLight_STRUCTUREID','eLight_GLOBALID2']
LightFldsDest3 = ['eSupportStructure_GLOBALID','eLight_SUBTYPE3','eLIGHT_LAMP3','eLight_COLOR3','eLight_ARMDIRECTION3','eLight_ARMLENGTH3','eLight_STRUCTUREID','eLight_GLOBALID3']
LightFldsDest4 = ['eSupportStructure_GLOBALID','eLight_SUBTYPE4','eLIGHT_LAMP4','eLight_COLOR4','eLight_ARMDIRECTION4','eLight_ARMLENGTH4','eLight_STRUCTUREID','eLight_GLOBALID4']

# Service Point
ServicePointFldsOrig = ['eSupportStructure_GLOBALID','SUBTYPE','FEEDERID','METERTYPE','PHASEDESIGNATION','CUSTOMERCOUNT','STREETADDRESS','PRIORITYREASON','GLOBALID']
ServicePointFldsDest = ['eSupportStructure_GLOBALID','eServicePoint_SUBTYPE','eServicePoint_FEEDERID','eServicePoint_METERTYPE','eServicePoint_PHASE','eServicePoint_CUSTOMERCOUNT','eServicePoint_STREETADDRESS','eServicePoint_PRIORITYREASON','eServicePoint_GLOBALID']
ServicePointFldsDest2 = ['eSupportStructure_GLOBALID','eServicePoint_SUBTYPE2','eServicePoint_FEEDERID_2','eServicePoint_METERTYPE2','eServicePoint_PHASE2','eServicePoint_CUSTOMERCOUNT2','eServicePoint_STREETADDRESS2','eServicePoint_PRIORITYREASON2','eServicePoint_GLOBALID2']
ServicePointFldsDest3 = ['eSupportStructure_GLOBALID','eServicePoint_SUBTYPE3','eServicePoint_FEEDERID3','eServicePoint_METERTYPE3','eServicePoint_PHASE3','eServicePoint_CUSTOMERCOUNT3','eServicePoint_STREETADDRESS3','eServicePoint_PRIORITYREASON3','eServicePoint_GLOBALID3']
ServicePointFldsDest4 = ['eSupportStructure_GLOBALID','eServicePoint_SUBTYPE4','eServicePoint_FEEDERID4','eServicePoint_METERTYPE4','eServicePoint_PHASE4','eServicePoint_CUSTOMERCOUNT4','eServicePoint_STREETADDRESS4','eServicePoint_PRIORITYREASON4','eServicePoint_GLOBALID4']

# Switch
SwitchFldsOrig = ['eSupportStructure_GLOBALID','DEVICEID','FEEDERID','FEEDERID2','PHASEDESIGNATION','SUBTYPE','GANGOPERATED','LOADBREAK','STRUCTUREID','CUSTOMERCOUNT','AUTOTRANSFER','CONTROLLERTYPE','MOTOROPERATED','BACKBONEINDICATOR','BYPASSINDICATOR','GLOBALID']
SwitchFldsDest = ['eSupportStructure_GLOBALID','eSwitch_DEVICEID','eSwitch_FEEDERID','eSwitch_FEEDERID2','eSwitch_PHASE','eSwitch_SUBTYPE','eSwitch_GANGOPERATED','eSwitch_LOADBREAK','eSwitch_STRUCTUREID','eSwitch_CUSTOMERCOUNT','eSwitch_AUTOTRANSFER','eSwitch_CONTROLLERTYPE','eSwitch_MOTOROPERATED','eSwitch_BACKBONE','eSwitch_BYPASS','eSwitch_GLOBALID']
SwitchFldsDest2 = ['eSupportStructure_GLOBALID','eSwitch_DEVICEID2','eSwitch_FEEDERID_2','eSwitch_FEEDERID22','eSwitch_PHASE2','eSwitch_SUBTYPE2','eSwitch_GANGOPERATED2','eSwitch_LOADBREAK2','eSwitch_STRUCTUREID','eSwitch_CUSTOMERCOUNT2','eSwitch_AUTOTRANSFER2','eSwitch_CONTROLLERTYPE2','eSwitch_MOTOROPERATED2','eSwitch_BACKBONE2','eSwitch_BYPASS2','eSwitch_GLOBALID2']
SwitchFldsDest3 = ['eSupportStructure_GLOBALID','eSwitch_DEVICEID3','eSwitch_FEEDERID3','eSwitch_FEEDERID23','eSwitch_PHASE3','eSwitch_SUBTYPE3','eSwitch_GANGOPERATED3','eSwitch_LOADBREAK3','eSwitch_STRUCTUREID','eSwitch_CUSTOMERCOUNT3','eSwitch_AUTOTRANSFER3','eSwitch_CONTROLLERTYPE3','eSwitch_MOTOROPERATED3','eSwitch_BACKBONE3','eSwitch_BYPASS3','eSwitch_GLOBALID3']

# Transformer Bank
TXFldsOrig = ['eSupportStructure_GLOBALID','DEVICEID','FEEDERID','FEEDERID2','PHASEDESIGNATION','SUBTYPE','RATEDKVA_A','RATEDKVA_B','RATEDKVA_C','CUSTOMERCOUNT','STRUCTUREID','PURPOSE','GLOBALID']
TXFldsDest = ['eSupportStructure_GLOBALID','eTX_DEVICEID','eTX_FEEDERID','eTX_FEEDERID2','eTX_PHASE','eTX_SUBTYPE','eTX_RATEDKVA_A','eTX_RATEDKVA_B','eTX_RATEDKVA_C','eTX_CUSTOMERCOUNT','eTX_STRUCTUREID','eTX_PURPOSE','eTX_GLOBALID']
TXFldsDest2 = ['eSupportStructure_GLOBALID','eTX_DEVICEID2','eTX_FEEDERID_2','eTX_FEEDERID22','eTX_PHASE2','eTX_SUBTYPE2','eTX_RATEDKVA_A2','eTX_RATEDKVA_B2','eTX_RATEDKVA_C2','eTX_CUSTOMERCOUNT2','eTX_STRUCTUREID','eTX_PURPOSE2','eTX_GLOBALID2']

# place list of fields above into another list for loop processing below
fldsOrig = [CapBankFldsOrig,RecloserFldsOrig,SectionalizerFldsOrig,VoltRegBankFldsOrig]
fldsDest = [CapBankFldsDest,RecloserFldsDest,SectionalizerFldsDest,VoltRegBankFldsDest]
fldsDestFuse = [FuseFldsDest,FuseFldsDest2,FuseFldsDest3,FuseFldsDest4,FuseFldsDest5,FuseFldsDest6]
fldsDestLight = [LightFldsDest,LightFldsDest2,LightFldsDest3,LightFldsDest4]
fldsDestServicePoint = [ServicePointFldsDest,ServicePointFldsDest2,ServicePointFldsDest3,ServicePointFldsDest4]
fldsDestSwitch = [SwitchFldsDest,SwitchFldsDest2,SwitchFldsDest3]
fldsDestTX = [TXFldsDest,TXFldsDest2]

# fGDB variables
unitTable = r'C:\arcdata\transfer\MIMS_Electric_Extract.gdb\MIMS\mmPoleEquipment'
#unitTableSDE = r'Database Connections\grucwgisdv01_MapEDPR_OSAuth.sde\mapedpr.ARCFM_ED.MM_PoleEquipment'
connectionRoot = r'C:\arcdata\transfer\MIMS_Electric_Extract.gdb\%s'
workspace = r'C:\arcdata\transfer\MIMS_Electric_Extract.gdb'


# feature classes
MM_POLEEQUIPMENT_XML = "C:/arcdata/MIMSMobile/MIMS_DATASET.XML"
origFC = ['eCapacitorBank','eRecloser','eSectionalizer','eVoltageRegulatorBank']
fuseFC = 'eFuse'
lightFC = 'eLight'
poleFC = 'eSupportStructure'
servicepointFC = 'eServicePoint'
switchFC = 'eSwitch'
transformerFC = 'eTransformerBank'

# default field values
stormName = 'Storm Drill'
sapNetwork = 1111

def updateUnits(connection,origFlds,destFlds,whereSQL=None):
    uc = arcpy.da.UpdateCursor(unitTable,destFlds)
    with arcpy.da.SearchCursor(connection,origFlds,whereSQL) as sc:
         for scrow in sc:
             uc.updateRow(scrow)
    del uc
    del sc

def insertPoles(connection,origFlds,destFlds,whereSQL=None):
    ic = arcpy.da.InsertCursor(unitTable,destFlds)
    with arcpy.da.SearchCursor(connection,origFlds,whereSQL) as sc:
         for scrow in sc:
              ic.insertRow(scrow)
    del ic
    del sc

def getRowCount(tbl,flds,whereSQL=None):
    rows = [row for row in arcpy.da.SearchCursor(tbl,flds,whereSQL)]
    if len(rows) > 1:
        print('Found %i in %s') % (len(rows),tbl)
    return len(rows)

def updateRelatedUnits(row,destFlds,guid):
    with arcpy.da.UpdateCursor(unitTable,destFlds,where_clause="eSupportStructure_GLOBALID = " + "'" + guid + "'") as uc:
        for ucrow in uc:
            ucrow = row
            #print('updating destflds %s') % x
            uc.updateRow(ucrow)
            # break and return out of the for loop so we dont update the entire row with same cursor object
            break
        return
    del uc
        
# prep unitTable
if not arcpy.Exists(unitTable):
    arcpy.ImportXMLWorkspaceDocument_management(workspace, MM_POLEEQUIPMENT_XML, "Schema_Only")
    #print('Setting default values: %s %i') % (stormName,sapNetwork)
    #arcpy.AssignDefaultToField_management(unitTable,"StormName",stormName)
    #arcpy.AssignDefaultToField_management(unitTable,"SAPNetwork",sapNetwork)
else:
    print 'Truncating...',unitTable
    arcpy.TruncateTable_management(unitTable)
print 'Checking for index eSupportStructure_GLOABLID...'
indexes = arcpy.ListIndexes(unitTable)
indexNames = []
for index in indexes:
    print index.name
    indexNames.append(index.name)
if "IDX_poleGUID" not in indexNames:
    print'You need to add the Index' #arcpy.AddIndex_management (truncTable, "eSupportStructure_GLOBALID", "IDX_poleGUID", "UNIQUE", "ASCENDING")
# truncate table to reload poles and relationships for damage assessments

#with arcpy.da.UpdateCursor(unitTable,'StormName') as uc:
#    for row in uc:
#        row[0] = stormName
#        uc.updateRow(row)

print 'Processing...you gotta wait now'
# Start Main
with arcpy.da.Editor(workspace) as edit:
    #print 'Inserting...',poleFC
    ic = arcpy.da.InsertCursor(unitTable,PoleFldsDest)
    with arcpy.da.SearchCursor(connectionRoot%(poleFC),PoleFldsOrig) as sc:
        for scrow in sc:
            ic.insertRow(scrow)
    del ic
    del sc
    idx = -1
    for fc in origFC:
        idx +=1
        #print 'Updating...',fc
        with arcpy.da.SearchCursor(connectionRoot%(fc),fldsOrig[idx],where_clause="eSupportStructure_GLOBALID IS NOT NULL",sql_clause=(None,"ORDER BY eSupportStructure_GLOBALID")) as sCur:
            for sCurRow in sCur:
                with arcpy.da.UpdateCursor(unitTable,fldsDest[idx],where_clause="eSupportStructure_GLOBALID = " + "'" + sCurRow[0] + "'",sql_clause=(None,"ORDER BY eSupportStructure_GLOBALID")) as uCur:
                    for uCurRow in uCur:
                        uCur.updateRow(sCurRow)
    idx = -1
    count = 0
    with arcpy.da.SearchCursor(unitTable,['eSupportStructure_GLOBALID'],sql_clause=(None,"ORDER BY eSupportStructure_GLOBALID")) as unitCur:
        for unit in unitCur:
            count +=1
            #print(count)
            with arcpy.da.SearchCursor(connectionRoot%(lightFC),LightFldsOrig,where_clause="eSupportStructure_GLOBALID = '"  + unit[0] + "'") as lghtCur:
                for lght in lghtCur:
                    #print 'Updating...',lightFC
                    idx +=1
                    updateRelatedUnits(lght,fldsDestLight[idx],lght[0])
                idx = -1
            idx = -1
            with arcpy.da.SearchCursor(connectionRoot%(fuseFC),FuseFldsOrig,where_clause="eSupportStructure_GLOBALID = '"  + unit[0] + "'") as fxCur:
                for fx in fxCur:
                    #print 'Updating...',fuseFC
                    idx +=1
                    updateRelatedUnits(fx,fldsDestFuse[idx],fx[0])
                idx = -1
            idx = -1
            with arcpy.da.SearchCursor(connectionRoot%(transformerFC),TXFldsOrig,where_clause="eSupportStructure_GLOBALID = '"  + unit[0] + "'") as xfrCur:
                for xfr in xfrCur:
                    #print 'Updating...',transformerFC
                    idx +=1
                    updateRelatedUnits(xfr,fldsDestTX[idx],xfr[0])
                idx = -1
            idx = -1
            with arcpy.da.SearchCursor(connectionRoot%(switchFC),SwitchFldsOrig,where_clause="eSupportStructure_GLOBALID = '"  + unit[0] + "' AND SUBTYPE IN (1,2,3,4)") as swtCur:
                for swt in swtCur:
                    #print 'Updating...',switchFC
                    idx +=1
                    updateRelatedUnits(swt,fldsDestSwitch[idx],swt[0])
                idx = -1
            idx = -1
            with arcpy.da.SearchCursor(connectionRoot%(servicepointFC),ServicePointFldsOrig,where_clause="eSupportStructure_GLOBALID = '"  + unit[0] + "'") as svcCur:
                for svc in svcCur:
                    #print 'Updating...',servicepointFC
                    idx +=1
                    updateRelatedUnits(svc,fldsDestServicePoint[idx],svc[0])
                idx = -1


print ('finished - elapsed time: '),str((time.time() - startTime)/3600)

               
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
Re: Optimize Python Code

DanPatterson_Retired — Mon, 23 Apr 2018 23:22:04 GMT
nested cursors... I suspect that could be an issue, is there any way you can modify your workflow to gather information in a different way, then assemble what you need for final output.
topic Optimize Python Code in Python Questions

Optimize Python Code

Re: Optimize Python Code
