da.InsertCursor super slow

[RandyBonds_Jr_]

I have the following code that will take around 12 Hours in insert 7,000 rows. I have no idea why it is so slow. 

the routeRunRows is just a dictionary from an oracle database (cursor.fetchAll() using the oracledb library). When I run the loop without the arcpy.da.InsertCursor it take seconds (which makes sense). Any help would be appreciated.

for row in routeRunRows:
    if (x%1000) == 0:
        message = str(x) + ' Processed...'; show_py_message()
    x += 1
    with arcpy.da.InsertCursor("d:/projects/SolidWaste/SolidWaste.sde/SolidWaste.DBO.cayServiceOrders", ["RouteNumber","RouteDescription","StartDate","RouteOrder","StreetAddress","ServiceItemType","SerialNumber","X","Y","ItemUse","RouteNote","AccountNumber","AccountStatus","ServiceItemID","SHAPE@XY"]) as insertSO:
        routeNote = " "
        if row[10] is not None:
            routeNote = row[10][:120]
        insertSO.insertRow((row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9],routeNote,row[11],row[12],row[13],(row[7],row[8])))

[DavidPike · Accepted solution]

It's extremely likely the cause is instantiating the cursor for each key in the dictionary, that's an expensive operation each time.

Simply instantiate the cursor once, and have the dictionary iteration below/within the cursor.

fc = "d:/projects/SolidWaste/SolidWaste.sde/SolidWaste.DBO.cayServiceOrders"

with arcpy.da.InsertCursor(fc, [
    "RouteNumber", "RouteDescription", "StartDate", "RouteOrder",
    "StreetAddress", "ServiceItemType", "SerialNumber", "X", "Y",
    "ItemUse", "RouteNote", "AccountNumber", "AccountStatus",
    "ServiceItemID", "SHAPE@XY"
]) as insertSO:

    x = 0
    for row in routeRunRows:
        if (x % 1000) == 0:
            message = f"{x} Processed..."
            show_py_message()
        x += 1

        routeNote = row[10][:120] if row[10] else " "

        # Insert row
        insertSO.insertRow((
            row[0], row[1], row[2], row[3], row[4], row[5], row[6],
            row[7], row[8], row[9], routeNote, row[11], row[12],
            row[13], (row[7], row[8])
        ))

[RandyBonds_Jr_]

I'll give that a try. I'm curious if you could explain why that makes a difference? Isn't it just a string?

[RandyBonds_Jr_]

Never mind, I read that wrong. I see what you're talking about. Thanks!

[DavidPike]

Ah ok haha that's good.  I'll add my thinking anyway, but I could be wrong.

1.  Every cursor establishes a new data connection.  This can be painful depending on where your data resides e.g. if it's in an SDE over VPN etc.

2.  Data schema is read and validated every time, again and again.  Nowhere near as bad as point number 1, but it's not exactly going to make things faster.
   

[RandyBonds_Jr_]

Final update on this thing. Thanks A TON! The script now runs in 10 seconds!