Community

Improve the speed of listing contents in an Enterprise Geodatabase (ArcSDE)

537
3
03-22-2022 11:27 AM
Status: Open
Labels (1)
Labels
AZendel
by
Occasional Contributor III

Our county has been on an Oracle Enterprise Geodatabase (ArcSDE) for over 15 years. When I add a large feature class (100K+ parcels, 200K+ address point locations) to both ArcMap and ArcGIS Pro, it's capable of filling the map with 10s of thousands of features every second. This demonstrates that the database and our network are plenty fast! But if it's that fast, why does it take nearly 10 seconds to list the contents of the database in ArcCatalog and the Catalog pane of ArcGIS Pro?

I realize that this might sound impatient and trivial, but those ten seconds add up. If I work 48 weeks out of the year and list the contents of the database 5 times per day, that's 3.3 hours per year -- almost half a day of work waiting for the contents to populate. I realize that contents of the geodatabase are stored in the "GDB" tables of the database. Then it must do joins to group feature classes inside of feature datasets. Then it must also filter out objects that the current user doesn't have access to. And so on. So it's not simple, but 10 seconds seems excessive. It would be nice to see some effort put into this (or an explanation of why previous efforts have run into walls).

3 Comments
Reinaldo_Cartagena
by
‎03-22-2022 12:17 PM

The problems in the Enterprise Geodatabase center on the performance, extraction and access to the data in each table and Feature Class. This low performance is cumulative and is associated with the volume of data. They must perform optimization tasks in ArcSDE, Oracle and the Applications. For example:

Parameter tuning in the SDE scheme
Oracle instance .init tuning
Reconcile and post of Geodatabase versions
Reduction of records in the Add and Delete tables through Geodatabase compress (SDE_State and SDE.STATE_LINEAGES )
Bring the Geodatabase to state 0
Creation of spatial indexes
Creation of alphanumerical indexes for queries (Feature Class and table)
Create a compress policy and active versions in the Geodatabase
Control visual reference scale in all web services and map projects
Control symbology in web services and map projects
Check the performance of the Database storage structure: RPM Disks, SSD (Indices), RAID 10/1/5, Etc.

Update database statistics

Rebuild Indexes on system tables

Compress the Geodatabase

First of all make a backup copy and test the recovery of your Geodatabase. They must also make these changes in a staging environment

See some reference links:

https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-oracle/setup-geodatabase-orac... 

https://support.esri.com/en/technical-article/000004531 

Reinaldo.

AZendel
by
‎03-23-2022 07:10 AM

Thanks for your responses. I wonder if we are talking about 2 different issues. The 10 second wait that I'm talking about happens when you list the database contents by double clicking the database connection below

ArcMap/ArcCatalog

AZendel_2-1648044635165.png

 

ArcGIS Pro:

AZendel_1-1648044049770.png

 

Our Oracle SDE database is otherwise very performant, even with large datasets that are versioned and/or have archiving enabled. The county's DBA regularly compresses the database to flush out the add and deletes tables when necessary. I imagine that he conducts other tuning and maintenance tasks to keep database operations in a healthy state.

I only notice significant latency while waiting for Arc* desktop products to list the contents of the database. That's what makes me suspect that ESRI developers may want to investigate if they can improve the queries that this action kicks off. 

Is anybody else experiencing this?