How can I make this arcpy script faster?

Anonymous User · ‎08-15-2017

I am creating a feature class that has all related features to our pole feature class into one. The process runs against a enterprise geodatabase on SQL Server. There are 48,191 poles. Some poles have one or more lights, fuses, transformers, and other equipment attached. We track those attachments using geodatabase relationship classes. I used arcpy InsertCursor SearchCursor and UpdateCursor to create the new feature class. And I used cProfile to report the stats of the script during runtime.

I think I can make this more efficient and pythonic but I hit a wall trying to reduce the length of time it runs. Its been running over 8 hours now. I'll post the cProfile stats when the script is finished running.

#todo fix 'eSupportStructure_YEARMANUFACTU'
import arcpy

# Poles use shape@ to create point feature for damage assessment
PoleFldsOrig = ['GLOBALID','SUBTYPE','OWNER','FACILITYID','POLESIZE','MATERIAL','COLOR','TREATMENTTYPE','YEARMANUFACTURED','STUBDATE','BILLINGGROUP','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_YEARMANUFACTU','eSupportStructure_STUBDATE','eSupportStructure_BILLINGGROUP','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]

# SDE variables
unitTable = r'Database Connections\OSAuth.sde\Inspection\eDamageAssessment'
truncTable = r'Database Connections\DBO.sde\Inspection\eDamageAssessment'
connectionRoot = r'Database Connections\OSAuth.sde\Electric\%s'
workspace = r'Database Connections\OSAuth.sde'

# feature classes
origFC = ['eCapacitorBank','eRecloser','eSectionalizer','eVoltageRegulatorBank']
fuseFC = 'eFuse'
lightFC = 'eLight'
poleFC = 'eSupportStructure'
servicepointFC = 'eServicePoint'
switchFC = 'eSwitch'
transformerFC = 'eTransformerBank'

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
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
print 'Truncating...',truncTable
arcpy.TruncateTable_management(truncTable)

# 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

# tables should have equal number of rows
getRowCount(unitTable,'OBJECTID',whereSQL=None)
getRowCount(poleFC,'OBJECTID',whereSQL=None)
print 'finished'
               
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

IanMurray · ‎08-15-2017

Yikes that is some nested cursor madness, you should never run one cursor inside of another cursor. No wonder that gets bogged down.

Please see Richard Fairhurst's blog, it should have some examples that will help out streamlining your processing of data using dictionaries instead of keeping multiple cursors open at a time. https://community.esri.com/blogs/richard_fairhurst/2014/11/08/turbo-charging-data-manipulation-with-...

Edit: Tagging Richard Fairhurst

Further Edit: Didn't see you had troubleshot a similar problem with Richard https://community.esri.com/thread/119381‌

JoshuaBixby · ‎08-15-2017

At first glance, I agree there appears to be room for optimizing the use of cursors. That said, I think a general statement of "never run one cursor inside of another cursor" oversimplifies the recommendation. Instantiating cursors does come with a cost, so instantiating a cursor with each new record of another cursor (which is what the OP is doing) will undoubtedly slow things down to a crawl, but the performance hit is being driven by the loop of the first cursor and not the nested nature of the cursors directly.

IanMurray · ‎08-15-2017

He has nested cursors 3 deep in from line 127 onward, the one open for that loop, another one on the subsequent with statements on lines 131, 138, 145, 152, and 159, and then within each of those loops call a function opening a third cursor. That is more than just a small performance hit, see https://gis.stackexchange.com/questions/220107/how-to-improve-performance-of-nested-search-cursors.

JoshuaBixby · ‎08-15-2017

Instead of focusing on optimizing parts of the script, there may be an overall different approach that works better. I tried to follow your code, and got part way through, but I eventually lost sight of the overall goal. Can you elaborate on what you expect the new feature class to look like? Even better would be some sample data from the original feature classes and tables and what the desired output looks like.

Anonymous User · ‎08-15-2017

Its my understanding of using a With Statement in arcpy that the cursors are removed when the With statement ends. So nesting should not be a problem (??).

As for the procedure. First it populates a point feature class with fields and SHAPE@ from eSupportStructure (aka poles). Then it uses the SearchCursor (unitCur) from unitTable to find the matching eSupportStructure_GlobalID value in each related feature class, such as light, fuse, transformer, etc.

Some related features are 1:n, such as lights. There could be as many as four lights on a pole. When this is found the updateRelatedUnits will update the unitTable row for each occurrence.

By the way I failed to write the cProfile stats to a output file and my buffer in the command window was not enough to get all stats. I'm running this against a fGDB right now, it is much faster. Based on count it is nearly 2/3 complete in about 20 minutes.

LightFldsDest
LightFldsDest2
LightFldsDest3
LightFldsDest4

# line 117 - update 1:1 related features into unitTable    
    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)
