I have a layer that is created by a python script that is used in portal and map service. It is in a SDE database and the script deletes all the features and then appends the new features that are created by the script. The issues that I am having is that the more and more I run the script it seems as the feature class/layer gets slower. For example if I try to preview the feature class in ArcCatalog it takes a long time to display the feature class, longer than it should. The same goes for the map service, this feature class in a Map Service. The a query from this map service takes a long time. What is the issue? Is there a better way to delete features and append features?
Are you compressing the database at all in any of your database maintenance routines? There are some caveats to this so it may/ may not be applicable to your environment/ database so check out the Usage.
@TonyAlmeida I second @Anonymous User for compressing the geodatabase. You mentioned your feature class is versioned, so each time the delete features/append occurs you are adding records to the delta tables (A & D tables). If there are 10,000 features, you are adding 10,000 new rows to the A table, and 10,000 new rows to the D table. ArcGIS queries these tables to display/query the feature class. The larger these tables are, the longer it will take to query/display.
In order to compress the geodatabase, you will want to make sure there are no locks on the feature classes. This means, you'll need to stop all ArcGIS Server services referencing data from your Enterprise Geodatabase. Here is a tool that may be helpful for you: