Database connection performance

[jorisfrenkel]

Hi all,

I wanted to look into our database performance, since every time we open a connection to it, it takes around 20 to 30 seconds to open. We have an enterprise geodatabase in SQL Server, and have around 700 feature classes in it. So I am wondering if 30 seconds is a normal time to wait for such a database connection to open. It seems ArcGIS Pro is a little bit faster than ArcMap.

I used sdeintercept to create a log and found that there is one big time consuming operation, which is the ExecuteSpatialQuery, by far the most time consuming step in the whole log. 

I am wondering why a spatial query needs to be executed when opening a database connection.

Here's a sample:
========================================
[W 16:42.941] Command: GetDbmsInfo
[R 16:42.941] Long: 0
[R 16:42.941] Long: 5
[R 16:42.941] Long: 1096810511
========================================
[W 16:42.941] Command: ExecuteSpatialQuery
[W 16:42.941] Long: 1
[R 17:02.648] Long: 0
========================================
[W 17:02.648] Command: NextBuffer

I hope anyone can shed some light on this, and would also like comments on whether this is a normal wait time for opening an enterprise geodatabase with such a number of feature classes.

Joris

[VinceAngelo · Accepted solution]

If you actually trace the SDE API calls, you'll see it's not the connecting, per se, but the need to inventory the objects present so they can be symbolized in the UI.  There are queries to list tables, and feature classes, and feature datasets, and rasters, and raster datasets,...  Then for feature classes, the geometry type needs to be queried, so the right icon is used. So yes, thirty seconds is not completely unreasonable for 700 tables.

[jorisfrenkel]

Thanks, that's very useful.