Why is this so so so slow??

JoshV · ‎12-21-2010

My below code is taking 15 minutes or more to run and I need to find a more efficient way. Any ideas please?? Thanks for any help

ICursor pCursor;
IRow pRow2;
ICursor pTableCursor;
pTableCursor = pTable.Search(null, true);  //pTable is a table inside of a local personal geodatabase

pRow2 = pTableCursor.NextRow();
IRowBuffer pRowBuff;
                        
pRowBuff = pNewTable.CreateRowBuffer();   //pNewTable is a table I just created inside a local File Geodatabase
pCursor = pNewTable.Insert(true);

do
  {
      //populate the row with values
      pRowBuff.set_Value((Int32)l_FCWell_ID, pRow2.get_Value((Int32)l_Well_ID));
      pRowBuff.set_Value((Int32)l_FCSidetrack_Number, pRow2.get_Value((Int32)l_Sidetrack_Number));
      pRowBuff.set_Value((Int32)l_FCAlternate_ID, pRow2.get_Value((Int32)l_Alternate_ID));
      pRowBuff.set_Value((Int32)l_FCMeasured_Depth, pRow2.get_Value((Int32)l_Measured_Depth));
      pRowBuff.set_Value((Int32)l_FCInclination, pRow2.get_Value((Int32)l_Inclination));
      pRowBuff.set_Value((Int32)l_FCAzimuth, pRow2.get_Value((Int32)l_Azimuth));
      pRowBuff.set_Value((Int32)l_FCE_W, pRow2.get_Value((Int32)l_E_W));
      pRowBuff.set_Value((Int32)l_FCN_S, pRow2.get_Value((Int32)l_N_S));
      pRowBuff.set_Value((Int32)l_FCTVD, pRow2.get_Value((Int32)l_TVD));
      pRowBuff.set_Value((Int32)l_FCState_Plane_Easting, pRow2.get_Value((Int32)l_State_Plane_Easting));
      pRowBuff.set_Value((Int32)l_FCState_Plane_Northing, pRow2.get_Value((Int32)l_State_Plane_Northing));
      pRowBuff.set_Value((Int32)l_FCSub_Sea_TVD, pRow2.get_Value((Int32)l_Sub_Sea_TVD));
                            
                            
      pCursor.InsertRow(pRowBuff);
      pRow2 = pTableCursor.NextRow();

 } while (pRow2 != null);

JoshV · ‎12-21-2010

Also I should mention that "pTable" inside of that personal geodatabase has about 205,000 records. If I produce the table pTable through VBA code in ArcMap, I can run through the above loop and multiple other loops in my function in less than 4 minutes. What gives??

JamesMacKay · ‎12-21-2010

Hi Josh,

If you want to kick it up a notch, try load-only mode. This is something used in File GDB and SDE (although the way they actually work differs slightly between the two). For File GDB, basically it suspends all indexes - when you're finished, you knock it out of load-only mode, rebuilding the indexes. It should make a significant difference.

IFeatureClassLoad featureClassLoad = (IFeatureClassLoad)pNewTable;
featureClassLoad.LoadOnlyMode = true;

// Get cursor, do inserts, etc., then...

featureClassLoad.LoadOnlyMode = false;

Cheers,
James

JoshV · ‎12-27-2010

Hi Josh,

If you want to kick it up a notch, try load-only mode. This is something used in File GDB and SDE (although the way they actually work differs slightly between the two). For File GDB, basically it suspends all indexes - when you're finished, you knock it out of load-only mode, rebuilding the indexes. It should make a significant difference.
IFeatureClassLoad featureClassLoad = (IFeatureClassLoad)pNewTable;
featureClassLoad.LoadOnlyMode = true;

// Get cursor, do inserts, etc., then...

featureClassLoad.LoadOnlyMode = false;
Cheers,
James

Hi James-

I tried your LoadOnlyMode method and set it in the correct spots as you suggested but it made no difference. Is there anyway I should be adding the data differently in my Do While loop? Should I be using IRowBuffer inside the Loop? Everything seems to be pointing to this loop as to why it's running so slow..

Please let me know any suggestions anyone. Many Thanks -Josh

Venkata_RaoTammineni · ‎12-27-2010

Hi James-

I tried your LoadOnlyMode method and set it in the correct spots as you suggested but it made no difference. Is there anyway I should be adding the data differently in my Do While loop? Should I be using IRowBuffer inside the Loop? Everything seems to be pointing to this loop as to why it's running so slow..

Please let me know any suggestions anyone. Many Thanks -Josh

while (pRow2 != null);
{
      //populate the row with values
      pRowBuff.set_Value((Int32)l_FCWell_ID, pRow2.get_Value((Int32)l_Well_ID));
      pRowBuff.set_Value((Int32)l_FCSidetrack_Number, pRow2.get_Value((Int32)l_Sidetrack_Number));
      pRowBuff.set_Value((Int32)l_FCAlternate_ID, pRow2.get_Value((Int32)l_Alternate_ID));
      pRowBuff.set_Value((Int32)l_FCMeasured_Depth, pRow2.get_Value((Int32)l_Measured_Depth));
      pRowBuff.set_Value((Int32)l_FCInclination, pRow2.get_Value((Int32)l_Inclination));
      pRowBuff.set_Value((Int32)l_FCAzimuth, pRow2.get_Value((Int32)l_Azimuth));
      pRowBuff.set_Value((Int32)l_FCE_W, pRow2.get_Value((Int32)l_E_W));
      pRowBuff.set_Value((Int32)l_FCN_S, pRow2.get_Value((Int32)l_N_S));
      pRowBuff.set_Value((Int32)l_FCTVD, pRow2.get_Value((Int32)l_TVD));
      pRowBuff.set_Value((Int32)l_FCState_Plane_Easting, pRow2.get_Value((Int32)l_State_Plane_Easting));
      pRowBuff.set_Value((Int32)l_FCState_Plane_Northing, pRow2.get_Value((Int32)l_State_Plane_Northing));
      pRowBuff.set_Value((Int32)l_FCSub_Sea_TVD, pRow2.get_Value((Int32)l_Sub_Sea_TVD));


      pCursor.InsertRow(pRowBuff);
      pRow2 = pTableCursor.NextRow();

}

try something like that...

AlexanderGray · ‎12-29-2010

You seem to be doing the loop ok. I wonder if so many cast operations is impacting the performance. You may want to keep a set of int32 variables to avoid doing that in the loop. I doubt that will make a huge difference though. I suggest you comment different parts of the loop to see if the problem is looping through the source table, setting the values of the rowbuffer or inserting the row buffer. What your code doesn't show is if the operation is embedded in a edit operation or an edit session or if your target table has a relationship class. These are all things that impact performance. You may also want to look at the ITableWrite2 interface that could speed things up. You might also want to test against a file geodatabase instead of a personal geodatabase. You may also want to compact the geodatabase after doing so many loads. In fact you may benefit from compacting after every few tens of thousands of records too. I also suggest getting a smaller dataset to try more variations without waiting 15 minutes for each test.

GregRieck · ‎07-23-2011

Did you resolve this? If so how? I'm inserting 24 new rows in basically the same manner and it is slow as well.