topic Re: Need to speed up performance of update cursor. in ArcObjects SDK Questions

Need to speed up performance of update cursor.

CarlosPiccirillo — Mon, 08 Nov 2010 18:04:07 GMT

Hi everyone,

I know this question has been asked many times in the past but I have tried every suggestion I found on the forums (been going through them for a week now) and have had little success in improving the speed of my code.

I have 16 SDE layers with a combined 75,000+ features. I have to go through every feature in every layer, run an Oracle query against one table and populate 49 fields in the SDE layer with this info.

The code below works fine but takes three hours to run and my boss wants the info updated every hour.

I've tried joining the table to each layer instead of doing the Oracle query and used ICalculator with IDisplayTable to update the fields but this took even longer, 9 hours. I've also tried search cursors, IFeature::Store and several others that I can't think of right now but they all have worse performance.

For the sake of brevity, I'm only showing three fields in the query/code and hard coded the layer. The full code is MUCH longer than it is here.

Does anyone have an idea how I can speed this code up?

Thanks,
Carlos

       public static void ProcessLayer()
        {
            try
            {
                Type appRefType = Type.GetTypeFromProgID("esriFramework.AppRef");
  object appRefObj = Activator.CreateInstance(appRefType);
                IApplication pApplication = appRefObj as IApplication;
                IMxDocument pMxDocument = (IMxDocument)pApplication.Document;
                IMap pMap = pMxDocument.FocusMap;
                IFeatureLayer pFeatureLayer = new FeatureLayerClass();
                pFeatureLayer = pMap.get_Layer(0) as IFeatureLayer;
                IFeatureClass pFeatureClass = pFeatureLayer.FeatureClass as IFeatureClass;

                //get the Workspace from the IDataset interface on the feature class
                IDataset pDataset = pFeatureClass as IDataset;
                IWorkspace pWorkspace = pDataset.Workspace;

                //Get a handle to the editor extension.
                UID pUID = new UIDClass();
                pUID.Value = "esriEditor.Editor";
                IEditor pEditor = (IEditor)pApplication.FindExtensionByCLSID(pUID);
                pEditor.StartEditing(pWorkspace);
                pEditor.StartOperation();

                IFeatureCursor pFeatureCursor = pFeatureClass.Update(null, false);

                int permitNoIdx = pFeatureCursor.FindField("PERMIT_NO");
                int appNoIdx = pFeatureCursor.FindField("APP_NO");
                int projectNameIdx = pFeatureCursor.FindField("PROJECT_NAME");
                string appNo = string.Empty;
                string permitNo = string.Empty;
                string projectName = string.Empty;

                OracleConnection dataConnection = new OracleConnection();
                dataConnection.ConnectionString = "Data Source=genp;" +
                                                  "Persist Security Info=True;" +
                                                  "User ID=pub;" +
                                                  "Password=pub;" +
                                                  "Unicode=True";

                dataConnection.Open();
                OracleCommand dataCommand = new OracleCommand();
                dataCommand.Connection = dataConnection;

                IFeature pFeature = pFeatureCursor.NextFeature();

                while (pFeature != null)
                {
                    appNo = pFeature.get_Value(appNoIdx).ToString();

                    dataCommand.CommandText = "SELECT PERMIT_NO, PROJECT_NAME " +
                                              "FROM reg_gis " +
                                              "WHERE app_no = '" + appNo + "'";

                    OracleDataReader dataReader = dataCommand.ExecuteReader();

                    while (dataReader.Read())
                    {
                        if (!dataReader.IsDBNull(0))
                        {
                            permitNo = dataReader.GetString(0);
                            pFeature.set_Value(permitNoIdx, permitNo);
                        }

                        if (!dataReader.IsDBNull(1))
                        {
                            projectName = dataReader.GetString(1);
                            pFeature.set_Value(projectNameIdx, projectName);
                        }
                    }
                    dataReader.Close();

                    pFeatureCursor.UpdateFeature(pFeature);
                    pFeature = pFeatureCursor.NextFeature();

                    //Clear values.
                    appNo = string.Empty;
                    permitNo = string.Empty;
                    projectName = string.Empty;
                }

                //Stop editing
                //pWorkspaceEdit.StopEditOperation();
                //pWorkspaceEdit.StopEditing(true);
                pEditor.StopOperation("Done");
                pEditor.StopEditing(true);

                //Release the Cursor
                System.Runtime.InteropServices.Marshal.ReleaseComObject(pFeatureCursor);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.StackTrace + "\r\n" + ex.Message, "Warning", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

Re: Need to speed up performance of update cursor.

RichardFairhurst — Mon, 08 Nov 2010 18:32:42 GMT

Looking at your code it appears that the performance hits are primarily due to having to to query each feature of the joined table more or less individually to create the link. Some sort of Array or List that could read and store all of the joined table records in memory once would be more efficient than hitting the on disk table through queries multiple times. 75,000 records total does not seem like it would exhaust your resources especially if the join table has much fewer records (or no more records). Choosing an approapriate Array, collection or list that gives you an efficient key to match each feature of your layers would be needed or you would need to sort the features to match the array sort so that you are not having to go backwards and forwards in the Array for a given layer. .Net has a variety of objects for storing arrays, lists or collections, so something should work (Possibly a List of Arrays or Structs would work). Anyway, that would be the basic approach I would investigate.

Re: Need to speed up performance of update cursor.

AlexanderGray — Mon, 08 Nov 2010 19:23:56 GMT

There a few things you can try but it is hard to tell what is the major bottle neck, the update cursor or the oracle queries. If you try the update cursor without the oracle query as a test (setting some dummy value) you may get a better idea where to focus.

As far as the update cursor is concerned, you can try removing the edit operation and edit session, that is like using the field calculator outside of an edit session (no undo) and you can try setting the recycling to true. You can also create a query filter with a null where clause but where you restrict the sub fields and pass it into the update method. Querying only the fields you need might help a little.

As far as oracle is concerned, perhaps querying all the rows into a ado.net table in local memory and then selecting the rows from there might be more efficient.

Re: Need to speed up performance of update cursor.

SteveFang — Mon, 08 Nov 2010 21:56:59 GMT

You have too much overhead doing your updates with ArcObjects and ADO .Net. If all you're doing is updating attributes than perhaps you should consider doing this operation in Oracle with PL/SQL. If you are concerned about versioning than look into the Oracle packages supplied by ESRI to deal with using SQL on versioned data. Just as a reference, I once updated a versioned SDE featureclass with 1.7+ million records in 5 to 6 hours using PL/SQL where it would of taken me days using ArcObjects.

Re: Need to speed up performance of update cursor.

CarlosPiccirillo — Tue, 09 Nov 2010 16:52:05 GMT

Alexander,

Thanks for your suggestions. To speed up testing, I reduced the number of records to 1,000 in my test layer and it appears that the bottle neck is the update cursor. Without the Oracle query and using dummy values, it took 2:35 minutes and with the Oracle query it took 2:45 minutes. Setting recycling to true had no effect and I could not remove the edit operation/session because I am using and update cursor which requires an edit session. The query with the sub fields had no affect either. The only thing I did not try that you suggested is the ado.net table in local memory because I do not know how to do that. Do you have an example in VBA/VB/.NET that I can see how to do this?

Carlos

There a few things you can try but it is hard to tell what is the major bottle neck, the update cursor or the oracle queries. If you try the update cursor without the oracle query as a test (setting some dummy value) you may get a better idea where to focus.

As far as the update cursor is concerned, you can try removing the edit operation and edit session, that is like using the field calculator outside of an edit session (no undo) and you can try setting the recycling to true. You can also create a query filter with a null where clause but where you restrict the sub fields and pass it into the update method. Querying only the fields you need might help a little.

As far as oracle is concerned, perhaps querying all the rows into a ado.net table in local memory and then selecting the rows from there might be more efficient.

Re: Need to speed up performance of update cursor.

CarlosPiccirillo — Tue, 09 Nov 2010 16:53:48 GMT

Steve,

Thanks for the reply, unfortunately, I am not familiar with PL/SQL at all. 😞

Carlos

You have too much overhead doing your updates with ArcObjects and ADO .Net. If all you're doing is updating attributes than perhaps you should consider doing this operation in Oracle with PL/SQL. If you are concerned about versioning than look into the Oracle packages supplied by ESRI to deal with using SQL on versioned data. Just as a reference, I once updated a versioned SDE featureclass with 1.7+ million records in 5 to 6 hours using PL/SQL where it would of taken me days using ArcObjects.

Re: Need to speed up performance of update cursor.

AlexanderGray — Tue, 09 Nov 2010 17:02:30 GMT

The biggest difference I have ever seen is removing the operation and edit session. You can try using the Ifeatureclasswrite interface but I am not confident you will get much gain. If the oracle stuff is only accounting for 6% of the operation's time, there is not much point focusing there, the best you can achieve is less than 6% gain. It is possible that the relation is not linear so testing the cursor without the oracle queries for more features might yield a different curve but again it looks like the wrong place to make gains.

Chump's suggestion looks much more likely to yield the 300+% gains you need.

Re: Need to speed up performance of update cursor.

MattMoyles — Tue, 09 Nov 2010 17:11:50 GMT

We had this same problem and the real bottleneck is if you use a .NET language there is a delay on the interop between COM and .NET. Each time you update a feature there is an interop delay between COM and the .NET wrapper.

If you use straight C++ COM you will see a HUGE performance increase. We were trying to update >100,000 records took almost an hour to run through the loop with .NET and a couple minutes with pure C++/COM.

Unfortunately you will not find much information out there about this in the ESRI documentation. There is one page: http://help.arcgis.com/en/sdk/10.0/arcobjects_net/conceptualhelp/index.html#//0001000000wm000000

It suggests you use managed c++, but I think you will find it is easier and faster to completely skip out on .NET and just use straight COM. Which is not well documented either. If you need some sample code to get started in C++ I can help you with that.

Re: Need to speed up performance of update cursor.

CarlosPiccirillo — Tue, 09 Nov 2010 17:17:20 GMT

Matt,

Thanks for the reply. Unfortunately, we are only allowed to develop code in C#. Can C++ be easily converted to C#?

Carlos

We had this same problem and the real bottleneck is if you use a .NET language there is a delay on the interop between COM and .NET. Each time you update a feature there is an interop delay between COM and the .NET wrapper.

If you use straight C++ COM you will see a HUGE performance increase. We were trying to update >100,000 records took almost an hour to run through the loop with .NET and a couple minutes with pure C++/COM.

Unfortunately you will not find much information out there about this in the ESRI documentation. There is one page: http://help.arcgis.com/en/sdk/10.0/arcobjects_net/conceptualhelp/index.html#//0001000000wm000000

It suggests you use managed c++, but I think you will find it is easier and faster to completely skip out on .NET and just use straight COM. Which is not well documented either. If you need some sample code to get started in C++ I can help you with that.

Re: Need to speed up performance of update cursor.

MattMoyles — Tue, 09 Nov 2010 17:27:28 GMT

Well the real issue is that there is an interop layer when accessing the underlying COM objects when you develop with a .NET language. You can write equivalent ArcObjects code in both C# and C++. C++ is prob. a little trickier since you have to watch your memory management.

You don't have to write your whole application in C++ though, what ESRI's article suggests doing is creating a small Managed C++ ArcObjects library and then loading and using it in your .NET C# application. If you could create a small managed C++ library with only a few of the functions doing the heavy lifting you could take a detour around your bottle neck. If you are stuck only using C# then you are unfourtantely out of luck with this approach.

Re: Need to speed up performance of update cursor.

AlexanderGray — Tue, 09 Nov 2010 18:45:17 GMT

If you know enough C++ or you have access to a C++ developer, you can always write a little proof of concept. If you find it is 10X faster it might be a lot easier to convince the decision makers with hard proof. If you only get small gains then you don't need to bother convincing people.

Re: Need to speed up performance of update cursor.

CarlosPiccirillo — Tue, 09 Nov 2010 19:01:38 GMT

Unfortunately, I do not know C++ at all or have access to a C++ developer. I do have C++ as part of my Visual Studio 2008 install however but am cluless on how to ever start coding in C++.

I've spent the last 15 months teaching myself C# and it's been a mighty steep learning curve switching from VBA.

If you know enough C++ or you have access to a C++ developer, you can always write a little proof of concept. If you find it is 10X faster it might be a lot easier to convince the decision makers with hard proof. If you only get small gains then you don't need to bother convincing people.

Re: Need to speed up performance of update cursor.

JamesCrandall — Sat, 11 Dec 2021 16:11:51 GMT

Carlos,

1. Instead of just updating the FeatureCursor, have you tried accomplishing this with an Update on an IFeatureBuffer? This should speed up the ArcObjects side of things.

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

pFeatBuf.Value(pFeatBuf.Fields.FindField("TheFieldName")) = CStr("blah")

pFeatCur.UpdateFeature(pFeatBuf)

...err something to that effect. That above is WAY untested there!

2. I agree that you if this is just updating attributes, then let a PL/SQL package handle it -- for just about all of my attribute updating, its almost always be faster if done on the server rather than the application tier. Get with your DBA to put together the SQL (sorry I can't help much as I deal mostly with SQLServer Stored Procedure development).

3. I am not sure about using an ADO.NET DataTable instead of the DataReader being faster. I think it'd actually be slower because there is more overhead in creating and filling a DataSet and DataTable compared to the DataReader. But don't mean to discourage you on that: you will need to setup an OracleDataAdapter, Fill a DataSet with the DataTable, then you can have access to it from there.

You are real close to doing this, just replace the DataReader with the DataAdapter.

http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracledataadapter.aspx

4. Last idea....

If this is not versioned data, and you cannot setup the PL/SQL package, then *perhaps* doing a straight-up .Update on a DataAdapter might be another option to increase speed. Letting the ADO.NET CommandBuilder generate the INSERT/UPDATE/DELETE commands might take away some performance, but you could mitigate this by coding these yourself.

http://msdn.microsoft.com/en-us/library/z1z2bkx2.aspx

Again, I don't have experience with Oracle db to say if this is even possible, but I don't see why it wouldn't work.

Re: Need to speed up performance of update cursor.

CarlosPiccirillo — Sat, 11 Dec 2021 16:11:53 GMT

Thanks James, I'll give your suggestions a try.

Carlos

Carlos,

1. Instead of just updating the FeatureCursor, have you tried accomplishing this with an Update on an IFeatureBuffer? This should speed up the ArcObjects side of things.
Dim pFeatCur As IFeatureCursor
Dim pFeatBuf As IFeatureBuffer
pFeatBuf = pFeatureClass.CreateFeatureBuffer
pFeatCur = pFeatureClass.Insert(True)

pFeatBuf.Value(pFeatBuf.Fields.FindField("TheFieldName")) = CStr("blah")

pFeatCur.UpdateFeature(pFeatBuf)
...err something to that effect. That above is WAY untested there!

2. I agree that you if this is just updating attributes, then let a PL/SQL package handle it -- for just about all of my attribute updating, its almost always be faster if done on the server rather than the application tier. Get with your DBA to put together the SQL (sorry I can't help much as I deal mostly with SQLServer Stored Procedure development).

3. I am not sure about using an ADO.NET DataTable instead of the DataReader being faster. I think it'd actually be slower because there is more overhead in creating and filling a DataSet and DataTable compared to the DataReader. But don't mean to discourage you on that: you will need to setup an OracleDataAdapter, Fill a DataSet with the DataTable, then you can have access to it from there.

You are real close to doing this, just replace the DataReader with the DataAdapter.

http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracledataadapter.aspx

4. Last idea....

If this is not versioned data, and you cannot setup the PL/SQL package, then *perhaps* doing a straight-up .Update on a DataAdapter might be another option to increase speed. Letting the ADO.NET CommandBuilder generate the INSERT/UPDATE/DELETE commands might take away some performance, but you could mitigate this by coding these yourself.

http://msdn.microsoft.com/en-us/library/z1z2bkx2.aspx

Again, I don't have experience with Oracle db to say if this is even possible, but I don't see why it wouldn't work.

Re: Need to speed up performance of update cursor.

JamesCrandall — Tue, 16 Nov 2010 17:33:39 GMT

Carlos,

Just had another idea, but again it's not tested....

From what I understand, you are just updating attributes. If you decide to attempt to perform the updates with ADO.NET's DataAdapter, then you might be able to use a portion of a class I've put up on ArcScripts: http://resources.esri.com/arcgisdesktop/dotnet/index.cfm?fa=codeGalleryDetails&scriptID=16946

What I am thinking here is that you could pass in the IFeatureLayer, use the code above to convert it to a DataTable, make your edits to the DataTable, then setup your DataAdapter and perform the .Update with the DataTable.

I'll post up some more code in the hopes it'll help you get started.

j

Re: Need to speed up performance of update cursor.

JamesCrandall — Sat, 11 Dec 2021 16:11:56 GMT

Follow up with some code. Here is a function I have in a DataAccessLayer(DAL) Class for one of my solutions/implementations that integrates a non-spatial SQLServer2005 database that is basically acting as complex attribtutes for a parcel layer.

This particular function is updating that non-spatial SQLServer database (which again is related to parcels by way of the "GISPID" or "PID" fields). What you'll see here is that I am passing in a DataTable "inDT" which is then used in the DataAdapter.Update(inDT) method. What is important to note is that the SELECT Command of the DataAdapter produces what is found in the DataTable being passed in.

You'll also see that I have manually setup the INSERT/UPDATE/SELECT/DELETE Commands because I am utilizing StoredProcedures I have written specifically for each of those Commands. The ADO.NET DataSet/DataTable keeps track of any modifications you make to it, so if you are doing updates/deletes on the DataTable the .Update on the DataAdapter will handle updating the database.

Anyway, this is for SQLServer I know, but shouldn't be that much different for Oracle and you'll just need to setup your DataAdapter a bit differently (as I've already posted above).

The main reason why all of this might be faster doing the updating is because of as agray1 mentioned: no need to Start/Stop and Edit Session. Again this all hinges on simply updating attributes and not the features themselves though!

Good Luck, hope this helps.

Public Function UpdateIgnoreRecs(ByVal inDT As DataTable) As DataTable

        Dim Selcmd As SqlCommand = New SqlCommand()
        Selcmd.CommandText = "Storms_Excps_IgnoreRec_Get"
        Selcmd.CommandType = CommandType.StoredProcedure

        'setup INSERT Command for DataAdapter
        Dim ins_Cmd As SqlCommand = New SqlCommand()
        ins_Cmd.CommandText = "sido.Storms_Excps_InsertIgnoreRec"
        ins_Cmd.CommandType = CommandType.StoredProcedure
        ins_Cmd.Parameters.Add("@ParcelID", SqlDbType.NVarChar, 15, "ParcelID")
        ins_Cmd.Parameters.Add("@ExcpType", SqlDbType.NVarChar, 50, "ExcpType")
        ins_Cmd.Connection = Me.sqlCn

        Dim paramPkIDupd As New SqlParameter()
        paramPkIDupd.ParameterName = "@PkID"
        paramPkIDupd.SqlDbType = SqlDbType.Int
        paramPkIDupd.Direction = ParameterDirection.Input
        paramPkIDupd.SourceColumn = "PkID"
        paramPkIDupd.SourceVersion = DataRowVersion.Original


        'setup UPDATE Command for DataAdapter
        Dim upd_Cmd As SqlCommand = New SqlCommand()
        upd_Cmd.CommandText = "sido.Storms_Excps_UpdateIgnoreRec"
        upd_Cmd.CommandType = CommandType.StoredProcedure
        upd_Cmd.Parameters.Add("@ParcelID", SqlDbType.NVarChar, 15, "ParcelID")
        upd_Cmd.Parameters.Add("@ExcpType", SqlDbType.NVarChar, 50, "ExcpType")
        upd_Cmd.Parameters.Add(paramPkIDupd)
        upd_Cmd.Connection = Me.sqlCn

        paramPkIDupd = New SqlParameter()
        paramPkIDupd.ParameterName = "@PkID"
        paramPkIDupd.SqlDbType = SqlDbType.Int
        paramPkIDupd.Direction = ParameterDirection.Input
        paramPkIDupd.SourceColumn = "PkID"
        paramPkIDupd.SourceVersion = DataRowVersion.Original

        'setup DELETE Command 
        Dim del_Cmd As SqlCommand = New SqlCommand()
        del_Cmd.CommandText = "sido.Storms_Excps_DeleteIgnoreRec"
        del_Cmd.CommandType = CommandType.StoredProcedure
        del_Cmd.Parameters.Add("@ParcelID", SqlDbType.NVarChar, 15, "ParcelID")
        del_Cmd.Parameters.Add("@ExcpType", SqlDbType.NVarChar, 50, "ExcpType")
        del_Cmd.Parameters.Add(paramPkIDupd)
        del_Cmd.Connection = Me.sqlCn


        Dim ds As New DataSet()
        Dim da As New SqlDataAdapter

        da.SelectCommand = Selcmd
        da.UpdateCommand = upd_Cmd
        da.InsertCommand = ins_Cmd
        da.DeleteCommand = del_Cmd

        Try
            Using Selcmd
                Selcmd.Connection = Me.sqlCn
                da.Fill(ds)
            End Using

            Dim rct As Integer = CInt(ds.Tables(0).Rows.Count)
            da.Update(inDT)
            rct = CInt(ds.Tables(0).Rows.Count)

            Me.CloseConnection()
            da.Dispose()
            connSvc.Dispose()

            Return ds.Tables(0)

        Catch ex As Exception
            MsgBox(ex.ToString)
            connSvc.Dispose()
            Return Nothing
        End Try

    End Function

Re: Need to speed up performance of update cursor.

CarlosPiccirillo — Mon, 22 Nov 2010 17:29:23 GMT

Hi everyone,

Thanks a lot for all of your suggestions! Between all of them, I have been able to improve the performance of the update cursor to 1/3 of the original time!

The winning combination was to treat the feature class as a table and use Iworkspace::ExecuteSQL to update all the fields at once per record instead of updating each field separately.

Thanks again!!!
Carlos