ArcGIS Copy Features tool extremely slow when exporting to ArcSDE?

Question

I have about 10 points in a file gdb that I am attempting to copy over to an sde feature set. In arcpy and in model builder it takes about a minute to execute. Do you know of any tips to speed this up? In SSMS I can query the table almost instantaneously, so there must be a bottleneck somewhere that I am not aware of, since the network is close to zero latency. In the progress bar, it reads "Writing Features 1" for the majority of the time, then seems to complete the rest of the task almost immediately.

If it is really supposed to be this slow, I would at least like to have some idea of what it is actually doing during this time. The file is only a few kilobytes total, so I assume this cannot be normal.

Update: I was trying this operation over a site-to-site VPN. It is a fast connection, so there is still no real reason it should take a full minute to upload 10 points. I tried this on a local connection (local to the SQL Server), and the same operation took 2 seconds. It seems that CopyFeatures has some sort of bug when working through a VPN connection. Any ideas on how I could find a workaround (I need to be able to copyfeatures over the network with a VPN connection)?

Answer 1

I just found the same thing. I have a script that geocodes and does some analysis on a collection of address data using a file GDB as the workspace, then loads the results back to SDE (running on SQL Server 2008R2) into four separate feature classes, each in a different projection. (Note: we are storing the shape data using the built-in SQL Server GEOMETRY data type rather than SDE_BINARY; our past experience has shown that there is a substantial performance hit for this choice but there were compelling reasons to do it anyway.)

For each CopyFeatures call, the process is loading the result from the file GDB to a different SDE feature dataset. The destination feature class may already exist in the SQL database, so SDE must first check and delete the existing feature class and then re-create it if need be. So, the following things have to happen:

• query the GDB tables to find relationships
• drop the existing feature class (delete all existing column and table registration data in the process, as well as checking for any DB constraints and relationships)
• create the new feature class table
• register the table with the gdb table registry and sde_layers
• register the columns with the gdb column registry
• transfer the data

Having just experienced this same issue (it took over 11 minutes to copy ONE feature from a file GDB to an SDE feature class...) I started digging. I ran SQL Server trace while my script was executing and looked for anything that took more than 10 milliseconds to execute.

What I found was that under the hood SDE appears to be continually looking up information from the GDB tables, at least one query of which is really slow. I found over 130 instances where a query (see below) that was basically variations on the code below execute, taking between 9 and 15 seconds per execution. The sum of that wait time was over half an hour.

select  <db name>.sde.GDB_Items.ObjectID,  <db name>.sde.GDB_Items.UUID,  <db name>.sde.GDB_Items.Type,  <db name>.sde.GDB_Items.Name,  <db name>.sde.GDB_Items.PhysicalName,  <db name>.sde.GDB_Items.Path,  <db name>.sde.GDB_Items.Url,  <db name>.sde.GDB_Items.Properties,  <db name>.sde.GDB_Items.Defaults,  <db name>.sde.GDB_Items.DatasetSubtype1,  <db name>.sde.GDB_Items.DatasetSubtype2,  <db name>.sde.GDB_Items.DatasetInfo1,  <db name>.sde.GDB_Items.DatasetInfo2,  <db name>.sde.GDB_Items.Definition,  <db name>.sde.GDB_Items.Documentation,  <db name>.sde.GDB_Items.ItemInfo,  <db name>.sde.GDB_Items.Shape  from  <db name>.sde.GDB_Items , <db name>.sde.GDB_ItemRelationships , <db name>.sde.GDB_ItemRelationshipTypes   where (<db name>.sde.GDB_ItemRelationships.OriginID = '{072E894A-AF77-4062-940F-351A7EB96318}' and <db name>.sde.GDB_ItemRelationships.DestID = <db name>.sde.GDB_Items.UUID and <db name>.sde.GDB_ItemRelationshipTypes.UUID = <db name>.sde.GDB_ItemRelationships.Type and <db name>.sde.GDB_ItemRelationshipTypes.IsContainment = 1)

My next task is to look at whether we can change our process, either by changing the design or by selecting a different tool to get the job done.

Answer 2

What geoprocessing tool do you for transferring data to ArcSDE? Do you run Copy Features tool or Append?

Geoprocessing tool do not have control over the actual performance of the ArcSDE server, the geodatabase, or the database. Most of the tuning should happen in the database and during the design and implementation. It is in general very expensive operation when you load data into database and thus this has to planned accordingly.

There is a great Esri help page which describes some of the geoprocessing considerations when using ArcSDE, worth reading. My general suggestion is always ask if it is really required to write to ArcSDE. I always use file geodatabase first and use ArcSDE only when need to move data to production; however, I never use ModelBuilder for that, only Python scripts or ArcGIS script tools. From the Help page:

Intermediate data is created when running models and scripts that have many steps to come to a final output. If your data is in ArcSDE and you also send all your intermediate data to ArcSDE, you could create a lot of network traffic/contention as well as cause the load on the database server to go up unnecessarily. It is recommended that all intermediate data be sent to a file geodatabase or, in the case of simple feature classes, to an in-memory feature class.

I am not aware of your ArcSDE configuration, but you might talk with your DBA or GIS administrator to see if there are any spatial indices problems, which has been a common problem for many people. From the Help:

Spatial indexes are used in ArcGIS to quickly locate features in feature classes. Whenever a feature is inserted or deleted, the spatial index needs to be updated. When creating a new output, geoprocessing defers the creation of the new feature classes' spatial index until after all data has been loaded. When editing existing feature classes, there are two options for when the spatial index is updated. You can leave the spatial index in place and let it be updated after every edit, or you can remove the spatial index prior to performing the edits and have it be updated once after all edits are complete.

Talking about using core GP tools, you might consider switching to ArcPy/Python which seems to have a bit fewer levels of abstraction comparing to ModelBuilder and thus often performs faster when the best coding practice is in place. For updating your feature class by loading new features, for instance, consider using arcpy.da module > UpdateCursor.

If Python is not an option for you, consider testing other methods of data load such as Load Objects tool in ArcMap editing session or Truncate Table GP tool + Append GP tool when loading data into an existing feature class.

Answer 3

Try using feature to points. I used feature class to feature class and it took much longer than it should. Class for an hour and a half to less than a minute.