Skip to Content
0
May 17, 2018 at 08:31 AM

uSelect query takes very long

280 Views

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