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,

when you execute the query in SQL studio, it only fetches a certain amount of rows. When you execute the query in Java (which is the case with uSelect), it queries all data and reads everything into the memory allocated to the according java process, that is why it is taking much longer.

What I am basically telling you is, you would need to reduce complexity of your query or optimize it in order to get better runtime.

Maybe it helps you to use group by logic like

select mskey,

max(casewhen AttrName ='MSKEYVALUE'then aValue end) mskeyvalue,

max(casewhen AttrName ='DISPLAYNAME'then aValue end) DisplayName,

max(casewhen AttrName ='MX_ENTRYTYPE'then aValue end) EntryType

from idmv_value_basic

where attrname in('MSKEYVALUE','DISPLAYNAME','MX_ENTRYTYPE')

groupby mskey

Explained here:

here

Regards

Norman

[former_member557511]

Hi Norman,

thank you for your answer.

I have the impression that SQL Studio fetches all of the data, as after 5 seconds I'm able to export the results to a CSV file and all of the approx. 17.000 rows are exported which is ~5 MB of data. So I can't explain why it takes soo much longer from an IdM process.

You mentioned the memory allocated to the java process. Can this be adjusted so that more memory is allocated to it?

Thank you,

Zoltan