Very poor performance of IDM-UI Todo-Tab

[markus_sauer]

Hi!

We are observing  very poor performance of the IDM UI ToDo tab.

I sometimes takes about 10min++ to generate the list of open approvals. If the list is generated at all.

Can anybody describe how the list ist generated?

I would like to know which Views/SPs/Requests the UI is sending to the database to generate the list of open approvals.

Maybe I can track down the bottleneck....

Some further informations:

- We are not using the pending-Value approval mechanism! We are generating entries that are representing request. Thes request objects then are processed within a workflow that contains approval-steps. For each approval step an ACL is defined.

The ACLs used for the approvals are not the root of the performance problems, I am relatively sure about this. 

- We are using IDM 7.2 SP7 and MS SQLServer DB.

- We have about 3500 open approvals.

BR,

Markus

[Former Member]

What does your ACL settings look like? Are the approvers fixed users, roles or SQL Filters? It doesn't help if your ACLs are quick if it has to be run for each of the 3500 pending approvals you have each time someone opens their To Do tab.

Br,

Chris

[markus_sauer]

Hi Chris!

I fully aggree that checking the open approvals against the ACLs each time someone opens the tab is the root of the problem.

We are using  SQL-filters to determine the approvers and the approvees.

The statements are looking like this :

SELECT distinct mcOtherMSKEY FROM idmv_link_basic_active with (nolock) where mcAttrname = 'G_GENEHMIGER' and mcThisMSKEY = %USERMSKEY%

What I am trying to find out is how the SQL-Statement the UI sends to the dateabase exactly is looking like. Maybe the view or SP can be streamlined a little bit.

Any idea?

Markus

[Former Member]

When you use a SQL Filter in the APPROVER part of the ACL we don't have any other option than to go trough every single queued entry for that task and run the statement with the logged in users MSKEY...

If I remember non PVO approvals correctly this is done in mxp_get_approvals, which now calls mxp_get_old_approvals if the old approval mechanism is in use.

The mxp_get_old_approvals procedure creates a cursor for all entries in the view mxpv_approval to get the ACLs of queued approvals. Then it loops the result of this cursor through the procedure mxa_check_access.

mxa_check_access which checks if the logged in user (%ADMINMSKEY%) matches the approver ACL using the procedure mxp_Check_SQLmember. There is logic in there that the approver is only checked ONCE per approval TASKID/ACL combination. BUT if you have a SQL Filter in the approvee as well, it needs to check if the target entry matches for this approver as well and that has to be done for each entry...

So, 3500 approvals with ACLs on both approver and approvee queued in the system will probably be an issue.

If you enable treshold logging you should see which of the approval listing procedures are using the time as well, and if its your ACL or just the number of approvals queued causing the issue.

I also want to mention that in 7.2sp8 (or perhaps its sp9)  so we tried caching the results of these approval ACLs so that they were executed once per user. We added a flag on the mxi_entry to indicate the last datetime the approval listing was performed and then only checked the ACLs for approvals created after that time. This promptly caused some uproar because now you lost the dynamic real-time listing that caused the bad performance to begin with, and required us to implement a "clear cache" function...

Br,

Chris

[markus_sauer]

Ok!

Thanks a lot!

These informations were very helpfull!

Markus