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

[Chenyang]

Hi Zoltan,

You can try split the task in two steps.

1 select everything into a temp table.

2 toASCIIfile - export the content of the table into a CSV file./ or any other way to continue processing the data

Based on your description, step 1 will take only a few seconds to complete. Depends on the data volumn and server performance, step 2 could take 1-2 minutes to complete downloading 17K records.

Cheers

Chenyang

[former_member557511]

Hi Chenyang,

it is the query which takes a very long time if it is executed from a SAP IdM process. The CSV export is quick. I have also tested the uSelect with a very simple SQL like:

select top (20000) * from idmv_bw_current_values

And this also took hours to complete.

Kind regards,

Zoltan

[Chenyang]

Hi Zoltan,

I understand the issue is from uSelect. That's why I suggested to bypass this function.

Step1 is just a SQL update action, you can do something like this

SELECT COL1, COL2, COL3 INTO TABLE XYZ. Since this is a SQL update and it runs in database directly without output, it should be efficient. Then in step2, you dump the table into a text file.

Cheers

Chenyang

[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

[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