topic Re: Bulk Load Geodatabase Table - Pro Core SDK in ArcGIS Pro SDK Questions

Bulk Load Geodatabase Table - Pro Core SDK

KierenTinning2 — Fri, 17 Jul 2020 15:07:59 GMT

All,

Does anyone know if there is a more efficient mechanism to bulk load records into a Branch Versioned Geodatabase Table. This is a console app, so I am using the ApplyEdits Method

ArcGIS Pro 2.5 API Reference Guide

And reading and writing in blocks of 2000 records. The target is to load approximately 1.5 million records. In regular C# code (data transformation, etc) with SQL Commands this takes about 4 minutes. It's taking about 12 minutes per 2000 records with the ApplyEdits Method in the core API. Reading 2000 record batches from the source and writing same. The table has a relationship class tied to a Utility Network feature class, so it needs to be and remain branch versioned.

Any advice would be greatly appreciated.

Re: Bulk Load Geodatabase Table - Pro Core SDK

RichRuh — Fri, 17 Jul 2020 15:48:42 GMT

Hi Kieren,

In the upcoming Pro 2.6 release, we're adding an InsertCursor class, that you can obtain by calling Table.CreateInsertCursor(). This might help you. For many feature classes, this will be several times faster. There are exceptions though, and relationships with messaging are one of those exceptions. This occurs if you're using anno, attachments, or composite relationships. In these cases, the performance won't be worse, but it won't be better either. I would definitely try this in 2.6 when it becomes available.

Since you mentioned branch versioning and utility networks, are you trying to insert these records into default or a version?

--Rich

Re: Bulk Load Geodatabase Table - Pro Core SDK

KierenTinning2 — Mon, 20 Jul 2020 13:03:50 GMT

Rich,

Thanks, we're inserting into default, it's a table that connects to a feature class through a simply relationship class.

I look forward to testing 2.6, it's interesting as Pro's append command will do the same in about 30 minutes (all 1.3 odd million records)

Re: Bulk Load Geodatabase Table - Pro Core SDK

RichRuh — Mon, 20 Jul 2020 16:25:11 GMT

Well, depending on your workflow, you can always call the Append geoprocessing tool from the Pro SDK.

--Rich

Re: Bulk Load Geodatabase Table - Pro Core SDK

RichRuh — Wed, 29 Jul 2020 20:05:14 GMT

As promised, the Pro 2.6 release adds an InsertCursor class, accessible via Table.CreateInsertCursor.

Re: Bulk Load Geodatabase Table - Pro Core SDK

KierenTinning2 — Tue, 01 Sep 2020 11:03:55 GMT

Rich Ruh‌ thankyou, I'll test it out.

Re: Bulk Load Geodatabase Table - Pro Core SDK

DHuantes — Wed, 02 Sep 2020 21:06:27 GMT

Rich,

Is there an example of using the InsertCursor anywhere? I noted in the SDK documentation that this is also available for FeatureClasses. I searched the community examples for InsertCursor and got no hits. I have lots of geometry (upwards of 4M features) that I've had to break up into multiple layers as Pro seems to have a threshold for efficiency and I'm excited to see what the InsertCursor does for performance. I'm current using the EditOperation.Create method and hoping this will be much faster. Thanks!

Re: Bulk Load Geodatabase Table - Pro Core SDK

DHuantes — Wed, 02 Sep 2020 21:26:24 GMT

I also searched the SDK Snippets and found this Read and Write blob fields with a row cursor in a callback

I'm thinking the concept is similar so I'll give it a try. Unfortunately my old code is currently running so it won't be immediate. Thanks.

Re: Bulk Load Geodatabase Table - Pro Core SDK

RichRuh — Sun, 12 Dec 2021 06:29:17 GMT

Hi Daniel,

The snippet you are looking for is over in the Geodatabase snippets area. That snippet shows how to use an insert cursor in a CoreHost app (command-line). If you want to run this inside a Pro add-in, you probably want to do something like this:

using (FeatureClass featureClass = featureLayer.GetFeatureClass())
{
  // Insert rows using InsertCursor
  EditOperation insertCursorEditOperation = new EditOperation();
  insertCursorEditOperation.Name = "Create rows using InsertCursor";
  insertCursorEditOperation.Callback((EditOperation.IEditContext editContext) =>
  {
    using (InsertCursor insertCursor = featureClass.CreateInsertCursor())
    using (RowBuffer rowBuffer = featureClass.CreateRowBuffer())
    {
      for (int i = 0; i < countRowsToCreate; i++)
      {
        // Fill in RowBuffer using your data here

        insertCursor.Insert(rowBuffer);
      }
      
      insertCursor.Flush();
    }
  }, featureClass);
}
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I hope this helps,

