cpicciri

Need to speed up performance of update cursor.

Discussion created by cpicciri on Nov 8, 2010
Latest reply on Nov 22, 2010 by cpicciri
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);
            }
        }

Outcomes