DeleteFeatures Behavior Changed in ArcSDE 10.1 / SQL Server 2012

RandyKreuziger — Fri, 05 Sep 2014 23:12:45 GMT

DeleteFeatures no longer deletes all records at one time which it's supose to do when the input is a feature class or table! We upgraded from ArcGIS 10.0 / SQL 2008 to ArcGIS 10.1 / SQL 2012 so I don't know which caused the change. In 10.0 this was carried out all at once in like a truncate on the database side, but after upgrading it's taking forever now. Through SQL Profiler I can see that the records are being deleted in batches of about 150 records at a time, so what use to take about 2 to 3 seconds now takes over 30 minutes for a feature class containing a 1,000,000 records. Is there any fix for this?

BTW, this is similar to a bug introduced back in 9.3.1

https://community.esri.com/message/16613?sr=search&searchId=27c4082a-5048-443b-ab3e-d38219e3b633&searchIndex=4#16613

-- sample from SQL profiler --

declare @p1 int

set @p1=8003

exec sp_prepexec @p1
output,NULL,N'DELETE FROM GEOLIB_STAGE.DBO.TRANS WHERE OBJECTID IN
(8678,149031,781870,529051,850941,146097,548880,540263,435454,354365,529268,582084,247165,795180,559851,414179,446673,660244,490564,517760,142856,241123,695475,471813,307298,412080,719441,332785,592142,194167,188071,810130,824122,400725,570745,21179,375132,466956,743512,188859,661765,372015,794739,91259,138852,136984,883971,444122,485764,713000,134614,347483,503788,44012,840195,132928,875866,156734,523141,402599,752233,603552,330094,513428,53304,131004,803002,129594,498844,667558,90466,357945,633048,672060,479776,608211,820966,126915,265315,440638,677618,464150,236562,34384,749617,733983,658454,506931,764976,301637,532466,872429,19165,7489,252175,120581,409177,543996,163044,438198,534060,262530,872814,185194,325948,81350,576719,110589,181631,55689,398198,13743,348374,705618,417351,115767,853417,30666,733162,858712,256363,177822,683846,622061,827987,854389,200153,859952,871,296910,516844,385774,118935,665057,36949,631680,118086,428021,716597,783347,813725,878892,436661,336825,755693,225822,339504,355226,784398,584838)'

select @p1

Re: DeleteFeatures Behavior Changed in ArcSDE 10.1 / SQL Server 2012

AsrujitSengupta — Fri, 05 Sep 2014 23:32:48 GMT

The 'Delete Features" or "Delete Rows" have always been deleting one record at a time. So what you may be observing is a performance degradation w.r.t. the tool.

Is your data registered as Versioned? If not, you may try the Truncate table tool. It deletes all the records in a table\fc in one go.

ArcGIS Help 10.1

Note: Try to post your question with proper tags to get a wider audience.

enterprise gis sde‌sde‌managing_data‌geodatabase‌arcgis sde‌enterprise geodatabases (arcsde)‌gp tools‌

Re: DeleteFeatures Behavior Changed in ArcSDE 10.1 / SQL Server 2012

RandyKreuziger — Fri, 05 Sep 2014 23:37:02 GMT

No it's not versioned. I just saw the Truncate Tool under Data Management Tools | Tables but it's not under Features so I missed it. (But I am disappointed I have to modify all my scripts and models after ESRI changed the behavior of a tool.)

Thank you.

topic DeleteFeatures Behavior Changed in ArcSDE 10.1 / SQL Server 2012 in Data Management Questions

DeleteFeatures Behavior Changed in ArcSDE 10.1 / SQL Server 2012

Re: DeleteFeatures Behavior Changed in ArcSDE 10.1 / SQL Server 2012

Re: DeleteFeatures Behavior Changed in ArcSDE 10.1 / SQL Server 2012