ArcPy performance issues with an UpdateCursor when sorting

Question

I'm hoping that someone will be able to help me here. I have written a short ArcGIS Python script (mainly cobbled together from other examples) that sorts a feature class ascending based on two fields (route, and sequence). A third field is then populated with an ascending number. An If block restarts the numbering for each route. The problem is performance. The process is very slow when running on my test data (~8000 records). I plan on running this on 100000 records, so any help or suggestions to improve speed will be appreciated. Thanks. James

import arcpy,os
from arcpy import env

inTable = arcpy.GetParameterAsText(0)      #  Input feature class

#  set variables for route and sequence starting values
route_previous = ""
route_current = ""
new_sequence = 1

#  create an update cursor that will append ascending order sequence number to the Service_Type field
#  sorts ascending by Route and Sequence
rows = arcpy.UpdateCursor(inTable,'','','','Route A;Sequence A')

for row in rows:
    route_current = row.Route
#  if the current service location is on the same route as the previous
    if route_current == route_previous:
        row.setValue("Service_Type",new_sequence)
        new_sequence += 1
        rows.updateRow(row)
#  if the current service location is on a different route to the previous
    else:
        new_sequence = 1
        row.setValue("Service_Type",new_sequence)
        new_sequence += 1
        route_previous = row.Route
        rows.updateRow(row)

#  delete the update cursor
del rows

Answer 1

If you are using Arcpy 10.0, there are some things you can do to boost performance.

An UpdateCursor is expensive. The effort behind the scenes to perform data conversion between python and COM data types is costly, approximately half a second for most computers per record.

On the other hand read and write calculations are quicker when writing to an in-memory dataset, then copying this data to your output dataset.

You could do 2 things to speed it up.

1. Copy all your data to a python dictionary and perform your data aggregation there, then write out the data to your output dataset.
2. Copy your data into an in-memory database, perform the operations there then copy the data to your output location.

Performance can depend on they data set you are using. In order from most efficient to least efficient dataset to use is as follows:

1. Shapefile
2. File geodatabase
3. Personal geodatabase

If you are using Arcpy 10.1, use the Data Access libraries, your performance should increase extensively out of the box.