arcpy.da.InsertCursor running slow compared to append

[MichaelBarker]

I have a sql server spatial table of around 9M points which for backup purposes I'm scripting to create a copy in SDE.  I was initially truncating the SDE table and using append to re-populate which took around 3 hours.  To improve performance I read about using the da.Insert and search cursors and having implemented that do have it working, but it took around 8 hours.  I'm fairly new to using these cursors, so I maybe don't have this configured correctly, so any advise would be appreciated.  Sample of the code is below.

    sql_tb = "INPUT_SQL_TB"
    sde_fc = "OUTPUT_SDE_FC"
    with arcpy.da.InsertCursor(sde_fc,['FIELD1','FIELD2','SHAPE@XY']) as icur:
        with arcpy.da.SearchCursor(sql_tb,['FIELD1','FIELD2','SHAPE@XY']) as scur:
            for srow in scur:
                icur.insertRow(srow)
    del icur,scur

Thanks in advance for any feedback, Mike.

[JamesCrandall]

I'd research a bit more and look into another way to bulk insert into SDE.  Since it's SQL Server, you'd probably get far better performance with a Stored Procedure rather than doing it application-side.  Maybe "sde.st_geometry" table insert would be a starting point to look into?

(sorry I don't have any specific examples at the moment)

[BlakeTerhune]

When using Esri tools, I've always understood that append was the quickest way to do a mass insert. You could also try Copy Rows to see if that's any different.

[JoshuaBixby · Accepted solution]

Performance wise, I wouldn't be surprised if Append and Copy Rows were comparable, but there is the functional difference where Append requires an existing table/dataset/etc... while Copy Rows has to create a new one.  Since the OP's original workflow includes truncating a table instead of deleting it, there may be a requirement that prevents the use of Copy Rows.

In terms of the performance of ArcPy Data Access cursors versus ArcPy original/older cursors versus ArcObjects in .NET or C++, there is a post over at GIS StackExchange that discusses the topic:  How is the data access cursor performance so enhanced compared to previous versions?  Jason Scheirer​, one of the arcpy.da developers, explains how they improved the performance of the new cursors, but he also states that ArcObjects in .NET or C++ is still over twice as fast as arcpy.da cursors.  Since the Append tool is native ArcObjects, the OP's runtimes seem reasonable, or at least not surprising, when talking about bulk transferring of data.

[MichaelBarker]

Thanks all for the suggestions.  As mentioned by Joshua I had a need to retain the table thus the truncate and append and why copy rows wouldn't have suited, at least in this case.  After some further testing I did find that the da.insert was quicker with smaller tables (~10-100K records), but append won out on the large table (~9M records).