topic Re: Joining Large files (900,000 records) in ArcGIS Pro Questions

Joining Large files (900,000 records)

BrianRizzo — Mon, 03 Apr 2017 18:52:41 GMT

Join operation takes about 12 hours to complete in Pro. (900,000 records with about 10 variables). Desktop operation takes about 6 hrs. Is this normal? If so, how does this speak to BIG data as this is not really representative of BIG data.

platform - recent I7, 16 GB, SSD.

Re: Joining Large files (900,000 records)

DanPatterson_Retired — Mon, 03 Apr 2017 19:51:09 GMT

Attribute join? or spatial join?

Re: Joining Large files (900,000 records)

IanMurray — Mon, 03 Apr 2017 20:11:03 GMT

You may try to find a better solution.

https://community.esri.com/blogs/richard_fairhurst/2014/11/08/turbo-charging-data-manipulation-with-python-cursors-and-dictionaries

Re: Joining Large files (900,000 records)

JoshuaBixby — Wed, 05 Apr 2017 20:33:05 GMT

In addition to Dan's question, can you provide some more information on where the data is stored (file geodatabase, enterprise geodatabase, etc...) and how exactly you are implementing the join (using a geoprocessing tool, dialog box in UI, etc...).

Re: Joining Large files (900,000 records)

BrianRizzo — Wed, 05 Apr 2017 22:45:12 GMT

Point fc file stored in a file GDB. Attempting to join an attribute from a csv.

Re: Joining Large files (900,000 records)

DanPatterson_Retired — Wed, 05 Apr 2017 22:48:27 GMT

I am wondering if the csv is the issue, have you tried to convert it to a geodatabase table and then join?

Re: Joining Large files (900,000 records)

BrianRizzo — Wed, 05 Apr 2017 23:16:45 GMT

While the join itself works and seems to happen very quickly, saving the join is the real problem. Converting the csv file to a GDB table doesn't seem to provide any gains.

Re: Joining Large files (900,000 records)

JoshuaBixby — Wed, 05 Apr 2017 23:47:39 GMT

Can you elaborate on "saving the join is the real problem." Are you referring to exporting the data after it has been joined?

Re: Joining Large files (900,000 records)

BrianRizzo — Wed, 05 Apr 2017 23:49:01 GMT

yes.

Re: Joining Large files (900,000 records)

JoshuaBixby — Wed, 05 Apr 2017 23:52:54 GMT

What is your workflow for exporting the data after the join? Are you using a tool like Feature Class to Feature Class or the Export Data dialog box from the GUI? Where are you exporting the data to, file geodatabase, shape file, enterprise geodatabase? If a file geodatabase or shape file, is the output location on a local or network drive?

Re: Joining Large files (900,000 records)

KoryKramer — Fri, 07 Apr 2017 14:13:46 GMT

Brian Rizzo‌ Joshua's question about what format you're exporting to is important. IMHO, the most important question hasn't been asked: is this a 1:1 or 1:M join? If 1:1, blows my theory. But if 1:M, since you're joining to a file gdb feature class, you will still only see the 900,000 records in the attribute table, but depending on the number of tabular records that are joined with each feature, you could be generating a table that is millions (perhaps many many millions) of records. These ARE stored in the file gdb even though you can't see them. If you export to a shapefile, each record would actually show up as an output feature (but depending on the number of records, you might exceed the limitation of shapefile storage and crash or freeze).

Anyway, let us know about the cardinality of the join and even send some data if you want to dig further.

About joining and relating tables—Help | ArcGIS Desktop describes what I'm talking about "However, it is possible to create a join under these circumstances. When you create a join in such a case, there are differences between how tools and other layer-specific settings work depending on the data source. If you are using geodatabase data to create the join, all matching records are returned. If you are using nondatabase data, like shapefiles or dBASE tables, to create the join, only the first matching record is returned.

This means that if you have created a 1:M or M:M join with geodatabase data and you generate a report, you see multiple records in the report, one for each corresponding match. The multiple matches are also seen when using a join field while symbolizing a joined layer, labeling, identifying features, generating a graph, and using either the Find or Hyperlink tool. If you are using the joined layer as input to a geoprocessing tool or in an export operation, the multiple matching records are used.

Caution:

In all cases of 1:M joins, only the first matching record is joined and displayed in the layer's attribute table."

Maybe this helps?

Re: Joining Large files (900,000 records)

BrianRizzo — Sat, 08 Apr 2017 00:11:23 GMT

Joshua, Kory

Thanks for the replies. The join is 1:1. I exported a table from a point file and processed it in SPSS. This generated an output file that added a new field to the table. Initially I joined this new table, which was in CSV form, to the original point file. The join worked well but saving the join takes a very very long time as indicated previously. I also created a GDB table from the CSV and joined this to the point file. Again the join worked but again the save operation took as long as the previous attempt. Interestingly I tried both solutions in Desktop and Pro. Desktop process the save in about half the time-6 hours!

I'm writing a script as suggested by Dan and will hope this addresses my problem.

Brian

Re: Joining Large files (900,000 records)

JoshuaBixby — Sat, 08 Apr 2017 12:54:17 GMT

What about indexes, is the join field indexed in both data sets?

Even without indexes, I still can't get my head around why it would take some long. I have worked with some million-record or more data sets. Sure, they are slower than what most people are used to, but 6 hours to export? That just doesn't make sense to me.

Re: Joining Large files (900,000 records)

BrianRizzo — Sat, 08 Apr 2017 18:32:01 GMT

Thanks everyone for the help. It turns out that INDEXING BOTH files is critical. Especially when the file sizes become very large.

Thanks everyone. Sorry to take up your time. I should have been more careful reading the help! To be honest I'm not sure how indexing works so I had no idea it could have such a big impact.

Re: Joining Large files (900,000 records)

BrianWade — Wed, 26 Apr 2017 16:05:53 GMT

Just Checking, indexing on the Just joining fields? and also how long did it take after you indexed?

Thanks