SDE Taking Too Long To Load Data

2882
18
Jump to solution
07-11-2016 05:02 AM
Highlighted
Occasional Contributor

We are loading large amounts of data (1,000,000+ records) into a single feature classes into our Oracle 12c ArcSDE 10.4 instance. However, it is taking way too long to load that data. For example, I tried to load 550,000 records into a feature class using the Simple Data Loader in ArcCatalog on Sunday starting at 10:00 am, by 7:00 pm, it still wasn't done loading. I let it run all night, and it finally loaded. Our test environment does not have this same issue. Loading data into our test environment, with less resources, takes just a few minutes.

Where can I start to troubleshoot this issue? Is it in Oracle, SDE set up? We have REST services being served out by these feature classes and tables in SDE, could that be an trouble area?

Any help would be appreciated.

Thanks,

Mark

Tags (2)
1 Solution

Accepted Solutions
Highlighted
Occasional Contributor

After some testing and looking at our trace files. We determined that a change had to be made to our sqlnet.ora file in our Oracle client. We added the line 'DEFAULT_SDE_SIZE= 512'. Once that change was made, data loaded much faster. Thanks for everyone's advice to help us solve this include Jim at Esri.

-Mark

View solution in original post

18 Replies
Highlighted
Esri Frequent Contributor

Hi Mark,

Is the data versioned in the instance that is taking a long time?

Has the time for loading gotten worse over time or always been longer to load than the test instance?

Are there any differences on the feature class in the test vs. prod?

--- George T.
Reply
0 Kudos
Highlighted
Occasional Contributor

George,

1. No, the data is not versioned. This is schema is not a transactional schema. Just a schema to serve out REST services.

2. No, it's always taken forever to load. The only caveat to that statement is that when we restart our old legacy, soon-to-be-retired, ArcServer stack, data seems to load faster.

3. No, there is no difference between the feature class structure in test vs. production.

-Mark

Reply
0 Kudos
Highlighted
Esri Frequent Contributor

So on both test and prod there is a map service being consumed when loading data?

What version of the Desktop client are you using?

Did it load faster on a previous version of Desktop?

--- George T.
Reply
0 Kudos
Highlighted
Occasional Contributor

1. REST services are being consumed from Prod. I stopped all services that were tied to feature classes in that feature dataset. Also, I dropped the spatial index prior to loading the data.

2. Desktop version: 10.4.0, SDE 10.4.0

3. Having only tried to load via version 10.4.0. Did not try earlier version.

After doing some digging, we did notice a difference between Test and Prod: ATTRBUFSIZE was different. In Test it was 100,000 in Prod it was 50,000. I am still trying to learn exactly what this means and does.

Reply
0 Kudos
Highlighted
Esri Regular Contributor

Try to load the data without having the Spatial Index enabled.

first delete the spatial index from the Feature Class, (Remote Spatial Index toolbox)

then Load the data

and finally create the spatial index again. (Create Spatial Index toolbox)

in the past versions with SDE commands available the option to set a Feature Class on Load_Only mode and Normal_IO was something similar.

Reply
0 Kudos
Highlighted
Occasional Contributor

I've always dropped the spatial index prior to loading. And then create a new one and rebuild it.

This isn't the issue.

Reply
0 Kudos
Highlighted
Esri Regular Contributor

What is your input?

Reply
0 Kudos
Highlighted
Occasional Contributor

1. I am loading data from a FGDB. It is point data with attributes and quite a few domains we've set up.

2. No. There are no triggers setup. I assumed Esri handles that all in its code base.

Reply
0 Kudos
Highlighted
Esri Regular Contributor

Also in the destination Table is it any Trigger enabled?

Reply
0 Kudos