topic Re: SDE Taking Too Long To Load Data in Geodatabase Questions

SDE Taking Too Long To Load Data

MarkMcCart — Mon, 11 Jul 2016 12:02:27 GMT

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

Re: SDE Taking Too Long To Load Data

George_Thompson — Mon, 11 Jul 2016 12:10:20 GMT

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?

Re: SDE Taking Too Long To Load Data

MarkMcCart — Mon, 11 Jul 2016 12:17:40 GMT

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

Re: SDE Taking Too Long To Load Data

George_Thompson — Mon, 11 Jul 2016 12:27:54 GMT

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?

Re: SDE Taking Too Long To Load Data

MarkMcCart — Mon, 11 Jul 2016 12:41:04 GMT

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.

Re: SDE Taking Too Long To Load Data

PanagiotisPapadopoulos — Mon, 11 Jul 2016 12:44:03 GMT

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.

Re: SDE Taking Too Long To Load Data

PanagiotisPapadopoulos — Mon, 11 Jul 2016 12:47:13 GMT

Also check if you have any network issues between the Database and the place the data coming.

If it is possible place the input data on the Database server.

Re: SDE Taking Too Long To Load Data

MarkMcCart — Mon, 11 Jul 2016 12:53:27 GMT

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

This isn't the issue.

Re: SDE Taking Too Long To Load Data

PanagiotisPapadopoulos — Mon, 11 Jul 2016 13:02:13 GMT

What is your input?

Re: SDE Taking Too Long To Load Data

PanagiotisPapadopoulos — Mon, 11 Jul 2016 13:10:34 GMT

Also in the destination Table is it any Trigger enabled?

Re: SDE Taking Too Long To Load Data

MarkMcCart — Mon, 11 Jul 2016 13:42:11 GMT

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.

Re: SDE Taking Too Long To Load Data

MarkMcCart — Mon, 11 Jul 2016 13:44:55 GMT

So we changed the ATTRBUFSIZE from 50,000 to 100,000 to match what we had on Test. It is still taking quite a while to load data. We're monitoring the data load in Toad and noticed once it loaded 56,000 records, it took about 3-4 minutes before it loaded any more.

Re: SDE Taking Too Long To Load Data

MarkMcCart — Mon, 11 Jul 2016 13:47:38 GMT

How can use the old 'Load-only' loading option in 10.4 now that SDE command line is gone?

Re: SDE Taking Too Long To Load Data

VinceAngelo — Mon, 11 Jul 2016 14:21:39 GMT

Dropping the spatial index has the same effect.

Re: SDE Taking Too Long To Load Data

MarkMcCart — Mon, 11 Jul 2016 14:40:27 GMT

When monitoring the data load in Toad, we noticed that Test and Prod are running different commands. Production is calling this statement a lot: SELECT table_name, time_last_modified FROM SDE.sde_tables_modified

Test does not make this call.

Re: SDE Taking Too Long To Load Data

George_Thompson — Tue, 12 Jul 2016 17:26:15 GMT

Hi Mark,

I think that it might be a good idea to open a new support case with Esri Technical Support and have the Geodatabase team investigate. There are a lot of different factors that go into the load times. They may be able to shed some more light on this issue.

Re: SDE Taking Too Long To Load Data

TerryIffland — Tue, 12 Jul 2016 17:33:34 GMT

Are all the patches applied to the Production database as those in Test? I recall something happening like this with 10.1 where a query was running before an insert. It created a ton of overhead. I believe there was a patch from ESRI to resolve the problem in 10.1. May be the same issue in 10.4 or in the Oracle database.

Re: SDE Taking Too Long To Load Data

MarkMcCart — Thu, 14 Jul 2016 12:05:23 GMT

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

Re: SDE Taking Too Long To Load Data

deleted-user-UxcAu2PHrDQp — Fri, 15 Jul 2016 11:11:59 GMT

Look for high Oracle Client wait times in the database logs or trace files from the client. This is an indicator of needing to set the DEFAULT_SDE_SIZE or look into client or network resources. We decided not to make a global database change for this parameter but let the clients define this in their own TNSNAME or SQLNET files.