Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have written this script that recurses through all feature classes in a given set of feature datasets and deletes all their features. It appears to do the job but it runs pretty slowly. Is there anything i'm doing wrong here or are there any obvious ways to speed things up?

I'm using DeleteFeatures_management to do the deed. DeleteRows_management also seems to work.

import sys
import os
import arcpy
from arcpy import env
import datetime
import getpass

try:
    passwd = getpass.getpass("Enter the sde user password: ")

    sdeConnectionFileDir = os.environ.get("TEMP")
    databaseName = ""
    fileName = "temp.sde"

    # Delete any pre-existing SDE connection file.
    fullPath = sdeConnectionFileDir + '\\' + fileName
    if os.path.exists(fullPath):
        os.remove(fullPath)

    # Create temporary SDE connection file.
    arcpy.CreateArcSDEConnectionFile_management (
        sdeConnectionFileDir, fileName,
        "sdeserver", "5151", "",
        "DATABASE_AUTH", "my_sde_user", passwd,
        "SAVE_USERNAME", "SDE.DEFAULT", "SAVE_VERSION"
    )

    env.workspace = fullPath

    # ArcPy status codes.
    returnCodes = {'WARN' : 0, 'INFO' : 1, 'ERROR' : 2}

    featureDatasets = []
    featureDatasets.extend(arcpy.ListDatasets("dataset1*"))
    featureDatasets.extend(arcpy.ListDatasets("dataset2*"))
    featureDatasets.extend(arcpy.ListDatasets("dataset3*"))

    list = '[%s]' % ', '.join(map(str, featureDatasets))
    response = raw_input("\n***** WARNING!!! ***** \nAll data will be deleted from all feature classess in the following datasets: \n\n" + list + "\n\n |--> Type DELETE to begin removal: ")
    if response == "DELETE":
        print "\nStarted: " + str(datetime.datetime.now()) + "\n"
        for dataset in featureDatasets:
            print "Processing dataset: " + dataset
            for fc in arcpy.ListFeatureClasses("*", "ALL", dataset):
                rowCount = int(arcpy.GetCount_management(fc).getOutput(0))

                if rowCount > 0:
                    print "  -- Processing feature class: " + str(fc) + " (" + str(rowCount) + " rows)"
                    #arcpy.DeleteRows_management(fc)
                    arcpy.DeleteFeatures_management(fc)

        print "\nCompleted: " + str(datetime.datetime.now())

except Exception as e:
    if arcpy:
        arcpyErrors = arcpy.gp.getMessages(returnCodes['ERROR'])
        if arcpyErrors:
            sys.stderr.write(arcpyErrors + "\n")
    sys.stderr.write(str(e) + "\n")
    sys.exit(1)

EDIT --

I put some performance timers in the script and here is the data:

  • Time to retrieve datasets: 0:00:01.254000
  • Total Feature Classes: 1682
  • Total Feature Classes with Data: 124
  • Total Features Processed: 190222
  • Total Run Time: 3 hours, 16 minutes

The breakdown:

Feature dataset --> list feature class calls:

* AVG   0:00:02
* MIN   0:00:01
* MAX   0:00:07
* COUNT 40
* TOTAL 0:01:08

Feature count calls (majority of the time):

* AVG   0:00:06
* MIN   0:00:01
* MAX   0:00:16
* COUNT 1682
* TOTAL 2:41:00

Feature deletion calls (reduced because only feature classes with rows are processed):

* AVG   0:00:17
* MIN   0:00:02
* MAX   0:03:22
* COUNT 124
* TOTAL 0:34:31
share|improve this question

3 Answers 3

up vote 11 down vote accepted

Which part of the script is actually taking up most of the time? There are about 5 other steps going on before you actually start deleting stuff.

You might want to break your script down into bite-sized tests. For example, instead of creating a temporary connection file, listing a bunch of datasets, listing their contents, counting their records, and then finally actually doing what you want to do (deleting features), just pass in a single feature class with a premade connection file to DeleteFeatures and see how long that takes.

If that performs acceptably then create another test to time the next potential trouble spot: counting rows. And another for listing feature classes within a feature dataset, and yet another for listing feature datasets within a geodatabase.

