Performance in Writing to an Oracle SDE Instance

[EdwardBlair]

Hello ???
I???ve got a process that???s built in C# using ArcObjects v10.1 that reads data from a feature class in a source File Geodatabase, applies updates to attributes and sometimes the shape, and writes a new feature to a target feature class in another File geodatabase.  This works fine.  As a typical example in one case it reads and writes about 120,000 features in 4 minutes.

Now I need to update the process to write to an Oracle SDE instance.  Overall it works, its just much slower.  For example, the process that took 4 minutes to process 120,000 records in an FGDB takes 18 minutes in Oracle.    This alone would be OK.  My issue is that I???ve got some processes that take hours in the FGDB.

As part of the updates for Oracle I???ve switched to use of IFeatureClassWrite::Write instead of IFeature::Store that I was using in the FGDB.  My understanding is that the FeatureClassWrite doesn't trigger events like Store.  However this doesn???t seem to make much difference.  Here???s an overview of what the process does.

// Get the target workspace
IWorkspace targetWorkspace = workspaceFactory.OpenFromFile(connectionString, 0);
IWorkpaceEdit workspaceEdit = (IWorkspaceEdit) targetWorkspace;

// Start editing
workspaceEdit.StartEditing(false);
workspaceEdit.StartEditOperation();

// Open the source and target feature classes
IFeatureWorkspace sourceFeaWorkspace = (IFeatureWorkspace) sourceWorkspace;
IFeatureClass sourceClass = sourceFeaWorkspace.OpenFeatureClass(sourceClassName);

IFeatureWorkspace feaWorkspace = (IFeatureWorkspace) targetWorkspace;
IFeatureClass targetClass = feaWorkspace.OpenFeatureClass(targetClassName);
IFeatureClassWrite fcWrite = (IFeatureClassWrite) targetClass;

// Put the target class in load-only mode and apply a schema lock
IFeatureClassLoad fcLoad = (IFeatureClassLoad) targetClass;
ISchemaLock schemaLock = (ISchemaLock) targetClass;
schemaLock.ChangeSchemaLock(esriSchemaLock.esriExclusiveSchemaLock);
fcLoad.LoadOnlyMode = true;

// Read from the source and write to the target
IFeature targetFea = null;

IQueryFilter queryFilter = new QueryFilter();
IFeatureCursor feaCur = sourceClass.Search(queryFilter,true);
IFeature sourceFea = feaCur.Next();
while (sourceFea != null)
{
[INDENT]targetFea = targetClass.CreateFeature();

// Update the shape and attributes

// Store the target feature
   fcWrite.WriteFeature(targetFea);

        sourceFea = feaCur.Next();
[/INDENT]
}

// Stop editing and release lock
workspaceEdit.StopEditing(true);
schemaLock.ChangeSchemaLock(esriSchemaLock.esriSharedSchemaLock);
fcLoad.LoadOnlyMode = false;


I???ve also tried grouping features into an ISet and using IFeatureClassWrite::WriteFeatures.  No difference in performance.  I???ve also tried using an InsertCursor.  No difference in performance.

Is it just the case that writing directly to an Oracle SDE instance is 4 to 5 times slower than writing to an FGDB?   Any insights or suggestions at all would be much appreciated.

Thanks,
Ed

[NeilClemmons]

Using IFeature.Store is the slowest way to save changes to a gdb if you're making large numbers of edits.  Instead, use IFeatureclass.Update to get an update cursor and call IFeatureCursor.UpdateFeature where you would normally call Store.  For creating new features, use IFeatureClass.Insert to get the cursor and IFeatureCursor.InsertFeature.  Also, before you start, place the feature classes you're writing to into LoadOnly mode using IFeatureClassLoad.  Take them out of LoadOnly mode when you're done.

[EdwardBlair]

Using IFeature.Store is the slowest way to save changes to a gdb if you're making large numbers of edits.  Instead, use IFeatureclass.Update to get an update cursor and call IFeatureCursor.UpdateFeature where you would normally call Store.  For creating new features, use IFeatureClass.Insert to get the cursor and IFeatureCursor.InsertFeature.  Also, before you start, place the feature classes you're writing to into LoadOnly mode using IFeatureClassLoad.  Take them out of LoadOnly mode when you're done.

Neil -

