topic Re: Slow C# query performance vs VBA in ArcObjects SDK Questions

Slow C# query performance vs VBA

CarlosPiccirillo — Wed, 27 Jul 2011 16:08:31 GMT

Hi everyone,

I am writing an stand alone C# application and I am having a performance issue. The following code was converted from VBA literally line by line. I am doing a spatial query between two layers. The VBA code runs in between 10 and 15 minutes. The C# code below takes just over 3 hours to do the same query on the same layers as the VBA code. The code below doesn't even start ArcMap, just accesses things directly. I also tried changing the code a bit to start ArcMap in code and then run the code but it still takes the same amount of time.

Obviously, I am missing something/doing something wrong but I can't figure it out. Anyone have any ideas?

Thanks,
Carlos

public static void AppsInBoundary()
{ 
    try
    {
 IFeatureLayer pAppLayer = null;
 IFeatureLayer pEnsLayer = null;
 IFeatureCursor pEvgCursor = null;
 IFeatureCursor pAppCursor = null;
 IFeature pAppFeature = null;
 IFeature pEnsFeature = null;
 IArea pFeatureArea;
 int featureCount = 0;
 int appFieldIndex = 0;
 string geometryFieldName = string.Empty;

 //Get a reference to the two layers involved in the query.
 pAppLayer = GetLayerReference("FILEGDB", "App_Merge", Scratch_Local + "temp.gdb", null) as IFeatureLayer;
 pEnsLayer = GetLayerReference("SDE", "RIM.HYHDB_BSN_EVRGLDS_AREA", null, "gerrpsde") as IFeatureLayer;

 //Make sure layers being queried have the same projection.
 //Otherwise, topological operations later in the code (DetermineOverlapArea) fail.
 SpatialReferenceCheck(pAppLayer, pEnsLayer);

 //Find index of app no field in app_merge layer so query can run faster. 
 appFieldIndex = pAppLayer.FeatureClass.FindField("APP_NO");

 //Select only polygons that have a basin name.
 IQueryFilter pEnsQueryFilter = new QueryFilterClass();
 pEnsQueryFilter.WhereClause = "basin_name <> ' '";

 pEvgCursor = pEnsLayer.Search(pEnsQueryFilter, true);
 pEnsFeature = pEvgCursor.NextFeature();

 ISpatialFilter pAppSpatialFilter = new SpatialFilterClass();

 while (pEnsFeature != null)
 {
     m_Label = pEnsFeature.get_Value(pEnsFeature.Fields.FindField("BASIN_NAME")).ToString();

     pAppSpatialFilter.Geometry = pEnsFeature.Shape;
     pAppSpatialFilter.GeometryField = "Shape";
     pAppSpatialFilter.SpatialRel = esriSpatialRelEnum.esriSpatialRelIntersects;

     pAppCursor = pAppLayer.Search(pAppSpatialFilter, true);
     pAppFeature = pAppCursor.NextFeature();

     while (pAppFeature != null)
     {
  if (pAppFeature.get_Value(appFieldIndex) != DBNull.Value)
  {
      m_AppNo = Convert.ToString(pAppFeature.get_Value(appFieldIndex));
      pFeatureArea = pAppFeature.Shape as IArea;
      m_AppArea = pFeatureArea.Area;

      //Set relation type, in or adjacent.
      if (DetermineOverlapArea(pAppFeature, pEnsFeature) == true)
   m_Relation = "AI";   //Application In
      else
   m_Relation = "AA";   //Application Adjacent

      //If app_no is not null, proceed, otherwise skip it.
      if (m_AppNo != "")
      {
   //Check if app status is "no response", if it is not, proceed.
   if (AppStatusCheck(m_AppNo) == true)
   {
       GetAppInfo(m_AppNo);
       PopAppCmgenTables(m_AppNo, m_Relation);
   }
      }

      pAppFeature = pAppCursor.NextFeature();

      //Clear values.
      m_AppNo = string.Empty;
      m_AppArea = 0;

  }   //APP_NO != DBNull.Value
     }   //pAppFeature != null

     pEnsFeature = pEvgCursor.NextFeature();

     //Clear values.
     m_AppNo = string.Empty;
     m_Relation = string.Empty;
     m_Label = string.Empty;
 }   //pEnsFeature != null
    }
    catch (Exception ex)
    {
 LogError(ex.StackTrace, ex.Message, "AppsInBoundary", null);
    }   
}