# line 125 - get cursor of poles to find 1:n related features
# use unit in unitCur to search all realted features, start with Lights first
    idx = -1
    count = 0
    with arcpy.da.SearchCursor(unitTable,['eSupportStructure_GLOBALID'],sql_clause=(None,"ORDER BY eSupportStructure_GLOBALID")) as unitCur:
        for unit in unitCur:

# find Lights that are related to unit in unitCur
# increment index to get next fldsDestLight
           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

# pass in lght from lghtCur
# it might find several lights that are related to eSupportStructure_GLOBALID in unitTable
# update LightFldsDest, break out and return, pass in next destFlds (such LightFldsDest2)
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
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

JoshuaBixby · ‎08-15-2017

No, ArcPy Data Access cursors are not deleted after the with statement completes. Using the with statement helps with managing locks, especially when an error is encountered while looping over a cursor. When no errors are encountered, the with statement allows the cursors reset method to be called behind the scenes so the user doesn't have to call it explicitly to reuse the cursor.

Anytime you create a cursor within a loop, even when looping just hundreds of times, there is probably a more efficient way to accomplish the task. I am not saying it won't work, you obviously have it working, but you are here now asking how to speed it up. Thus far, the overwhelming response from users is "don't nest cursors as much as you have."

My recommendation would be to look into using Pivot Table—Help | ArcGIS Desktop to turn those one-to-many tables into one-to-one tables, and then you can join everything together to create a new table once all of the tables are one-to-one.

MicahBabinski · ‎08-15-2017

Hello Darris,

I definitely agree with Joshua - keeping the overall goal in mind and clearly stating the desired end product will help target the best solution.

In the meantime, here's some additional observations:

I think your getRowCount function does pretty much the same thing as Get Count—Data Management toolbox | ArcGIS Desktop. This may work faster. Check the second script example for how to implement.
Cursoring through tables and feature classes stored in your Enterprise Geodatabase will be time-consuming. Since your data is in SQL Server, you may wish to consider using query layers and then the Copy Features tool to create your new feature classes, especially since you seem to have no trouble writing SQL queries.
Have you confirmed that an edit session is necessary to do this work? From my read of the documentation, this is only necessary if your datasets are registered as versioned. If they are not versioned, ditching the edit session may speed this up.

Good luck! Looks like an ambitious and cool process you are working on.

Micah

Anonymous User · ‎08-15-2017

Thanks for the tips, I'll review Query Layers and Copy Features.

The edit session is necessary, versioned enterprise data.

The end goal is make one row of all related equipment on a pole.

Our current version of ArcGIS Runtime does not support related objects with ObjectID. I can't move to using GlobalID for the relationship method because a third party application (Schneider Electric ArcFM Solution) does not support using GlobalID for relationships. So I have to create a separate feature class to perform damage inspections with yet another third party ArcGIS Runtime application.

Anonymous User · ‎08-15-2017

This completes in about 45 minutes in a fGDB, no changes to code. Maybe I should run the entire process in a temp fGDB and then insert rows into an empty feature class in the enterprise GDB at the end.