--Rich

Re: Bulk Load Geodatabase Table - Pro Core SDK

DHuantes — Fri, 04 Sep 2020 19:09:56 GMT

Rich,

Thanks. I have Good and Bad News...

Good News: Your snippet helped and I got the InsertCursor to work.

Bad News: Despite getting it to work it did not result in any performance improvement over the standard EditOperation.Create method. The creation of approximately 264,000 Polylines that took 50 minutes previously still takes that long using InsertCursor....

Hmmm... probably not exactly that long because I'm measuring the time it takes to run an entire process that takes 50 minutes that includes the creation of these lines but since they only thing different between the two implementations was the InsertCursor with EditoOperation.Callback approach then I think my assessment is valid.

The documentation for InsertCursor seems to indicate that it should increase the speed of these transactions but there was a caveat about certain situations where it would not. I didn't think my situation applied.. Each of my records/rows is a simple polyline feature with 3 attributes that are all three integers. Any other suggestions are welcomed.

Re: Bulk Load Geodatabase Table - Pro Core SDK

RichRuh — Fri, 04 Sep 2020 20:46:58 GMT

Daniel,

What kind of geodatabase are you using? I'm seeing significant performance enhancements with file geodatabases and enterprise geodatabases. With feature services, I'm seeing an improvement, probably because in this case the database insertions are already being batched.

--Rich

Re: Bulk Load Geodatabase Table - Pro Core SDK

DHuantes — Tue, 08 Sep 2020 13:48:57 GMT

I'm using a File Geodatabase...

You are correct. I was batching before. I'm inserting the same size batches with the InsertCursor as I was with the EditOperation.Create method... approximately 4400 records/rows for each call to EditOperation.Execute.... I was thinking maybe I should increase this. Unless you have some other things I should consider. Speaking of which... Do you know if there is a "sweet spot" in terms of the maximum number of records/rows per Execute? Additionally, I haven't tried but would you expect any improvements if I use EditOperation.ExecuteAsync since each row/record is independent of the other? Thanks!

Re: Bulk Load Geodatabase Table - Pro Core SDK

RichRuh — Tue, 08 Sep 2020 23:10:22 GMT

That's weird. I repeated my tests today, and am seeing about 3.5x improvement of InsertCursor in a callback vs. EditOperation.Create with a file geodatabase feature class. We're looking into some more ideas and will get back to you soon.

--Rich

Re: Bulk Load Geodatabase Table - Pro Core SDK

RichRuh — Mon, 14 Sep 2020 18:46:25 GMT

Hi Daniel,

We ran a test here where we created 275,000 polylines in a file geodatabase. Using the old CreateRow method we saw it take about 10 minutes. Switching to insert cursors cut it down to 7 minutes. Not as dramatic a difference as seen with enterprise geodatabases, but still a significant improvement.

We might be at the point where the best path forward is to log an issue with tech support. They can work with you to help put together a file geodatabase and code sample. Please point tech support to this thread to ensure the issue gets routed to me.

Sorry I don't have any other good ideas to try.

--Rich

Re: Bulk Load Geodatabase Table - Pro Core SDK

DHuantes — Wed, 16 Feb 2022 15:29:30 GMT

Wow... How time flies.... Was revisiting this issue after working a lot of other things in the interim. Ultimately I found significant improvement in performance when not calling SaveEditsAsync as often as Create... I'm not sure of the right ratio but I believe back then it was a 1:1 ratio of calls to EditOperation.Create to EditOperation.Execute. Now I probably have 100's if not 1000's of calls to EditOperation.Create before calling EditOperation.Execute. Is there a recommended ratio or limit? But that's not why I'm posting again...

With even larger datasets then I've used the Append GP Tool and Table to Table to bulk copy load. Which is far more efficient than EditOperation. To bulk load data I've used Table to Table for upwards of 100K records in seconds from a CSV file. I've also done equally large datasets of point, line, and polygon feature class using Feature Class to Feature Class and Append. But for Table to Table I've only done that with data (i.e. no features). Now I'm looking at the original problem that I posted here and the approach I'm trying is to write to a CSV first. My Add-In calls a native C++ code for all the algorithm processing and currently returns the Geometry and attributes in seconds and then it takes 10's of minutes for the AddIn to actually create all the feature classes and features. The geometry and attributes are a set of points and attributes for those points that are used to make a large number of polyline features to be used as inputs to the 3D Line Intersect with Surface GP Tool. Haven't found a simple way to do this in one step so my plan is to:

1. Write CSV and attributes to CSV file

2. Once file is written, bulk import points into a Table or Point Feature Class

2. Use Point to Line GP Tool to create the line features from the Point Features

Is there an easier more efficient way to do this? Thanks!