Delete Rows in model builder hangs

[BugPie]

Does anyone else see issues with the Delete Rows GP tool hanging at 10.1?

We are using this GP tool within a model that updates records within a polygon SDE feature class. Our model uses a view which is a table of of new projects. We are creating a temp layer with "Make Feature Layer" using a little SQL query so that the new temp layer has only the records that are in the new project view. We are then utilizing the delete rows to delete all records matching the view in the SDE feature by way of the temp layer>delete rows.

The records are removed from our SDE feature class just fine. Our issue is in the time it takes to delete these records. Even when there are only 12 records to delete, it can take upwards of 7 minutes to run. Our SDE feature class is quite large (400K records) but they are indexed.  We did not have this type of lag with our 9.2 env running on SQL 2000, so I was wondering if anyone had an idea whether or not this is related to 10.1 or maybe the way our SDE has been setup in the 10.1 env. Which is running on a SQL 2008r2 release.

Cheers!

[DougKampe]

Does anyone else see issues with the Delete Rows GP tool hanging at 10.1?

We are using this GP tool within a model that updates records within a polygon SDE feature class. Our model uses a view which is a table of of new projects. We are creating a temp layer with "Make Feature Layer" using a little SQL query so that the new temp layer has only the records that are in the new project view. We are then utilizing the delete rows to delete all records matching the view in the SDE feature by way of the temp layer>delete rows.

The records are removed from our SDE feature class just fine. Our issue is in the time it takes to delete these records. Even when there are only 12 records to delete, it can take upwards of 7 minutes to run. Our SDE feature class is quite large (400K records) but they are indexed.  We did not have this type of lag with our 9.2 env running on SQL 2000, so I was wondering if anyone had an idea whether or not this is related to 10.1 or maybe the way our SDE has been setup in the 10.1 env. Which is running on a SQL 2008r2 release.

Cheers!

Yes. I'm running this process as an update for a layer with about 86000 polygons and it takes forever. This should be a much faster process. Normally, I would delete the feature, but it participates in topology and if I delete the layer I have to rebuild the topololgy.

[George_Thompson]

If you guys are just deleting all the features out of the table and not deleting a selection of the table, I would try to use the Truncate Table tool. This tool will removed all the records/rows in a table.

Truncate Table (Data Management): http://resources.arcgis.com/en/help/main/10.2/index.html#//00170000016z000000

*This will not work on a versioned data.......

Be sure to test this on a test geodatabase with data first. Not on your production.  🙂

Hope this helps.

-George

[BugPie · Accepted solution]

Thanks for the reply George. I should have been a better nerd and posted some of the results from our testing and ESRI calls from the last few months.

We had the same recommendation from our support rep. (truncate table tool) however this would not work for us, and luckily we tried this on beta first. We are only trying to remove a few records from our table based on a view into another system.

What we did discover is that there seems to be a bug, or at least bad performance at 10.1.1 with the delete rows or delete records GP tool when the storage type of the data is Geometry.  Our old SDE had been using the old ESRI SDEBinary storage type. When we moved to the new server and SQL release, we had not changed the default data type, so it went with the new Geometry storage type. I'm hoping this is fixed in 10.2. I really do not want to deal with changing our data back to SDEBinary!!  Cheers

From ESRI Tech Support "One important change that took place between 9.2 and 10.1, would be the Default Storage type: The default storage type used to be SDEBinary earlier, which has changed to GEOMETRY starting from 10.1."