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.
-= Keith Adams =- Systems Analyst, Leidos
