ArcGIS Server 10.7.1: What is the best practice to improve the performance of a versioned geodatabase feature class?

[JamalNUMAN]

ArcGIS Server 10.7.1: What is the best practice to improve the performance of a versioned geodatabase feature class?

I couldn’t figure out the best practice to improve the performance of a versioned geodatabase feature class.

[Anonymous User]

Are you working with branched or traditional versioning?

[JamalNUMAN]

Traditional! but what if it's branched?

[Anonymous User]

Hi Jamal

I think if you are going to use map/feature services, you might need to have a look at branch versioning.

Editing and Administration with Branch Versioning – It’s Showtime! 

Regards,

Bing

[DavidHoy]

Hi Jamal,

to answer your original question

hands down the most important thing you can do to keep the performance optimized for your traditionally versioned geodatabase is to introduce a regime of reconcile, compress and rebuild statistics. (ideally as a scheduled python script)

If you dont do this, the state_lineage tree that determines the number of internal datatabase queries (between the sde_states, sde_state_lineage and the base+A&D tables that make up the versioned feature class)  that are required to return the "current" value of your feature classes can become enormous, particularly if the class is being edited frequently.
I have seen sites where there was no compress happening for months and once performed, queries dropped from minutes to sub-second.

[JamalNUMAN]

Many thanks David for the help.

How about “rebuild indexes” tool? Has it something to do with enhancing the performance of the a versioned feature class?

[George_Thompson]

Here is the recommend workflow David references: Recommended traditional version administration workflow—ArcGIS Pro | Documentation 

Here is the script to run the process: Using Python scripting to batch reconcile and post traditional versions—ArcGIS Pro | Documentation 

In my experience running all the tools in the order above for both the SDE and Data Owners schemas are beneficial for performance.

[JamalNUMAN]

Thanks George

I couldn’t figure out which tools are doing what!

Which tools are used to improve the display performance? As I got from the material, this is the function of analyze, and compress

Which tools are used to improve the query performance? This is the function of “rebuild indexes” tool

Am I right?

But how the “rebuild indexes” tool is different from “recalculate spatial index”? is the “recalculate spatial index” available as Geoprocessing tool?

[George_Thompson]

Rebuild indexes also rebuilds attribute indexes, if present. I think that all of the tools together improve performance. There are times when a single tool (indexes or statistics) will improve the performance but hard to know when. If you keep the maintenance up-to date then that is the best performance you can get.

Recalculate Spatial Index - Modify a spatial index—ArcGIS Pro | Documentation 

Now note that there may be other causes of poor performance besides the Enterprise Geodatabase, i.e. server / network / client issues.

[JamalNUMAN]

Sure I see.

Is it more accurate to say that compress and analyze are for improving display performance while “rebuild indexes” are for improving query performance?