What is the best way of using HR Criteria without impacting application performance?

[Rishi Raj2]

HR criteria defines the audience for HR content, services, or cases. You can make information available to or create an HR case for specific groups, individuals, or to all employees using HR criteria.

It allows you to create conditions on custom tables and/or custom columns. At the same time you can create conditions on existing non-indexed columns.  

To ensure that such HR Criteria’s don’t adversely impact the performance of the system when they are executed, it is essential that you review the selectivity of the columns used in the condition AND appropriately create indexes on those columns.

Ex:

Let us say you create a column in sn_hr_core_profile, called u_tax_jurisdiction. You expect this field to be selective, having values like Americas, EU, Latin America, Asia, etc.

If this col doesn’t have an INDEX and you build an HR Criteria like

Name: Latin America Tax Jurisdictions

Table=sn_hr_core_profile

User Col=user

Condition

u_tax_jurisdiction=‘Latin America’

Then this HR Criteria can significantly impact the performance where ever used, as it will do a full table scan, given no indexed column is used in the filter condition.

However, when you create an index on u_tax_jurisdiction field - the database will use the relevant index and the query will perform better.

Related Documents on Indexing tables:-

1. Adding index to column:- https://docs.servicenow.com/bundle/geneva-servicenow-platform/page/administer/table_administration/t...

2. Use slow query Log to identify queries impacting performance:- https://docs.servicenow.com/bundle/newyork-platform-administration/page/administer/platform-performa...

3. Index suggestions for slow queries:- https://docs.servicenow.com/bundle/newyork-platform-administration/page/administer/platform-performa...

4. Generate an Index creation for a slow query:- https://docs.servicenow.com/bundle/newyork-platform-administration/page/administer/platform-performa...