Efficiently selecting related records using ArcPy?

Question

Below is the code I'm using to replicate the "related tables" button in ArcMap. In ArcMap that button selects features in one feature class or table based on the selection of features in another related feature class or table.

In ArcMap I can use that button to "push" my selection to the related table in a matter of seconds. I was unable to find anything built in to arcpy that replicates the button so I used some nested loops to do the same task.

The code below loops through a table of "treatments". For each treatment, it loops through a list of "trees". When a match is found between the ID fields of treatment and trees, a selection occurs in the tree layer. Once a match is found for a treatment, the code does not continue to search the tree layer for additional matches. It goes back to the treatment table, selects the next treatment and again searches through the tree feature class.

The code itself works fine, but it is agonizingly slow. The "treatment table" in this case has 16,000 records. The "tree" feature class has 60,000 records.

Is there another more efficient way to recreate what ESRI is doing when it pushes the selection from one table to another? Should I be creating an index for the tables? NOTE: This data is stored in an SDE.

 # Create search cursor to loop through the treatments
treatments = arcpy.SearchCursor(treatment_tv)
treatment_field = "Facility_ID"
for treatment in treatments:
 #Get ID of treatment
 treatment_ID = treatment.getValue(treatment_field)
 # Create search cursor for looping through the trees
 trees = arcpy.SearchCursor(tree_fl)
 tree_field = "FACILITYID"
 for tree in trees:
 # Get FID of tree
 tree_FID = tree.getValue(tree_field)
 if tree_FID == treatment_FID:
 query = "FACILITYID = " + str(tree_FID)
 arcpy.SelectLayerByAttribute_management(tree_fl, "REMOVE_FROM_SELECTION", query)
 break

• PolyGeo: Are you using ArcGIS 10.1? If so, arcpy.da.SearchCursor is likely to be much faster (perhaps 10X) than arcpy.SearchCursor. Also, you may want to consider the use of a Python dictionary. I suspect that a "keyfile selection" like this might greatly benefit from the approach used here
• blah238: Is your SDE database on Oracle by chance?

Answer 1

First off, yes you will definitely want to make sure your primary and foreign key fields are indexed on both tables. This lets the DBMS plan and execute queries against these fields much more efficiently.

Secondly, you are calling SelectLayerByAttribute_management in a tight, nested loop (once per tree per treatment). This is highly inefficient, for several reasons:

• You don't need two loops, one nested within the other, for this, as far as I can tell. One will suffice.
• Geoprocessing functions are "chunky" and take a lot of time to call compared to typical built-in Python functions. You should avoid calling them in a tight loop.
• Asking for one record/ID at a time results in vastly more round trips to the database.

Instead, refactor your code so that you call SelectLayerByAttribute_management just once with a whereclause constructed to select all of the related records.

Borrowing a function from another answer for the whereclause construction logic, I'd imagine it would look something like this:

def selectRelatedRecords(sourceLayer, targetLayer, sourceField, targetField):
 sourceIDs = set([row[0] for row in arcpy.da.SearchCursor(sourceLayer, sourceField)])
 whereClause = buildWhereClauseFromList(targetLayer, targetField, sourceIDs)
 arcpy.AddMessage("Selecting related records using WhereClause: {0}".format(whereClause))
 arcpy.SelectLayerByAttribute_management(targetLayer, "NEW_SELECTION", whereClause)

You could call it like so: selectRelatedRecords(treatment_tv, tree_fl, "Facility_ID", "FACILITYID")

Notes:

• This uses an arcpy.da.SearchCursor, only available at 10.1. As @PolyGeo mentioned, these cursors are much faster than their predecessors (arcpy.SearchCursor). It could be easily modified to use the old SearchCursor though:

  sourceIDs = set([row.getValue(sourceField) for row in arcpy.SearchCursor(sourceLayer, "", "", sourceField)])
  

• If your SDE geodatabase is on Oracle, be warned that the IN statement used in the function from the linked answer is limited to 1000 elements. One possible solution is described in this answer, but you'd have to modify the function to split it into multiple 1000-length IN statements instead of one.

Answer 2

The above solution works great for me and was very quick. Using the above code and referenced code from the other post this is how I built it:

# Local Variables
OriginTable = "This must be a Table View or Feature Layer"
DestinationTable = "This must be a Table View or Feature Layer"
PrimaryKeyField = "Matching Origin Table Field"
ForiegnKeyField = "Matching Destination Table Field"
def buildWhereClauseFromList(OriginTable, PrimaryKeyField, valueList):
 """Takes a list of values and constructs a SQL WHERE
 clause to select those values within a given PrimaryKeyField
 and OriginTable."""
 # Add DBMS-specific field delimiters
 fieldDelimited = arcpy.AddFieldDelimiters(arcpy.Describe(OriginTable).path, PrimaryKeyField)
 # Determine field type
 fieldType = arcpy.ListFields(OriginTable, PrimaryKeyField)[0].type
 # Add single-quotes for string field values
 if str(fieldType) == 'String':
 valueList = ["'%s'" % value for value in valueList]
 # Format WHERE clause in the form of an IN statement
 whereClause = "%s IN(%s)" % (fieldDelimited, ', '.join(map(str, valueList)))
 return whereClause
def selectRelatedRecords(OriginTable, DestinationTable, PrimaryKeyField, ForiegnKeyField):
 """Defines the record selection from the record selection of the OriginTable
 and applys it to the DestinationTable using a SQL WHERE clause built
 in the previous defintion"""
 # Set the SearchCursor to look through the selection of the OriginTable
 sourceIDs = set([row[0] for row in arcpy.da.SearchCursor(OriginTable, PrimaryKeyField)])
 # Establishes the where clause used to select records from DestinationTable
 whereClause = buildWhereClauseFromList(DestinationTable, ForiegnKeyField, sourceIDs)
 # Process: Select Layer By Attribute
 arcpy.SelectLayerByAttribute_management(DestinationTable, "NEW_SELECTION", whereClause)
# Process: Select related records between OriginTable and DestinationTable
selectRelatedRecords(OriginTable, DestinationTable, PrimaryKeyField, ForiegnKeyField)