Speedup/Alternative to ArcGIS table truncation operation

Question

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?

blake.terhune · Answer

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

hhharsha36 · Answer

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.

blake.terhune · Answer

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.