0

Looking for a best practice for loading mass quantities of data into an Oracle GDB using ArcPy.

I'm attempting to load ~10 million records of Polygon and Point shape data into a GDB (~5M per table). I'm currently running into a problem that, as the records go up, the time it takes to insert the records significantly increase. This was expected, but NOT to the extent that it's happening. At about ~2M records in, it's taking almost 20 minutes to append a 50mb file. Please correct me if I am wrong, but I am assuming this is caused by ArcPy adjusting spatial and objectid indices.

This is my primary sequence of commands simplified:

# First I copy the .shp file to a local fgdb. I need to do this to add and update fields used by my company
arcpy.CopyFeatures_management(shp_path, hold_table)

# Then I add the fields I need
arcpy.AddField_management(hold_table, "myfield", "int")

# Then I update the field I made
with arcpy.da.UpdateCursor(hold_table, "myfield") as update_cursor:
    for row in update_cursor:
        row[0] = "myupdate"
        update_cursor.updateRow(row)

# Now I append to the gdb
arcpy.Append_management(hold_table, table_to_insert_path, "NO_TEST")

I've also noticed a massive slowdown in a process that removes records from the GDB as the records increase as well, even with a where_statement included in the update cursor. For instance, removing ~2M records out of 5M total took almost 6 hours.

This is what I want to attempt to try, however, I feel I will run into the same issue:

Create a local fgbd for each "group" of data I need to insert, and then run an append, or a merge, on all those local fgdbs into the GDB at the end. Instead of running almost 400 files through the above process and instantly appending them to the GDB, this would create approximately 20 local FGDBs which would get appended/merged at the end of the whole process. (I would assume the appends at the end would take the same amount of time as doing them instantly, but then again, this is ArcPy...)

This is ArcGIS 10.3, using the ArcPy installation that comes with ArcGIS Desktop.

I have tried dropping the spatial indices from the 2 tables I am appending to. This doesn't seem to increase performance. Oddly enough, the exact same index seems to get recreated by the append function, but they are not registered as a mdsys.spatial_index.

Edit 1: In response to Yanes comment. I am currently appending to an empty schema. The data I am grabbing has more fields than the data I will be appending to the table. Thus, I am using arcpy.FieldMap() in order to direct the fields. I have tested the load with and without FieldMapping, and the performance hit is the same either way. I will not always be appending to an empty schema. I will need to be able to remove, make edits, and append to the full ~5M record table.

Edit 2: In response to a comment from Vince. I am using SDO_GEOMETRY storage.

Update: The arcpy.Append_management function is the root of the performance issues. It looks like this function commits after every single record. This caused the recovery area of the db to grow rapidly, causing the slowdowns. Also, this caused problems with spatial indexes. I have now switched to using arcpy.da.InsertCursor, and I'm adding in my additional fields with each line. Recently, an overnight run would push ~3M records. Last night, it pushed right around the ~10M mark.

  • For such a huge data, do you reckon it would be better to copy the schema of your fgdb first and then load the data on to the empty schema? – yanes Dec 2 '15 at 21:19
  • 1
    That is what I am doing. The schema I am appending to actually has quite a few less fields than the files I am reading. I did not post it above, but I am using field mapping to direct the data. I have tried both with, and without field mapping, and I get the same performance hit. – disflux Dec 2 '15 at 21:24
  • 1
    Please do not place critical details in comments. Always edit the question in response to clarification requests. – Vince Dec 2 '15 at 21:25
  • 1
    Another note is that you are using Arcpy. Is this operation running on your desktop, or on the server? If at all possible, I would try to run this at the server level. With that many records, even if the records do not contain a large amount of data each, you may be running into a memory issue. If you are running out of RAM at the 2M record mark and having to start swapping to the hard drive, this could be the source of the slowdown. – Get Spatial Dec 2 '15 at 21:35
  • 3
    The first law of database deletion is to never do it. Far better to make a new table with CREATE TABLE bar AS SELECT * FROM foo WHERE .... The first law of database loading is to never have indexes active while bulk loading. I've measured order of magnitude improvement with "small" tables (only 1M rows) by dropping indexes before Append. Please edit the question to specify that you're using SDO_GEOMETRY storage. There's a number of issues with your methodology that are suboptimal, making this question quite broad. – Vince Dec 2 '15 at 21:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.