Joins take too long in Model Builder

JohnByers

Hi all,

I'm trying to write a script that automates a join and then does some reporting.

I'm not very experienced with joins and I'm running into a problem.
When I do a join manually (right clicking the shapefile and specifying the join) it does the join seemingly automatically.
However, when I try and do a "Join Field" in ModelBuilder it takes a really long time. Minutes.

Is there a different method of automating a join using python or Model Builder that runs faster?

Thanks for any help.

RichardFairhurst

Hi all,

I'm trying to write a script that automates a join and then does some reporting.

I'm not very experienced with joins and I'm running into a problem.
When I do a join manually (right clicking the shapefile and specifying the join) it does the join seemingly automatically.
However, when I try and do a "Join Field" in ModelBuilder it takes a really long time. Minutes.

Is there a different method of automating a join using python or Model Builder that runs faster?

Thanks for any help.

You don't want to use the Join Field tool to replicate what you are doing in ArcMap, because that tool actually physically modifies your input layer to include the fields and data in that single feature class, which is why it takes so long.  But that is not what you are doing when you right click on a layer and Join it to another layer in ArcMap, because that join is set up as a layer in memory, not phyically stored in a database.  That is why it is very fast to set up.

To replicate what you are doing in ArcMap you need to use the Add Join tool.  The trick in ModelBuilder is that the first input feature class or table must be formatted as a Layer (Make Feature Layer) or a Table View (Make Table View) to initate the Join.  The second Feature Class or Table does not have to be in the form of a Layer or Table View (the thing you actually see in ArcMap), but may be read directly from disk (the thing you see in ArcCatalog).

The second trick is to be sure that both the main layer and the join layer are Indexed on the common join field using the Add Attribute Index tool.  Indexed fields dramatically improves the speed of selection and calculation operations on a Joined layer.

Finally, because the Add Join tool only exists in memory, you can break the Join using the Remove Join tool.  This allows you to break one Join and create another Join or act on the original layer (which is faster if you don't need the join table fields to select records or participate in calculating values).