Attribute Rules slowing down bulk load to enterprise geodatabase

01-23-2024 07:31 AM
Occasional Contributor II

Hello, I'm relatively new to attribute rules.  I'm trying to build them into a new schema I'm developing to handle primary key population with a sequence using the NextSequenceValue() function.  I've noticed that just by adding this one simple attribute rule, the time to bulk import features to the feature class slows significantly.  For my test I used a sample line dataset of 1,000 rows.  When importing data through the Append tool, with the rule disabled, the data is loaded in seconds.  When the sequence attribute rule is enabled, it takes about 1.5 minutes.  And this is being done from Pro on a machine at the same datacenter as the Oracle geodatabase I'm loading into.

1.5 minutes is okay, but that's just for 1,000 rows.  With this schema I'm building, one major workflow is loading data into the schema from a 3rd party, and some of the 1:M tables related to the line feature class could get 1,000's of rows each every day.  I'm worried the load time will be so long with this attribute rule, that I'm going to hog our ETL scripting server all night with just this one job. Same thing happens with loading the data via FME

I'm wondering if I should fall back to alternative ways for generating primary keys outside of the Esri ecosystem.  For example, creating a trigger at the database level to grab sequence values on row inserts.  Or running a query in the ETL tool to grab sequence values from the database upstream from the geodatabase import.  These feature classes won't be versioned or archive enabled, so I don't think the trigger population would be an issue.

Although attribute rules are great for managing some key attributes for transactional edits done by analysts throughout the day, they seem to really slow down bulk loads. I'm curious if folks out there have created some mitigation strategies for speeding up nightly bulk loads while keeping your attribute rules intact or daily edits.

0 Kudos
0 Replies