topic Re: Join Field - Incredibly Slow in Geoprocessing Questions

Join Field - Incredibly Slow

OwenEarley — Wed, 07 Oct 2015 05:43:10 GMT

I have a table containing some calculated results and a feature class with 4 text fields that I want to bring into the results table based on a shared ID field (Long). Both contain just fewer than 60,000 rows/features and both have indexes on the ID field used for the join.

If I do this using the Join Field tool in ArcMap 10.2.2 the process takes over 50 minutes. This is insane for a join on ~60k rows.

As a comparison I can do the process manually in ArcMap in under 4 minutes by creating new fields, joining data and calculate fields.

If I use the Add Join tool to join all fields the process takes 11 seconds. However, this brings in a whole heap of fields that I am not interested in.

Additional notes: The process is to be run from within a .Net application so the manual process is not an option. The PC is an 8 core machine, the CPU usage sits at around 12% and there is about 10GB of available memory.

Why is Join Field so incredibly slow?

Re: Join Field - Incredibly Slow

curtvprice — Wed, 07 Oct 2015 06:37:45 GMT

Join Field is slow. I do not have an explanation.

Why not run the add field tool 4x, add join, and calculate within .NET?

In Python I have a little function that uses a dictionary to copy the data and it is scary fast by comparison.

Re: Join Field - Incredibly Slow

DanPatterson_Retired — Wed, 07 Oct 2015 07:22:15 GMT

Owen, I presume you saw the performance tips section in this link? Essentials of joining tables—Help | ArcGIS for Desktop

Re: Join Field - Incredibly Slow

curtvprice — Fri, 16 Oct 2015 03:37:42 GMT

I shoud have mentioned, the reason Add Join is instantaneous (11 sec) is it doesn't copy any data, it just sets up a dynamic link. It still can be slow when you try to copy the data (especially if the join fields aren't indexed), say if run Copy Features on the table with the join active.

But not as slow as Join Field!

Re: Join Field - Incredibly Slow

JustinMeyers — Fri, 16 Oct 2015 15:35:00 GMT

I would set up a model. add fields, set up a join, then a calculate. you can have numerous drop downs for what fields you want to claculate based on, and have your join fields set up too. a little work up friont, but will save you time in the end. Join field tool has always been slow. if you really want to use it, hide all the other fields, or delete them going into the tool so you only have the fields you want.

Re: Join Field - Incredibly Slow

DuncanHornby — Sat, 17 Oct 2015 08:47:02 GMT

I've used this tool without issues but that has been within ArcMap. You make a vague reference to running this tool from within a .Net application, I would image the bottle neck is there. Without seeing the underlying code I don't think anyone can help you here.

Re: Join Field - Incredibly Slow

OwenEarley — Sat, 11 Dec 2021 20:03:33 GMT

The .Net code just passes parameters to the geoprocessor object to run the tool. The delay is in the running of the tool itself.

        public Geoprocessor gp { get; set; }
        public ITrackCancel CancelTracker { get; set; }

        ...  

        public void GpAddJoin(string layer, string keyField, string joinTable, string joinField, bool keepAll = false)
        {
            var tool = new ESRI.ArcGIS.DataManagementTools.AddJoin();
            tool.in_layer_or_view = layer;
            tool.in_field = keyField;
            tool.join_table = joinTable;
            tool.join_field = joinField;
            tool.join_type = (keepAll) ? "KEEP_ALL" : "KEEP_COMMON";
            gp.Execute(tool, CancelTracker);
        }

Re: Join Field - Incredibly Slow

DuncanHornby — Mon, 19 Oct 2015 14:57:06 GMT

Hmmm.. Do the fields that you are joining with have indices, that can can improve performance significantly?

Re: Join Field - Incredibly Slow

BruceHarold — Mon, 19 Oct 2015 15:29:53 GMT

Try this tool, which from memory works in both 10.x and Pro:

http://www.arcgis.com/home/item.html?id=da1540fb59d84b7cb02627856f65a98d

Regards

Re: Join Field - Incredibly Slow

curtvprice — Wed, 21 Oct 2015 01:29:04 GMT

Bruce, that script tool has a really interesting approach, you add the fields and then populate the values using an update cursor with a generator to limit how much memory gets used. The tool uses arcpy.da so it should work with 10.1 SP1 and later. You may want to add that tidbit to the info about the tool.

Now that pandas will be shipped with 10.4 it will be interesting if some of its lightning fast table handling can be leveraged to create similar supercharged tools for common operations that take too long!

Re: Join Field - Incredibly Slow

DanPatterson_Retired — Wed, 21 Oct 2015 01:33:58 GMT

remembering that Pandas is just Numpy all dressed up

Re: Join Field - Incredibly Slow

BruceHarold — Wed, 21 Oct 2015 15:38:03 GMT

Yes, arcpy.da.ExtendTable is already there and suitable for the join field use case.

My tool has an issue if your key field is unicode, byte order drives the sort used to join whereas the collation order of a language may be different. I didn't pursue fixing that - it looked hard. Maybe a temporary SQLite database could be used.

Re: Join Field - Incredibly Slow

OwenEarley — Wed, 21 Oct 2015 20:52:41 GMT

This tool is very impressive, joining the fields in just over 12 seconds.

For my original issue, I ended up changing the way that the data was being processed to avoid the join field geoprocessing tool altogether. However, I am marking Bruce's answer as correct as it is most likely to help others that are searching for a solution to this problem.

Re: Join Field - Incredibly Slow

MohammadrezaNikfal1 — Sun, 19 Mar 2017 15:25:24 GMT

There is a huge difference between 3 tools. To me, I used "AddJoin_management" in python. It is working very well. But the secret is input data. Using file-base table as the main Layer is perfect, however, when we use a Geo-database table, the performance is dropping dramatically!

fishNetMinimumPointsDistance = os.path.join(arcpy.env.scratchFolder, 'fishNetMinimumPointsDistance.dbf')
fishNetPointMapToLayer = arcpy.mapping.Layer(fishNetPointMap)
arcpy.AddJoin_management(fishNetPointMapToLayer, "FID", fishNetMinimumPointsDistance, "IN_FID", "KEEP_ALL")