ArcGIS Pro is unusable with large SDE geodatabases

[alex_friant]

We have ArcGIS Pro 2.0, and we are trying to connect to our main SDE geodatabase, but it's painfully slow, to the point of making it practically unusable.

Our SDE geodatabase has 380 feature classes. It's a beast, but ArcCatalog takes just 20 seconds to connect up once, and then it's fast for the rest of the session.

However, in Pro, if we even touch (not double click, just a single click) our large SDE geodatabase connection icon to view the "Details" of the SDE geodatabase, Pro does the progress triple-bar thing for about a minute, evetually loads the Description metadata, but then proceeds to lock up Pro for another minute.

This happens every single time we go away from the "Databases" folder and then return to it. Pro doesn't remember that it connected to the geodatabase just 5 seconds ago. So when we come back to the "Databases" folder the whole process starts over. What is going on in the background?

At the UC 2017, I spoke with an Esri employee who stated that ArcGIS Pro 1.4 (and previous) had a bug where Pro would make "hundreds" of calls to each feature class for no functional reason. He then said that this bug was fixed in Pro 2.0, but it appears to still be an issue. He also advised indexing, which I've done (left the computer locked with me signed in, Pro running over night), but it also appears to have no positive effect.

Anyways, our smaller SDE geodatabases work much faster, but you can still tell that Pro has to "reconnect" and gather some data from the gdb in the background. It just seems like Pro has taken a huge step backwards, contrary to advertisements of "64 bit speed", from ArcMap/ArcCatalog when it comes to interacting with SDE geodatabases.

Can anyone explain why Pro is slower than ArcCatalog when interacting with SDE geodatabases?

[George_Thompson]

I would recommend get an https://community.esri.com/groups/technical-support?sr=search&searchId=9f8ac989-b5d7-4344-b24a-905cc...‌ case open and if possible, provide a backup of the DB for testing. Sounds like there may be something else in play.

[alex_friant]

Will do. Thanks.

[DanPatterson_Retired]

You might also consider the observations for upgrades to ArcMap discussed in this thread

where sequential upgrades over existing installations finally reached a breaking point.  You may wish to examine the combined history of your machine with all the esri software you use...  

[alex_friant]

Thanks for the recommendation. There have been no upgrades on this machine. It's all fresh installs on a new Win 10 Pro OS. 

The part to remember is that ArcCatalog works just fine. ArcGIS Pro is slow. This was also the case with my older Windows 8 OS, with ArcGIS Pro 1.4 and ArcGIS Pro 2.0.

All things begin equal, ArcGIS Pro is very slow with this large SDE connection and doesn't remember connections. ArcCatalog works like a champ.

The network is fast, the database server is fast, everything is fast. ArcGIS Pro is slow with the large SDE connections. This was true on my previous setups and is still true today with a new setup.

Plus that Esri expert admitted they had issues with Pro doing buggy stuff with SDE connections.

I'll create a support ticket.

[alex_friant · Accepted solution]

Updated:

Discovered the issue: Metadata Geoprocessing History

Cleared out the Geoprocessing History from the Metadata for our large SDE geodatabase by running this Python code in ArcMap's Python Console. 

##Change this to point to your SDE connection file

gdb = r"C:\Users\...\AppData\Roaming\ESRI\Desktop10.5\ArcCatalog\SDE@YOURGDB.sde"

##Change this to match your file structure and location
remove_gp = r"C:\Program Files (x86)\ArcGIS\Desktop10.5\Metadata\Stylesheets\gpTools\remove geoprocessing history.xslt"

##Change this loaction to a temp folder to output the XML
name_xml = r"C:\temp\cleanup_staging"

arcpy.XSLTransform_conversion(gdb, remove_gp, name_xml, "")

arcpy.MetadataImporter_conversion(name_xml, gdb)

print 'completed'

This reduced the size of our GDB by more than 50%!

After that, when in ArcGIS Pro, connecting to the GDB went down from 1.25 minutes to just 5 seconds. Fantastic.

The bonus is that it sped up connection times to SDE in ArcMap as well.

Thanks Esri support!

[MichaelVolz]

Thanks for sharing the solution to your problem.  This information will probably come in handy when my organization migrates our enterprise system from ArcMap to ArcPro.

[DanPatterson_Retired]

Alex, do you remember the size of the files in that folder vs the size of the SDE geodatabase... I can't find anything greater than a couple of kb in any of mine

[alex_friant]

I got the size from a query Esri support suggested. It went from 60 MB to 25 MB. That means there was 35 MB of GP history in the metadata that got deleted.

I don't know why it takes so long to read 35 MB of data for modern software, but that's the way it goes with Esri software. It can do amazing visual gymnastics with 3D data in realtime, but it takes over a minute for it to read 35 MB of text, essentially. Go figure.

 

[Asrujit_SenGupta]

There is a Desktop Add-In as well, in case anyone wants to try that out:

Clear Geoprocessing History

If you look inside the XML under that Documentation field for 'Workspace' in the GDB_ITEMS tables, it will show you something like this:

This can become so huge at one point that SQL Server Management Studio refused to open up the XML file in one of the Customer's environment. Clearing the GP History improves the connection speed in ArcCatalog and ArcPro drastically, as you observed.

Bill Fox‌