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.
Best Answer
If there is such an inbuilt method for ArcPy then I am unaware of it.
Curiously, the ArcInfo Workstation architecture (which preceded ArcGIS for Desktop) had a cursor implementation within its Arc Macro Language (AML) that supported working with related records.