Performance issue after CSM installation (sys_user table affected)

[Radim4]

Hello everyone, 

I would like to ask for your advice and possible hints to help resolve the issue we're facing on customer production instance. 

Issue:

Performance issue with loading forms which contains ref. to sys_user table. 

After the CSM plugin installation, the issue started appearing, for example when you open the form, the watchlist starts loading and the transaction is cancelled due to exceeded wait time, or when you try to open lookup on Assigned to field, the loading takes around 20sec. 

Cause:
We're aware about the source of this issue - Together with CSM installation the new table customer_contact was created (sys_user is parent). Our requirement was to import around 1.5mil records into the customer_contact table. For this reason, everytime the sys_user is queried, it goes through all 1.5mil records, instead of previous 200k. 

Possible solution:

This is the part where I would appreciate any help or hint leading to performance improvement for these queries. We tried to use sys_class_name=sys_user in our ref. quals, but unfortunately, this has no effect on performance. The only workaround we applied so far on watchlist variable is to use attribute glide_list, which won't load the users initially and this helped with long transaction being cancelled on load, but as mentioned, this is only workaround for watchlist. 

Thank you in advance.

[Sandeep Rajput]

@Radim4 Did you try using sys_class_name!=customer_contact in the reference qualifier?

[Radim4]

Hello Sandeep,

Thank you for your response! I must say that using both conditions 

sys_class_name!=customer_contact^sys_class_name=sys_user^

does make difference with loading time. I can see it for example of watchlist variable, when the error popped up with transaction timeout, now the error doesn't display, but the loading is still longer than before populating customer_contact table and in case user starts doing some other operations on form, before it's fully loaded, the error with transaction limit is displayed. So your advice is definitely beneficial, but the problem is not fully solved. I'll try to play around bit more with ref qual and using "duplicate" conditions as above. Thank you