Need to speed up performance of update cursor.

[CarlosPiccirillo]

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);
            }
        }

[AlexanderGray]

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.

[CarlosPiccirillo]

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.

[JamesCrandall]

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.

[CarlosPiccirillo]

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.

[JamesCrandall]

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

[JamesCrandall]

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

[CarlosPiccirillo]

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