If, on the other hand, DeleteFeatures does not perform acceptably, then at least we know where the problem is. In that case I would be more inclined to look at how your geodatabase is designed:

  • Are any of your feature datasets versioned? When using versioning there exists an additional pair of A (adds) and D (deletes) tables for each versioned table, and when you delete features you aren't deleting records in the base table, you are adding records to the D table. This will take much longer than if it was not versioned.

  • Since your feature classes all seem to be in feature datasets, do they participate in geodatabase behavior such as a topology or geometric network? When you add/remove/modify features participating in geodatabase behavior there is a lot more overhead.

  • Also note that contrary to popular belief, feature datasets are not designed to be used as an organizational tool:

    Feature data sets exist in the geodatabase to define a scope for a spatial reference. All feature classes that participate in topological relationships with one another (e.g., a geometric network) must have the same spatial reference. Feature data sets are a way to group feature classes with the same spatial reference so that they can participate in topological relationships with each other.

    To most users, feature data sets also have a natural organizational quality, much like a folder on a file system. Since for many GIS applications the majority of the data has the same spatial reference, the temptation to group large numbers of feature classes into feature data sets is irresistible.

    Feature data sets, however, are not free. When you open a feature class contained in a feature data set to look at its properties or draw or query it in ArcCatalog™, ArcMap™, or a custom application, all of the other feature classes in that feature data set are also opened. This is done because updates to a feature class in a feature data set can potentially ripple to other feature classes in the feature data set that participate in topological relationships.

    From: Multiuser Geographic Information Systems with ArcInfo 8 (April 2000)

    So that might be another source of overhead even if they do not participate in a topology or geometric network.

Beyond the arcpy DeleteFeatures/DeleteRows commands:

  • If you have SDE administration command access you could use:

    sdetable -o truncate -t <tablename>

    This issues truncate table commands to your DBMS so it should be much faster, but note that this ignores geodatabase behavior.

  • Using ArcSDESQLExecute to issue TRUNCATE TABLE commands directly (again bypassing geodatabase behavior), but this is very trouble-prone as you would need to issue one for each table that makes up a feature class (base, F, S, I, A, D, etc.) Failing to do this carefully and correctly could leave your data in an inconsistent state.

share|improve this answer
    
This is great advise. I'll put some timers in for each operation and post the results. At least from the feedback so far it sounds like i'm using the appropriate ArcPy API methods. I'm still learning the ArcPy API so I was concerned there was some other method I should be using. –  Andy Arismendi Jun 18 '12 at 16:30
    
As far as arcpy goes I think DeleteRows and DeleteFeatures are about the best you can do but I added some other options that may be worth looking into at least if this is time-critical. –  blah238 Jun 18 '12 at 20:05
    
I added some performance metrics above. There are 1682 feature classes. Though on average counting only took 1/3 of the time of deleting. Granted the delete operations only processed feature classes with features. I'll try removing the count and just pass all feature classes to DeleteRows_management and see how that pans out. I'll also try sdetable using the truncate option. –  Andy Arismendi Jun 19 '12 at 9:59
2  
Yikes, 1682 feature classes?! I would definitely look into an alternative workflow to deal with that much data. When you start talking about hundreds of tables you should really be on the back end working with the DBMS directly IMO. Have your DBAs cook something up in PL/SQL or something :) –  blah238 Jun 19 '12 at 10:22
    
Only 124 feature classes actually had features, the rest were empty. BTW you just gave me an idea! Perhaps creating the SDE connection file using an Oracle direct connect will help improve performance. I'll give that a try next. –  Andy Arismendi Jun 19 '12 at 10:31

If upgrading to ArcGIS 10.1 (now released) is an option then I just found this in the What's New in ArcGIS 10.1 PDF:

New tool to delete all rows from a table

The TruncateTable geoprocessing tool in the Tables toolset can be used to delete all rows from a table or feature class. You should use the Truncate Table tool instead of the Delete Rows tool when you want to delete all rows from a table or feature class.

Its online help can be found here.

share|improve this answer
    
This is so much faster than DeleteRows –  nmpeterson Mar 19 '13 at 16:30

Why not just delete the feature datasets themselves with arcpy.DeleteFeatures_management(dataset)? If you still need the feature dataset to exist, you could simply recreate it after it's been deleted.

share|improve this answer
    
I need to keep the feature datasets and their feature classes around because they are not quickly re-created due to the SDE/Oracle combination and there are quite a few feature classes. I'm trying to delete the features as fast as possible. –  Andy Arismendi Jun 18 '12 at 3:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.