Optimization of a huge sys_user table for better performance

[Agata5]

Hello Community, 

I'm searching for solutions to improve the performance of  sys_user table with more than 5 mln records (including inactive ones).

The pain points today are mostly:

- the table is loading for ages

- referencing the user record (even in catalog builder) is nearly impossible

- searching for the user / filtering the table is possible but it's not ideal (using: .filter)

My ideas (I'm aspiring developer / architect so please be patient with my comments):

1. Filter out the sys_user table to hide the inactive records and ideally archive them

2. Change of the Table list view

3. Table indexing

Is there any insights or similar case that was witness by anyone in here? 

Are these all the options to optimize the user experience and performance of the platform?

I would much appreciate all the comments.

Thank you, 

Agata

[Mark Roethof]

Hi there,

5 million is a lot, though shouldn't be that bad honestly. So try to find other causes. Are there for example extremely big row sizes? Were hundreds of custom fields added and in use? Are there query business rules, client scripts, bad ACs etc in place? Ofcourse indexing can help, though try to find the rootcause.

Regarding huge row sizes, you can create a support case, ServiceNow support can provide you such insights.

Archiving is not the solution.

[HIROSHI SATOH]

If there are many inactive users, it is effective to use "Before Query business rules" to prevent inactive users from being referenced by anyone other than Admin. Please refer to the documentation for details. The settings are easy, but please be careful about how you use it.

Before Query business rules

https://docs.servicenow.com/ja-JP/bundle/washingtondc-platform-security/page/administer/company-and-...