[GIS] ArcGIS Copy Features tool extremely slow when exporting to ArcSDE

arcgis-desktoparcpyenterprise-geodatabasemodelbuilderperformance

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)?

Best Answer

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.

Related Question