ArcPy script with cursors running slow?

Question

I've got 2 polygons layers (no gaps): IDUs and crops. What I want is to identify the 3 largest crops by % area for each IDU. This seems common enough but I couldn't find a way to do it all using tools, so I decided to try and learn basic arcpy to finish the job. I'm in ArcGIS 10.1 SP 1 (Advanced) and have done lots of PHP but this is my first try at python.

I found the Tablulate Intersections tool which works great and gives a table with all the overlapping crops with area and %area fields, but I want to add attributes to the IDU layer for the 3 largest crop codes - eg. LU_1=123, LU_2=201, LU_3=132. Here's the script I wrote. It works, but it's taking 100sec to process 160 test IDU records, and eventually it needs to process 100,000. I've got a 2013 Retina MacBookPro which flies through everything else I've thrown in in Arc so far, so something is obviously poorly done in my code.

import arcpy
idusFC = "c:/Envision/StudyAreas/PEI-Dev/NewIDUs/PEI.gdb/IDUs_Test"
cropTB = "c:/Envision/StudyAreas/PEI-Dev/NewIDUs/PEI.gdb/IDU_CROP2011_table_Sort1"
iduFields = ["CROP2011_A_Code", "CROP2011_B_Code", "CROP2011_C_Code", "IDU_ID"]
cropsFields = ["CROP_2011_GRIDCODE"]
print "Starting processing"
start = time.time()
with arcpy.da.UpdateCursor(idusFC, iduFields) as idus:
 for idu in idus:
 itr = 0
 iduID = idu[3]
 whereClause = arcpy.AddFieldDelimiters(cropTB, "IDU_ID") + " = " + str(iduID)
 with arcpy.da.SearchCursor(cropTB, cropsFields, where_clause=whereClause) as iducrops:
 for iducrop in iducrops:
 idu[itr] = iducrop[0]
 itr += 1
 if itr > 2:
 break
 idus.updateRow(idu)
end = time.time()
processTime = end - start
print "Processing Completed - {:.1f} seconds".format(processTime)

Is there another way I should do this so that it runs reasonably fast?

• Hornbydd: As you are using a whereClause on the IDU_ID field, have you added an attribute index to this field? This would certainly speed up processing.
• dklassen: Instead of running your Search cursor within your Update cursor, you might want to try to do the Search cursor first - then the update cursor - using a list or a dictionary to store you search values.
• ShaneW: I tried adding an index but that didn't help at all. I did rewrite the code to make the two loops sequential, and that did help, though I also changed to creating a new table rather than updating an existing table so it's hard to know where the bigger benefit came from. One other likely factor is that I was entering the code in the python window, but later turned it into a python script tool. In the end, it now takes about 15 seconds to process 200,000 rows of input - that seems about right.

Answer 1

The answer to this seems to be in comments:

• @dklassen suggested:

Instead of running your Search cursor within your Update cursor, you might want to try to do the Search cursor first - then the update cursor - using a list or a dictionary to store you search values.

• ShaneW (the asker) then said:

I did rewrite the code to make the two loops sequential, and that did help, though I also changed to creating a new table rather than updating an existing table so it's hard to know where the bigger benefit came from. One other likely factor is that I was entering the code in the python window, but later turned it into a python script tool. In the end, it now takes about 15 seconds to process 200,000 rows of input - that seems about right.