topic Re: DeleteFeatures_management or truncate? in Data Management Questions

DeleteFeatures_management or truncate?

J_P_ — Wed, 16 Nov 2011 12:37:55 GMT

Can someone help to settle this for me --

Is arcpy.DeleteFeatures_management performing an SDE truncate?

The help for the DeleteFeatures_management python tool could be interpreted either way:
"If you want to delete all features from an ArcSDE feature class, use this tool against the ArcSDE feature class (not a layer) without a selection in order to perform a database truncate rather than a row by row deletion of features to delete all the features. The database truncate operation is much faster when deleting all the features in a feature class."

Our sys admin thinks that using SQL truncate may perform a faster delete on SDE features than using DeleteFeatures_management but I'm not so sure that DeleteFeatures_management is not already performing a truncate based on the help manual.

Is there anyone with experience between the two methods of removing records in SDE that could talk about the difference between the two?

Many thanks!

Re: DeleteFeatures_management or truncate?

VinceAngelo — Wed, 16 Nov 2011 13:36:05 GMT

There's always the option of doing a trace to see what is going on, but if the doc says it
does a TRUNCATE, and the best possible solution is a TRUNCATE, then it's pretty likely
that the command is doing a TRUNCATE (and it's likely to apply it to any parallel tables
manifested by different storage types, too).

Using SQL on objects registered with the geodatabase should be a rare event; running a
SQL TRUNCATE on a versioned table would be a very bad thing indeed.

- V

Re: DeleteFeatures_management or truncate?

J_P_ — Wed, 16 Nov 2011 13:56:29 GMT

Thank you.

Could you expand on why using SQL on objects registered with the geodatabase should be a rare event?

These tables are not versioned so we're good there; but if you have time to give the short answer to why not use SQL truncate I would appreciate it too.

Re: DeleteFeatures_management or truncate?

VinceAngelo — Wed, 16 Nov 2011 14:11:55 GMT

I already included one reason -- some storage types are implemented as multiple tables.

The closer you come to simple feature classes (aka ArcSDE layer, aka database table),
the more flexibility you have in using SQL for nominal management. Don't get me wrong --
You *can* always use SQL (I do, albeit rarely), but if you do it wrong, you'll be validating
your backup integrity more than most sites.

- V

Re: DeleteFeatures_management or truncate?

J_P_ — Wed, 16 Nov 2011 14:46:48 GMT

Great, thanks!