Note: While this question has an answer, any further tips for optimizing a cursor process would be greatly appreciated. I will be monitoring for any updates.
Currently, my boss (who works in Avenue) and I (working in Python) are both attempting to solve the same problem. Rather, we have both solved it, but the speed at which our solutions operate are... disjointed, to say the least. What his script processes in 2 hours can take mine up to 6. The only real difference in syntax and implementation in logic comes from 3.x's Bitmaps and 10.x's Cursors. We both:
1) Store values from Table 1.
2) Use those values to query a row in Table 2.
3) Store values from Table 2 for insertion into Table 3 as a new row.
In both scripts, these processes are completed in two nested loops. Before I start digging into the wonderful world of code optimization, is this an expected occurrence when comparing Avenue script performance to Python? This is not the first time his scripts have greatly outperformed mine in terms of operation time, so I would like to know if there is something I should be aware of before I crucify myself for horrid scripting.
Here's my script sans extraneous bits:
import arcpy import time import sys import os def recordfindcopy(inFile,query,outFile): findRecord = arcpy.SearchCursor(inFile,query) for record in findRecord: copyRecord = arcpy.InsertCursor(outData) # <--- D'oh! (See answer) field = record.FIELD copy = copyRecord.newRow() copy.FIELD = field copyRecord.insertRow(copy) StreetsFileList = [r"Path", r"Path"] for sfile in StreetsFileList: inStreets = sfile inTable = r"Path" outData = r"Path" fsaEntry = arcpy.SearchCursor(inTable) for row in fsaEntry: id = row.ID sQuery = "ID = %s " % (str(id)) recordfindcopy(inStreets,sQuery,outData)
EDIT: Given some of the comments so far, I wonder if there might be a better way to do this via joins, although I am dubious given the brobdingnagian (word of the day!) size of the tables. The heart of the processing is to append information from one table to any matching records in a second table and create a third table only containing the important fields. I wanted to try this using SDE, but that appears to not be an available option. Thoughts? I apologize if my questions are always so involved, but I'm trying to get to the bottom of a long-standing annoyance.
Answered: Jakub's simple suggestion alone decreased the processing time from 30 seconds per 500 records to 3 seconds per 500 records. Re-initiating the insert cursor on every insert slowed things down considerably (obviously). While this may not be the most optimization one can do for this process when put up against ArcView 3.x's speed, it is enough for my purposes at this time. Further suggestions are very welcome!