uSelect query takes very long

[former_member557511]

Hi IdM Experts,

I wanted to build a reporting functionality that can be triggered through a UI task (Report Form). The UI task starts a process (type: form action process) which uses a script with an SQL query to grab the data from the database. I'm using the built-in uSelect to query the database.

The SQL-Query is quite complex but it is executes within 5 seconds when I start it in MSSQL Server Management Studio. However when the process executes the query it takes ages to finish (3000 - 5000 seconds). I have rewritten the query using different views but it did not help.

Any help is appreciated! Kind regards,

Zoltan

SAP IDM: 8.0.4

Here is the latest version of the query:

SELECT li.mcThisMSKEYVALUE as Username, en2.sval as Firstname, en3.sval as Lastname, en4.sval as 'Display name', li.mcOtherMSKEYVALUE as 'Role ID', en5.sval as 'Role name', en.mcMSKEYVALUE as 'Management Unit', convert(varchar(20), li.mcValidFrom, 20) as 'Valid from', convert(varchar(20), li.mcValidTo, 20) as 'Valid to', convert(varchar(20), mcAddedTime, 20) as 'Request date', en6.sval as Initiator, REPLACE(REPLACE(REPLACE(li.mcReason, CHAR(13), ''), CHAR(10), ''),';',':') as 'Reason for request', convert(varchar(20), en7.mcDate, 20) as 'Approval Date', en7.mcResponsibleDisplayName as Approver, REPLACE(REPLACE(REPLACE(en7.mcReason, CHAR(13), ''), CHAR(10), ''),';',':') as 'Approval reason', convert(varchar(20), li.mcModifyTime, 20) as 'Assignment date', en8.sval as 'Inactive', 'lb' FROM [mxmc_db].[dbo].[idmv_link_ext] li WITH (NOLOCK) left join [mxmc_db].[dbo].[idmv_entry_simple] en on li.mcContextMSKEY = en.mcMSKEY left join [mxmc_db].[dbo].[idmv_vna] en2 on li.mcThisMSKEY = en2.MSKEY and en2.AttrName = 'MX_FIRSTNAME' left join [mxmc_db].[dbo].[idmv_vna] en3 on li.mcThisMSKEY = en3.MSKEY and en3.AttrName = 'MX_LASTNAME' left join [mxmc_db].[dbo].[idmv_vna] en4 on li.mcThisMSKEY = en4.MSKEY and en4.AttrName = 'DISPLAYNAME' left join [mxmc_db].[dbo].[idmv_vna] en5 on li.mcOtherMSKEY = en5.MSKEY and en5.AttrName = 'DISPLAYNAME' left join [mxmc_db].[dbo].[idmv_vna] en6 on li.mcAssigner = en6.MSKEY and en6.AttrName = 'DISPLAYNAME' left join [mxmc_db].[dbo].[idmv_historic_approvals] en7 on li.mcUniqueID = en7.mcLinkId and en7.mcOperationText = 'Approved' left join [mxmc_db].[dbo].[idmv_vna] en8 on li.mcThisMSKEY = en8.MSKEY and en8.AttrName = 'MX_DISABLED' where li.mcAttrName ='MXREF_MX_ROLE' and li.mcContextMSKEY is not null and ((li.mcLinkState = 0 and li.mcExecState = 1) or (li.mcLinkState = 1 and li.mcExecState = 512 and li.mcValidFrom > GETDATE())) order by li.mcThisMSKEYVALUE, li.mcOtherMSKEYVALUE

[normann]

Hi Zoltan,

You can add Java VM parameter in dispatcher settings. It takes lot of CPU time to allocate new memory, hence it would make sense to tune VM parameters, but those parameters would affect all Idm processes unless you use a dedicated dispatcher only for heavy operations like that. Anyway, I wouldn't expect too much from that, but I'm curious to see whether it makes a big difference.

Regards

Norman

[former_member557511]

Hi Norman,

thank you very much, I will try adjusting the JAVA VM parameter.

What I also tested today how uSelect reacts when you run a very simple query which pulls a large amount of data. I used:

select top (20000) * from idmv_bw_current_values

It finishes under 1 sec in SQL Studio and needs hours to finish in SAP IDM using uSelect. So it seems that large junks of data can't be handled correcly. Probably it is also an issue SAP could cast an eye at?

Kind regards,

Zoltan

[former_member557511]

Hi Norman,

the customer was not available in the past couple of weeks so I was only able to check with them yesterday the dispatcher settings. Unfortunately I was not able to locate where to change the settings, nor was I able to find any information about that.

Could you please point us in the right direction?

Thank you very much,

Zoltan

[normann]

Hi Zoltan

please check in properties of dispatcher -> tab policy -> Java Options

Best Regards

Norman