Rollback Window Opens Slowly for ALS Users

Affected Software/Reports/DLL(s): EDP

Symptoms: When an Application Level Security (ALS) user tries to open the "Rollback" screen in EDP, there is a very long delay before the list of EBATCH records available for rollback is displayed.

Cause/Details: Security checks being performed at the database level by the EQUIS.AUTH() function may take a long time to return data. This function is used to determine which EBATCH records are available for you to roll back; these checks are a necessary part of protecting data but can impact performance.

Resolution/Workaround: To improve performance of the EQUIS.AUTH() function and therefore the EDD Rollback process, several indexes can be added to the database. The following indexes, by table, can significantly improve the return of the rollback list for ALS users.

Table to Build Index On	Column to Index	Additional Column(s) to Include in the Index
ST_FILE_REGISTRATION	ebatch	facility_id
DT_LOCATION	ebatch	facility_id
DT_SAMPLE	ebatch	facility_id
DT_WATER_LEVEL	ebatch	facility_id
DT_FACILITY	ebatch	facility_id

Following commands in SQL Server Management Studio will create the indexes listed above in an EQuIS database running on Microsoft SQL Server:

CREATE INDEX IX__st_file_registration__ebatch ON st_file_registration (ebatch) INCLUDE (facility_id);

CREATE INDEX IX__dt_location__ebatch ON dt_location (ebatch) INCLUDE (facility_id);

CREATE INDEX IX__dt_sample__ebatch ON dt_sample (ebatch) INCLUDE (facility_id);

CREATE INDEX IX__dt_water_level__ebatch ON dt_water_level (ebatch) INCLUDE (facility_id);

CREATE INDEX IX__dt_facility__ebatch ON dt_facility (ebatch) INCLUDE (facility_id);

Adding an index to a table has the potential to slow down data loading for that table. After an index has been added to any of the above tables, if inserting or updating data to the table becomes noticeably slower or if the index is no longer needed, the index can be removed so it does not impact data loading.

Note: In one test scenario, applying these indexes improved execution times from approximately 150 seconds down to approximately 30 seconds. Remember that performance will vary from one database to another.