Add index in SF for performance optimization in IAM synchronization

  • 1 April 2022
  • 3 replies
  • 44 views
T
Rank
Userlevel 1

We are looking to upgrade our TW software to version 2022.1, and during my tests I encountered some performance issues while synchronizing our latest version. Our performance monitor pointed towards the function look_up_chain_granted. This function is called around 1.5 million times, as our application contains a high amount of Roles. At 1.25ms per execution this could take a very long time. The performance index suggested adding the index stated below and this decreased the avg execition time to 0.075ms, which helped with decreasing the total sync time from almost one hour to just ten minutes.

I would suggest Thinkwise take a look at this suggestion as it has helped us very much.

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname, >] ON [dev_sf_upgrade_test_2022].[dbo].[ref_col] ([project_id],[project_vrs_id],[target_col_id]) INCLUDE ([abs_order_no])

 

3 replies

Mark Jongeling
Rank
Userlevel 7
Badge +23

Hi Thomas,

Thank you for your suggestion and help to improve the platform. I have converted this topic to a conversation as it is not an Idea that needs votes. We'll take a look at this and report our findings back here. 

By the way; What code did you execute to find out this Index was missing? Having a reproduction query makes it more easy for us to pin-point the place to be improved.

"Wake me up before you low-code"
T
Rank
Userlevel 1

Hi Thomas,

Thank you for your suggestion and help to improve the platform. I have converted this topic to a conversation as it is not an Idea that needs votes. We'll take a look at this and report our findings back here. 

By the way; What code did you execute to find out this Index was missing? Having a reproduction query makes it more easy for us to pin-point the place to be improved.

Hi Mark,

Thank you for your quick reply!

It is hard for me to pinpoint the exact code, but I think is was the view i_sync_role_col, which contains the function in some where clauses. I also believe one of your colleagues that help us once collected our SF for reproduction purposes for you guys, as we have a very large amount of roles. Is this enough information for you?

Mark Jongeling
Rank
Userlevel 7
Badge +23

We'll make it work, it is indeed the view i_sync_role_col. It is going to be investigated, thanks again!

"Wake me up before you low-code"

Reply


Terms & ConditionsCookie settings