How to improve performance of nested search cursors?

Question

I have three nested SearchCursors. It works correctly but with slow performance. There are some solutions to improve the searchcursor performance like dictionary but i don't know how to use it with conditional statements. The code is as below.How to get rid of nested loops and improve performance?

with arcpy.da.SearchCursor(parameters[1].valueAsText,["Station","Code"],where_clause = sql_exp1) as cur:
    for row in cur:
        if  row[0] != None:
            with s arcpy.da.SearchCursor(parameters[2].valueAsText,["Code","Name","Code1"]) as curt:
                for rowt in curt:
                    if rowt[0] == row[1]:
                        with  arcpy.da.SearchCursor(parameters[0].valueAsText,["Name","Origin","Destination","Code1"]) as curl:
                            for rowl in curl:
                                if rowl[0] == rowt[1] and rowl[3] == rowt[2]:
                                    listnumber.append(rowl[0])
                                    linecodeFinal.append(rowl[3])
            codelinestr = ','.join(str(b) for b in linecodeFinal)

Answer 1

I have to keep posting that I will never use embedded search cursors ever for relating tables to each other. Only dictionaries should be used to do what you are doing. Performance will increase 100 fold over a 3 level set of embedded search cursors, since dictionaries use random access from data stored in memory which is immediate, verses cursor's and sql that access the table linearly by repeated reading the data from disk which is incredibly sloooow. See my Blog on Turbo Charging Data Manipulation with Python Cursors and Dictionaries

Load each of the embedded cursors to their own dictionary using the related field as the key value using a style shown below that add all other fields into a list for the values. Then just run a cursor on the master data source and look up the key value in the first related table. Use a for loop on the first related table's value list to get the look up for the second level table key and then append the returned value of that table to the list you are building.

The conditional statements are virtually identical to what you have in your code and you should not think of dictionary logic any differently. The examples in my blog already show this kind of conditional logic. It occurs in the line that reads: if keyValue in valueDict:

Anyway, the logic of your code is less obvious than the logic of the dictionary code I use. I have tried to equate what you have done to how it should be done using dictionaries, but I may not have kept track of the list for loops that I needed to build. I made the Name Code1 value key of the two embedded cursors a tuple dictionary key for the most embedded level to make the matching easier. Not sure why you have origin and destination at that level, since they are not used. The second dictionary is basically using (Name, Code1) key as the equivalent of a multi-field composite key in a table relationship (something my blog discussed in general terms, but did not show in an example).

I have revised the code to use Paul's suggestion of using the defaultdict(list). The code has not been tested so some debugging is still most likely needed, but it illustrates the overall approach.

from time import strftime  
  
print "Start script: " + strftime("%Y-%m-%d %H:%M:%S")  
  
import arcpy  
  
sourceFC1 = parameters[2].valueAsText  
  
sourceFieldsList1 = ["Code","Name","Code1"] 
  
valueDict1 = defaultdict(list)  
with arcpy.da.SearchCursor(sourceFC1, sourceFieldsList1) as searchRows:  
    for searchRow in searchRows:  
        keyValue = searchRow[0]  
        valueDict1[keyValue].append((searchRow[1], searchRow[2]))  
                        
sourceFC2 = parameters[0].valueAsText  
  
sourceFieldsList2 = ["Name","Code1","Origin","Destination"] 
  
valueDict2 = defaultdict(list)  
with arcpy.da.SearchCursor(sourceFC2, sourceFieldsList2) as searchRows:  
    for searchRow in searchRows:  
        keyValue = (searchRow[0], searchRow[1])
        valueDict2[keyValue].append(searchRow[2:])
  
updateFC = parameters[1].valueAsText 
  
updateFieldsList = ["Station","Code"]  
  
with arcpy.da.UpdateCursor(updateFC, updateFieldsList, where_clause = sql_exp1) as updateRows:  
    for updateRow in updateRows:  
        if updateRow[0] != None:
        # store the Join value of the row being updated in a keyValue1 variable  
            keyValue1 = updateRow[1]  
            # verify that the keyValue1 is in the Dictionary  
            if keyValue1 in valueDict1:  
                # transfer the values stored under the keyValue1 from the dictionary to the updated fields.  
                for keyValue2 in valueDict1[keyValue1]: 
                    print(keyValue2)
                    if keyValue2 in valueDict2: 
                        listnumber.append(keyValue2[0])
                        listcodeFinal.append(keyValue2[1])
            codelinestr = ','.join(str(b) for b in linecodeFinal)
del valueDict1
del valueDict2  
  
print "Finished script: " + strftime("%Y-%m-%d %H:%M:%S")  
print "Finished script: " + strftime("%Y-%m-%d %H:%M:%S")