internal static ILayer GetLayerReference(string layerType, string featureClassName, string path, string serverName)
{
    try
    {
 IWorkspaceFactory pWorkspaceFactory = null;
 IFeatureWorkspace pFeatureWorkspace = null;
 IFeatureClass pFeatureClass = null;

 if (layerType == "SDE")
 {
     IPropertySet pPropertySet = new PropertySetClass();
     pPropertySet = GetSdePropertySet(serverName, null);
     pWorkspaceFactory = new SdeWorkspaceFactoryClass();
     pFeatureWorkspace = pWorkspaceFactory.Open(pPropertySet, 0) as IFeatureWorkspace;
 }
 else if (layerType == "FILEGDB")
 {
     pWorkspaceFactory = new FileGDBWorkspaceFactoryClass();
     pFeatureWorkspace = pWorkspaceFactory.OpenFromFile(path, 0) as IFeatureWorkspace;
 }
 else if (layerType == "PGDB")
 {
     pWorkspaceFactory = new AccessWorkspaceFactoryClass();
     pFeatureWorkspace = pWorkspaceFactory.OpenFromFile(path, 0) as IFeatureWorkspace;
 }

 pFeatureClass = pFeatureWorkspace.OpenFeatureClass(featureClassName);
 IFeatureLayer pFeatureLayer = new FeatureLayerClass();
 pFeatureLayer.FeatureClass = pFeatureClass;
 return pFeatureLayer;
    }
    catch (Exception ex)
    {
 LogError(ex.StackTrace, ex.Message, "GetGroupLayer", null);
 return null;
    }
}

Re: Slow C# query performance vs VBA

NeilClemmons — Wed, 27 Jul 2011 17:24:25 GMT

.NET code using ArcObjects will almost always run slower than code written in VBA/VB6. This is because .NET does not directly support COM and VBA/VB6 does. Any call into a COM object in .NET must pass through a runtime callable wrapper (RCW) that acts as a middleman between COM and .NET. Naturally, this slows things down. The more ArcObjects calls you make, the slower it will run. That being said, there are several things you may be able to do to speed things up. The first is to make sure your data is properly indexed. Aside from a spatial index on the feature class, all attribute fields involved in the process should also be indexed. When you execute your queries, setting the Subfields property on the query/spatial filter will help. You should set this property to include only the fields that you need to be populated in the cursor. You should also review your logic to see if there is a way to reduce the number of queries you have to make. I don't know exactly what your code is doing but it looks like you are performing a spatial query on one layer for each feature in the other layer that has a basin name. Could you possibly combine the geometries of all features with the same basin name into a single spatial query? For instance, let's say there are 100 features in the layer with a single basin name. If you add these 100 geometries into a geometry bag and use it to execute a single spatial query then you will eliminate 99 spatial queries from the process. Of course, depending on what it is that you're doing with the query results you may not be able to do this but this is the type of thing you should look for. If all else fails, you may be better off writing this procedure in VB6 or C++ and compiling it into a library that you can then call from your .NET application.

Re: Slow C# query performance vs VBA

CarlosPiccirillo — Wed, 27 Jul 2011 17:47:05 GMT

Thanks for the reply Neil!

All the fields I query are indexed as I learned that lesson from you a couple of years ago. I will try your suggestions and see what happens.

