ArcView 3.x Avenue Bitmaps (Tabs?) vs. ArcView 10 Python Cursors

Question

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!

Answer 1

I'll assume you're using ArcPy, or arcgisscripting circa 9.3. Either way the techniques here will speed up your processing....maybe better than your bosses.

The first thing is performing look up and insertions with any medium other than memory is going to slow your processes. Avenue is optimized to work quickly, and uses a C\C++ (correct me if I'm wrong) code base which is inherently quicker at IO than most other languages. Python is quick too (just as quick) except where there are overheads in hooking into c libraries to perform operations, such as ArcPy or arcgisscripting.

So try this first:
1. Copy the tables you need to use into memory using the methods -

• gp.CopyFeatures("Path to featureclass\FeatureclassName", "'in_memory'\FeatureclassName") - for feature classes and;
• gp.CopyRow("Path to featureclass\FeatureTableName", "'in_memory'\FeatureTableName") - for tables in to a 'in_memory' feature class or table.
  
  This will allow you to use memory like RAM disk, and save you a lot of disk thrashing. You can also create a feature class or table in memory by substituting the FeatureDataset parameter with 'in_memory'.

Use python containers as much as possible. This will also increase speed.

Finally the order for efficiency in reading and writing information for ESRI formats is

1. Shapefile (sad but true)
2. Personal Geodatabase
3. File Geodatabase
4. ArcSDE (even with direct connect it's slower)

Give these suggestions a try, as I'm trying to compile a list of things that work here on gis.stackexchange.com see here

Answer 2

I bet it's not that Avenue is faster than Python, but that ArcView3 is faster than ArcGIS (at what you are trying to do).

Since from the sound of it this is essentially a non-spatial exercise you may want to experiment with accessing the database tables directly (e.g. don't use arcpy) with something like dbfpy or odbc (haven't tried either of them myself). Personally I've found commandline ogr2ogr of the gdal/ogr suite to be orders of magnitude faster than equivalent transactions in arcgis. I've only dipped lightly into OGR query abilities though, and I haven't built anything using only the python bindings so I don't know if that speed carries over.

Answer 3

I am not new to programming but very new to Python so take this with a grain of salt...

copyRecord = arcpy.InsertCursor(outData)

Should not the insert cursor be set prior to the For Next loop? Seems to me that if the path to the "out" data is stored in the "outData" variable then then it does not need to be reset every time you iterate. I would think that this should speed things up significantly.

Answer 4

This is not an especially helpful answer at the moment, but wait for ArcGIS 10.1. At this year's esri dev summit we were told that arcpy 10.1 cursor support has been completely rewritten and is significantly faster. During the plenary there was a claim of speed improvements of around 8x.