Rollback Window Opens Slowly for ALS Users

<< Click to Display Table of Contents >>

Navigation:  EDP > Troubleshooting >

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);

 

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.