InsertCursor Performance

[JanDuske]

Hi,

I am running a check routine on a big (oracle based) SDE database (100K's of entries in dozends of tables) and create new Pointfeatures with some additional Field entries according to that check.

I am using an InsertCursor for storing new features, because I've read that it can perform better than the FeaturClass.CreateFeature() / Feature.Store() aproach. However, it seems that my featureclass has some "complex" properties, as calling InsertCursor.InsertFeature() takes about 500 milliseconds for each point. Having to create about 30K Points, this results in a processing time of at least 6 hours, and that's just for storing the features, the check takes some time itself...

Does anyone have an idea on how to speed up the process? Maybe a direct method of writing new Rows into the Featureclasses' Table might speed up the whole thing... I'm afraid I don't have much oracle know how, is there a simple way to accomplish such an insert operation with some c# code?

[JamesCrandall]

Hi,

I am running a check routine on a big (oracle based) SDE database (100K's of entries in dozends of tables) and create new Pointfeatures with some additional Field entries according to that check.

I am using an InsertCursor for storing new features, because I've read that it can perform better than the FeaturClass.CreateFeature() / Feature.Store() aproach. However, it seems that my featureclass has some "complex" properties, as calling InsertCursor.InsertFeature() takes about 500 milliseconds for each point. Having to create about 30K Points, this results in a processing time of at least 6 hours, and that's just for storing the features, the check takes some time itself...

Does anyone have an idea on how to speed up the process? Maybe a direct method of writing new Rows into the Featureclasses' Table might speed up the whole thing... I'm afraid I don't have much oracle know how, is there a simple way to accomplish such an insert operation with some c# code?

You could try an IFeatureBuffer.  After you create the FeatureClass, you can then set your IFeatureBuffer to this empty FC, as well as implement the IFeatureCursor.  You'd do something like the following (this is JUST a snipit from a larger set of code used to create and populate a new FeatureClass from an ADO.NET DataTable):

Dim pFeatCur As IFeatureCursor
Dim pFeatBuf As IFeatureBuffer
pFeatBuf = pFeatureClass.CreateFeatureBuffer
pFeatCur = pFeatureClass.Insert(True)

'*Add the new point
 tmpPoint = New ESRI.ArcGIS.Geometry.Point
 tmpPoint.PutCoords((CDec(row.Item("longitude"))), CDec(row.Item("latitude")))

 '*Set the new point to match Projected CoordSys of SDE FeatureClass'
pGeo = tmpPoint
pGeo.SpatialReference = pSpRefWGS
pGeo.Project(pSpRefNAD83)

'*Create a point
pGeo.SnapToSpatialReference()
pFeatBuf.Shape = pGeo
pFeatCur.InsertFeature(pFeatBuf)

[JanDuske]

I am already using a FeatureBuffer with an InsertCursor (didn't mention that directly, sorry). I think the problem is, that the Featureclass I am working with has some complex features, which forces the InsertFeature function to fall back on the slower CreateFeature / Store functions (as the documentation tells me). I have tried both methods, and they both need the same time for storing a new feature.

[KirkKuykendall]

Did you try using IFeatureClassWrite?

[AlexanderGray]

Sounds like you are doing a lot of things right from an ArcObjects stand point.  IFeatureClassWrite or ITableWrite sound like a good bet now.  Also you can try unversioning the featureclass if it is versioned.  That will avoid the SDE delta tables overhead.  If that is not an option, there is some tuning you can do on Oracle and SDE to make it faster, sounds like the bottle neck is AO though, not Oracle.  You could also consider using the SDE APIs (C or Java) for bulk loading to by pass ArcObjects all together.

[JanDuske]

I've just tried the IFeatureClassWrite. Seems to be considerably faster indeed thanks for the hint!
Although it looks like a good option, I am getting a "The requested operation is invalid on a closed state" Exception when calling StopEditing() or StopEditOperation() after everything is done. Do I have to do something else to prevent this? Seems like some mechanism is locking the table.

[KirkKuykendall]

Did you try using it in conjunction with IFeatureclassLoad and ISchemaLock?
http://edndoc.esri.com/arcobjects/9.2/ComponentHelp/esriGeoDatabase/IFeatureClassLoad_Example.htm

[JanDuske]

After using an exclusive schema lock I don't get the error any longer, but the changes are not persisted either 😞