Thanks for the reply.  As my post says, I'm not using IFeature.Store.  I've tried using an insert cursor, as well as WriteFeature and WriteFeatures from the IFeatureClassWrite interface.  All are equally slow.  My example also shows that I'm using LoadOnly mode.

Forgot to mention that I've removed all indexes from the target class -- except that for ObjectID.  And I've remove all relationships.  The target is also a simple point feature class - no network or topology defined.

Ed

[AlexanderGray]

A file gdb will almost always outperform an entreprise gdb.  The model is much, much simpler, you are just writing to a file on your hard drive.  As long as on-access scanner isn't killing the performance it will be much faster.

For the code side, I would consider not using an edit session or at least not use an edit operation if you can get away with it.  The option to turn off the undo/redo doesn't always work (Note that the supression of undo/redo logging is not supported for remote database workspaces.)  I would consider using an insert cursor and calling flush every so many records (100? something to experiment with.)  CreateFeature for sure will create an objectId for each call when it is called so, right there sql statements are being generated one at the time...

I suggest you look at the following link and avoid creating the features one by one.  There are more direct ways of doing this.  If you need to process the data, you can do that in the fgdb and then load.
http://help.arcgis.com/en/sdk/10.0/arcobjects_net/conceptualhelp/index.html#//0001000003rp000000

From the database side, a lot can be done tweak the performance.  The trick is to know what is holding you up.  Do you have versioning?  How big is the user temp space?  What is going on with the roll back log?  Do you have replication?  These are some of the  many things you need to master to get performance out of your database. A good orace DBA can be invaluable in figuring out performance on the Oracle side. 

You can also consider using spatial SQL to insert the features directly through the SQL geodatabase API
http://resources.arcgis.com/en/help/main/10.1/index.html#//006z00000008000000

I have also dabbled in exporting recordsets into XML and importing them as a new featureclass in Oracle, maybe that can work for you. 

Variable size sample sets, some good metrics and some experimentation, I think you will find an efficient way of doing this.

[EdwardBlair]

A file gdb will almost always outperform an entreprise gdb.  The model is much, much simpler, you are just writing to a file on your hard drive.  As long as on-access scanner isn't killing the performance it will be much faster.

For the code side, I would consider not using an edit session or at least not use an edit operation if you can get away with it.  The option to turn off the undo/redo doesn't always work (Note that the supression of undo/redo logging is not supported for remote database workspaces.)  I would consider using an insert cursor and calling flush every so many records (100? something to experiment with.)  CreateFeature for sure will create an objectId for each call when it is called so, right there sql statements are being generated one at the time...

I suggest you look at the following link and avoid creating the features one by one.  There are more direct ways of doing this.  If you need to process the data, you can do that in the fgdb and then load.
http://help.arcgis.com/en/sdk/10.0/arcobjects_net/conceptualhelp/index.html#//0001000003rp000000

From the database side, a lot can be done tweak the performance.  The trick is to know what is holding you up.  Do you have versioning?  How big is the user temp space?  What is going on with the roll back log?  Do you have replication?  These are some of the  many things you need to master to get performance out of your database. A good orace DBA can be invaluable in figuring out performance on the Oracle side. 

You can also consider using spatial SQL to insert the features directly through the SQL geodatabase API
http://resources.arcgis.com/en/help/main/10.1/index.html#//006z00000008000000

I have also dabbled in exporting recordsets into XML and importing them as a new featureclass in Oracle, maybe that can work for you. 

Variable size sample sets, some good metrics and some experimentation, I think you will find an efficient way of doing this.

Alexander -

Thanks very much for your comments and suggestions.  While investigating some of the options you put forth I had a breakthrough.  I tried to use the ArcCatalog simple data load and found it to fail.  At that point I realized my classes were registered for use with the ArcFM software -- custom GeoObjects.  I unregistered the classes and found performance improve dramatically. 

I'm not quite at the speed of the FGDB -- but pretty close to it.

Thanks again - you pointed me in the right direction!

Ed

[EdwardBlair]

Just to be clear.  What made the big difference in performance for me were two things:

1.  I reverted my target feature classes to core Esri objects.  They had inadvertently been left defined as custom object classes as required by the ArcFM application.

2.  Made use of a FeatureBuffer and InsertCursor to create the new features.  I flush the buffer after each 2,000 features.

With these two changes the process that completes in 4 minutes for 120,000 features in a File Geodatabase completes in (about) 4 minutes in the Oracle database.

Thanks again for the responses.
Ed