EGMP2201 practice question 4 of 15

A GIS database administrator needs to identify any performance issues with a nightly load process. Upon further research, the database administrator…

Full set: 65 questions
A GIS database administrator needs to identify any performance issues with a nightly load process. Upon further research, the database administrator discovers the following: • A table with 20 million rows is reloaded each night • This existing table is truncated before an Append is executed • There are three attributes in addition to objectid and geometry, one of which is a unique text identifier • The unique index is removed before Append and created again after Append • The Append operation takes 120 minutes to complete What should the administrator recommend?
Answer options
A: Remove the objectid index before Append
B: Remove the spatial index before Append Correct answer
C: Add a unique key index before Append
Correct Answer:
B. Remove the spatial index before Append
Explanation

The most significant performance bottleneck when appending a large volume of spatial data (20 million rows) is the continuous, row-by-row maintenance of the spatial index. Each inserted feature requires a computationally expensive update to the complex grid structure of the spatial index. The established best practice for bulk data loading is to drop the spatial index before the operation, append the data to the table, and then rebuild the index once. This single, holistic index creation process is vastly more efficient than millions of individual index updates. The scenario already correctly follows this practice for the attribute index, making the spatial index the remaining and most impactful element to address.

Why Incorrect

A. The ObjectID is the primary key, and its index is highly optimized and generally cannot or should not be dropped. The performance overhead is minimal compared to a spatial index.
C. Adding a unique key index before a bulk append is counter-productive. It would force the database to validate and update the index for each row, severely degrading performance.

References

1. Esri ArcGIS Pro Documentation: In the section on data loading strategies, Esri explicitly recommends this approach. "For very large loads, you can improve performance by dropping indexes, loading data, and re-creating the indexes. This applies to both attribute and spatial indexes."

Source: Esri ArcGIS Pro Documentation, "Strategies for data loading," section "Load data into an existing feature class or table."

2. Esri ArcGIS Enterprise Documentation: The documentation on geodatabase administration reinforces this principle for managing large datasets. "When loading large amounts of data... it is almost always faster to drop the indexes, load the data, and rebuild the indexes."

Source: Esri ArcGIS Enterprise Documentation, "Geodatabase management in SQL Server," section "Create spatial and attribute indexes."

3. Pennsylvania State University, GEOG 868: Course materials for Geodatabase Development and Management outline best practices. "A common strategy for loading large datasets is to first drop any indexes on the table (except the ObjectID index), load the data, then rebuild the indexes. This is especially true of spatial indexes, which can be time-consuming to maintain on a row-by-row basis."

Source: Penn State University, John A. Dutton e-Education Institute, GEOG 868: Geodatabase Development and Management, Lesson 5: "Loading and Maintaining Data."

Scroll to Top