topic Re: da.InsertCursor super slow in Python Questions

da.InsertCursor super slow

RandyBonds_Jr_ — Tue, 11 Mar 2025 23:51:31 GMT

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])))

Re: da.InsertCursor super slow

DavidPike — Wed, 12 Mar 2025 00:53:24 GMT

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]) ))

Re: da.InsertCursor super slow

RandyBonds_Jr_ — Wed, 12 Mar 2025 14:45:16 GMT

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

Re: da.InsertCursor super slow

RandyBonds_Jr_ — Wed, 12 Mar 2025 15:00:30 GMT

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

Re: da.InsertCursor super slow

DavidPike — Wed, 12 Mar 2025 16:14:10 GMT

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.

Re: da.InsertCursor super slow

RandyBonds_Jr_ — Thu, 13 Mar 2025 15:06:04 GMT

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