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.
Best Answer
We had this same problem with really sucks. The MXD tries for 15 minutes to find the old server before giving up. We got around this problem by giving a new server the old SDE server's IP address. The new server has nothing to do with GIS or databases. However, the MXD tries to connect then gives up right away once it sees that the SDE data source is no longer there. The MXD opens quickly with the broken layer icons so you can fix the data source on your own.