In the case of the two layers I am querying, the outter cursor (basin name) layer has only 17 features and there are no duplicate values of basin name. The inner cursor (app_no) has 76,000+ features and they are already merged by app_no to minimize queries so I guess there is no performance improvement there.

I have very little VB6 experience, mostly using examples as reference for VBA or C# and no C++ so I will try to convert the code to VB inside of Visual Studio 2008 and see if it runs better there.

If you have any other suggestions, I'm all ears. Thanks again for your time!
Carlos

.NET code using ArcObjects will almost always run slower than code written in VBA/VB6. This is because .NET does not directly support COM and VBA/VB6 does. Any call into a COM object in .NET must pass through a runtime callable wrapper (RCW) that acts as a middleman between COM and .NET. Naturally, this slows things down. The more ArcObjects calls you make, the slower it will run. That being said, there are several things you may be able to do to speed things up. The first is to make sure your data is properly indexed. Aside from a spatial index on the feature class, all attribute fields involved in the process should also be indexed. When you execute your queries, setting the Subfields property on the query/spatial filter will help. You should set this property to include only the fields that you need to be populated in the cursor. You should also review your logic to see if there is a way to reduce the number of queries you have to make. I don't know exactly what your code is doing but it looks like you are performing a spatial query on one layer for each feature in the other layer that has a basin name. Could you possibly combine the geometries of all features with the same basin name into a single spatial query? For instance, let's say there are 100 features in the layer with a single basin name. If you add these 100 geometries into a geometry bag and use it to execute a single spatial query then you will eliminate 99 spatial queries from the process. Of course, depending on what it is that you're doing with the query results you may not be able to do this but this is the type of thing you should look for. If all else fails, you may be better off writing this procedure in VB6 or C++ and compiling it into a library that you can then call from your .NET application.

Re: Slow C# query performance vs VBA

NeilClemmons — Wed, 27 Jul 2011 17:58:29 GMT

Converting the code to VB.NET will not help as it will still run using COM Interop. It sounds as if the data and the query logic may not the problem. It could be the code inside your GetAppInfo and PopAppCmgenTables methods. That's where I'd start to look for ways to speed things up. How fast do the queries run if you comment out the calls to these two methods. You can put code at various places to calculate how much time it takes to execute blocks of code. I would do this to find where the bottleneck is and then look for ways to overcome it. If you post the code in those bottlenecks I or someone else may be able to offer suggestions.

Re: Slow C# query performance vs VBA

CarlosPiccirillo — Wed, 27 Jul 2011 18:39:25 GMT

I commented out both GetAppInfo and PopAppCmgenTables methods and stopped the code after 20 minutes because it took the same amount of time to get through 7 of 17 records as the original code so it looks like the bottle neck is somewhere in there.

I am including the code for both GetAppInfo and PopAppCmgenTables methods in case you see ways of improving them. GetAppInfo does a bunch of queries against Access tables and PopAppCmgenTables populates a few tables in another Access database.

I have to admit, GetAppInfo is quite large, convoluted and ugly. It's evolved over the years with patches upon patches to deal with quirky data out of my control. When I started converting it to C# from VBA, I thought about trying to redo the method as a clean start but decided against it because I know the original code works well and fast.

I will write out to a text file at the start and end of pieces of code with time stamps and see if I can narrow down the bottleneck.

I had to zip the code up because it was too large to fit in this window and too large to send as a plain text file, sorry.

Re: Slow C# query performance vs VBA

NeilClemmons — Sat, 11 Dec 2021 23:17:28 GMT

Use something like this to measure how long a block of code takes to execute.

        Dim startTime As Date = Now
        ' execute a block of code here
        Dim span As TimeSpan = Now.Subtract(startTime)
        Debug.Print("time to execute block:  " & span.Seconds & " seconds")

Just modify the print statement so you can identify what's what in the debug window.

Re: Slow C# query performance vs VBA

CarlosPiccirillo — Sat, 11 Dec 2021 23:17:30 GMT

Neil,

