topic Re: Speedup/Alternative to ArcGIS table truncation operation in Python Questions

Speedup/Alternative to ArcGIS table truncation operation

hhharsha36 — Mon, 06 Nov 2023 21:50:37 GMT

I have a table within my ArcGIS Pro application which I created using the below code:

table1 = arcpy.CreateTable_management(None, "TableName1") table2 = arcpy.CreateTable_management(None, "TableName2") arcpy.management.AddRelate("TableName1", "key1", "TableName2", "key2", "KeyRelation", "ONE_TO_MANY")

I am performing insert operations into this table every time I run a Python ToolBox which is done using the below code.

def truncate_place_table(): st1 = time.perf_counter() arcpy.TruncateTable_management("TableName1") arcpy.TruncateTable_management("TableName2") arcpy.AddMessage(f"Table Truncation time taken: {time.perf_counter() - st1}") return True def insert_to_table(): truncate_place_table() with arcpy.da.InsertCursor("TableName1", INSERT_FIELD_NAMES) as cursor: arcpy.AddMessage(f"insert_doc: {doc}") cursor.insertRow(doc) return True

The problem is that the table truncation takes more than a second to complete. If I do not include the table truncation operation, the `insertRow` command does not work.

Is there any way to speed up table truncation or alternative to flush and insert new records to table at a faster pace?

Re: Speedup/Alternative to ArcGIS table truncation operation

BlakeTerhune — Mon, 06 Nov 2023 22:01:56 GMT

Truncate is the fastest way to remove all rows from a table. Delete Rows is an alternative, but it is not faster than Truncate.

Maybe instead of using an insert cursor, see if Append is any faster.

Re: Speedup/Alternative to ArcGIS table truncation operation

hhharsha36 — Tue, 07 Nov 2023 01:25:49 GMT

Thanks for your fast response @BlakeTerhune

I tried Append as suggested and it consumed similar time to insert cursor.

As you pointed out, Truncate is faster in comparison to Delete Rows in my code, but it the one consuming the most time in my case.

I guess there is no other mechanism to speed up Truncate operation.

Re: Speedup/Alternative to ArcGIS table truncation operation

BlakeTerhune — Tue, 07 Nov 2023 14:15:19 GMT

If this is running on an enterprise geodatabase (RDBMS), there might be some specific database tuning to help. I'm not a DBA though.