Create SQL Server Index on [GDB_TO_DATE] column?

Question

Background

I maintain an ArcGIS Enterprise Portal v11.3.0, with a SQL Server data source.

Problem

We have an Experience Builder app whose REST API calls were running slowly (8 seconds for initial page load). I traced the speed issues back to the database server.

What I've tried

I consulted SQL Server's Missing Indexes table, and it suggested indexing the  [GDB_TO_DATE] column (which I believe is part of the versioning feature).

Running SQL Profiler, I see that the Experience Builder queries always include the following, which I assume means "return the current version of the record".

WHERE GDB_TO_DATE = '9999-12-31 23:59:59'

I've been told that it's preferable to manipulate the ArcGIS database via ESRI tools. But when I attempted to create an Attribute Index against [GDB_TO_DATE], it's not an option in the list of columns.

Question

1. Is it safe to bypass ESRI tools and create the index directly on SQL Server ?

2. If not, then is there a way to create this index with ESRI tools?

3. Is there a different way to resolve the slow Experience Builder queries?

Walter · Answer

Thanks for the reply Vince.

gdb_to_date is part of Archiving, not Versioning, per se

I misspoke in my OP, the table in question has Archiving enabled, not Versioning.

Enabling Archiving creates a bunch of indexes on the _H table

I don't have any _H tables.

How many rows in the table are there, and how many of them have been deleted

Total Rows: 7,040,948
Deleted Rows: 6,926,377
Difference: 114,571

Do these numbers look bad? Is that why queries are so slow?

Note that this is probably more of a geodatabase (Data Management) question ...

I apologize for the mix up. This is my first time posting on these forums, can the post be moved?

Also, I'm not actually that proficient in ArcGIS knowledge, I'm a developer/sysop providing technical support to my GIS colleague.

VinceAngelo · Answer

gdb_to_date is part of Archiving, not Versioning, per se.

Enabling Archiving creates a bunch of indexes on the _H table. It's not generally necessary to create additional indexes (which could harm performance more than help it), and doing so outside of supported tools might result in issues upgrading your geodatabase.

This seems to be an XY Problem question. Instead of asking about the solution you're working, it might be better to ask about the root problem, of archived table performance.  It's quite possible that the table size and database server characteristics have a lot more to do with this query latency than an index on a column with low selectivity. (How many rows in the table are there, and how many of them have been deleted?)

Note that this is probably more of a geodatabase (Data Management) question than an Enterprise one.

- V