As it turns out, there are two bottlenecks, methods GetAppInfo and DetermineOverlapArea (below). DetermineOverlapArea calculates how much overlap two polygons have. If it's greater than 5%, we record it, otherwise, skip it. No idea why a simple area calculation would slow things down so much.

        public static bool DetermineOverlapArea(IFeature pFeature1, IFeature pFeature2)
        { 
            try
            {
                IArea pFeature1Area = pFeature1.Shape as IArea;
                IArea pFeature2Area = pFeature2.Shape as IArea;
                ITopologicalOperator2 pTopologicalOperator = pFeature2.Shape as ITopologicalOperator2;
                pTopologicalOperator.IsKnownSimple_2 = false;
                pTopologicalOperator.Simplify();

                IArea pIntersectArea = pTopologicalOperator.Intersect(pFeature1.Shape, esriGeometryDimension.esriGeometry2Dimension) as IArea;

                int appOverlap = Convert.ToInt32((pIntersectArea.Area / pFeature1Area.Area) * 100);
                int ensOverlap = Convert.ToInt32((pIntersectArea.Area / pFeature2Area.Area) * 100);

                if (appOverlap > 5 || ensOverlap > 5)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                LogError(ex.StackTrace, ex.Message, "DetermineOverlapArea", null);
                return false;
            }           
        }

Use something like this to measure how long a block of code takes to execute.
        Dim startTime As Date = Now
        ' execute a block of code here
        Dim span As TimeSpan = Now.Subtract(startTime)
        Debug.Print("time to execute block:  " & span.Seconds & " seconds")
Just modify the print statement so you can identify what's what in the debug window.

Re: Slow C# query performance vs VBA

NeilClemmons — Wed, 27 Jul 2011 19:56:09 GMT

A few things to try:

First, a method should only take what it really needs as parameters. In this case, you don't really need 2 features, you need 2 polygons. So I would modify the method signature to this:

public static bool DetermineOverlapArea(IPolygon polygon1, IPolygon polygon2)

When you call the method, pass in the feature geometries cast as polygons. Use ShapeCopy instead of Shape because you don't want your method altering the feature geometries.

Try skipping the geomtry simplification. The general idea here is that an unmodified feature geometry from a feature class will already be simple.

Remove the if/else block at the end and replace it with: return (appOverlap > 5 || ensOverlap > 5);
Probably not much time savings there but every bit counts.

Re: Slow C# query performance vs VBA

CarlosPiccirillo — Thu, 28 Jul 2011 12:56:00 GMT

Neil,

I tried your suggestions for speeding up method DetermineOverlapArea and it's just as slow as before. I tried passing in the geometries both as polygons as well as geometries and there is no improvement at all. It takes 10 minutes to get through 5 records.

A few things to try:

First, a method should only take what it really needs as parameters. In this case, you don't really need 2 features, you need 2 polygons. So I would modify the method signature to this:

public static bool DetermineOverlapArea(IPolygon polygon1, IPolygon polygon2)

When you call the method, pass in the feature geometries cast as polygons. Use ShapeCopy instead of Shape because you don't want your method altering the feature geometries.

Try skipping the geomtry simplification. The general idea here is that an unmodified feature geometry from a feature class will already be simple.

Remove the if/else block at the end and replace it with: return (appOverlap > 5 || ensOverlap > 5);
Probably not much time savings there but every bit counts.

Re: Slow C# query performance vs VBA

JeffreyHamblin — Thu, 28 Jul 2011 16:00:10 GMT

I commented out both GetAppInfo and PopAppCmgenTables methods and stopped the code after 20 minutes because it took the same amount of time to get through 7 of 17 records as the original code so it looks like the bottle neck is somewhere in there.

If I am reading this correctly... If you comment out those two methods and the operation is still just as slow, then the bottleneck is not in those methods.

Re: Slow C# query performance vs VBA

CarlosPiccirillo — Thu, 28 Jul 2011 16:05:02 GMT

