How to speed up lists on large tables

[ceskie]

Ahoy, we are one of the numerous tenants on an instance and our users are complaining that some lists take too long to open (30-60 sec per page load). Proper indices are already in place. The fact is that our affected tables inherit from task and there are currently 40 M records in task table, and many customizations. Any ideas how to speed up the performance? As I said, I don't think new indexes would help and task archive rules are also out of the game (table exists for 2 years and users still need all records there). Is it just "what it is" and users must wait a minute for each page load or is there anything we can do? Thank you

[ceskie]

Ahoy, its not our case, all our users have full read access on our (affected) scoped tables 

[sonali panda1]

The filters used should be like ,(is one of; is not one of ) instead of or or and operator. So that the processing time is less. Ensure you have indexes in place. There is a count on max indexes that can be used. Have a HI ticket raised so they can check which indexes are not used much so they can take your permission to remove some unused indexes and add appropriate indexes so that loading speed increases. Also see what is the size of the instance and if you can increase to higher level so that processing is fast. You can also try to archieve some volume of old data so that you can free up this table space and automatically increase the performance.

[Hitoshi Ozawa]

Hi Ceskie,

Beside removing total count, consider followings:

1. limit number of column displayed.

https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0786335

2. Check calculated fields and query time. Check the accepted solution in the following thread.

https://community.servicenow.com/community?id=community_question&sys_id=281a4769db5cdbc01dcaf3231f96...

[ceskie]

One more questions please, do you think/know what is the archive rules impact on list performance? For example if there are 20M records in table and we archive 15M, would the table run 4x faster? Thank you

[sonali panda1]

Hi

Please go through this before achieving so you are aware of what to expect-

https://docs.servicenow.com/bundle/sandiego-platform-administration/page/administer/database-rotation/concept/c_ArchiveData.html

Regarding the speed, it will be faster. Theoretically it should be 4x times faster, practically it may not be that but near to that. I cannot comment on this as I am not sure how fast its going to be. Less records better and faster performance for sure.

Note: If this has answered your question, please mark Helpful /Answer.

Thanks

Sonali