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