Aparently, I messed up doing the first test because when I repeated them later trying some of Neil's suggestion, the code took less than a minute. The bottleneck is definitely between GetAppInfo and DetermineOverlapArea. The biggest bottleneck seems to be DetermineOverlapArea. I've been trying alternative code all day and get the same slow results.

Re: Slow C# query performance vs VBA

NeilClemmons — Thu, 28 Jul 2011 19:07:07 GMT

The most expensive thing the method does is call the Intersect method. How long does it take for that one line of code to execute?

Re: Slow C# query performance vs VBA

AlexanderGray — Thu, 28 Jul 2011 20:45:48 GMT

How long do the convert.toint32 functions taking? If they are significant (type conversions are one thing slower because of the .Net interop) you can probably do without it and compare the doubles.

Re: Slow C# query performance vs VBA

CarlosPiccirillo — Mon, 01 Aug 2011 10:11:50 GMT

Neil, Alexander,

Sorry for the late reply. I was out Thursday afternoon and all day Friday.

Neil, you are correct, the bottleneck is the Intersect command in the DeterimeOverlapArea method. I'm trying to figure out what I can use in it's place that might be faster.

Alexander, the Convert.ToInt32 is very fast so that part of the code is okay although I will try your suggestion. Every little bit of speed increase helps.

Carlos

Re: Slow C# query performance vs VBA

NehaS1 — Wed, 26 Oct 2011 20:03:54 GMT

Carlos,
From my experience arcobjects should never be instantiated in every iteration of while loop if possible. In your case IArea pFeature1Area,IArea pFeature2Area,ITopologicalOperator2 pTopologicalOperator can be declared once globally and can be reused. You can use pTopologicalOperator.Boundary.SetEmpty(); to release memory

Re: Slow C# query performance vs VBA

NehaS1 — Wed, 26 Oct 2011 20:19:28 GMT

How many records are stored in pAppLayerand pEnsLayer? Is it sde?

Re: Slow C# query performance vs VBA

CarlosPiccirillo — Wed, 26 Oct 2011 20:37:35 GMT

Samham,

Thanks for the reply! pAppLayer is in a file geodatabase and pEnsLayer is in SDE.

Not sure I follow you when you say, "From my experience arcobjects should never be instantiated in every iteration of while loop if possible."

Do you have an example of what you mean?

I will be out until Monday so my appologies ahead of time if I am late replying to any replies you post. Thanks again!

Carlos

How many records are stored in pAppLayerand pEnsLayer? Is it sde?

Re: Slow C# query performance vs VBA

NehaS1 — Wed, 26 Oct 2011 20:58:30 GMT

For example if pAppLayer has 100,000 records then you are iterating 100,000 times and creating 100,000 ITopologicalOperator2 pTopologicalOperator. Why not create one ITopologicalOperator2 pTopologicalOperator and reuse it?

Re: Slow C# query performance vs VBA

NehaS1 — Wed, 26 Oct 2011 21:00:07 GMT

When you comment the method how much time does it take to iterate through all the records?

Re: Slow C# query performance vs VBA

DubravkoAntonic — Fri, 28 Oct 2011 08:02:39 GMT

If you consider your app's performance is more valuable then time you spend for optimizing then this effort is valid one, in other cases let the user wait.

There are two way approach.
ne is to use VB code create COM object that you will refer from you project and use it when needed and you'll be very close to ArcMap speed.

Second:
There try to use IRelationalOperator instead of ITopologicalOperator sinc you are using just a part of ITopologicalOperator functions. Please try to measure how much you will be faster. Neil is again totally right, this time about Simplify, people use this very often. Since you are not modifying geometry the is no need for this operation.
If you are satisfied with the speed, then you can consider using managed C++. There you'll save interop operation time.
http://help.arcgis.com/en/sdk/10.0/arcobjects_net/conceptualhelp/0001/0001000000wm000000.htm

Regards