524 views

Loading a specific table takes longer than expected | Known Error




Symptom
  • Loading a specific table takes longer than expected. 
  • A query that runs a large amount of records takes too long to complete.
  • Query results are automatically sorted by the Order field.  

 

Cause

When running a query in the ServiceNow base system, the system, by default, sorts the table records by Order. Queries that have an order by on a table field that is not indexed can cause the system to run for several minutes and impact performance levels. 

 

Workaround

Understanding how the sort by Order column is added on the query helps to optimize the database and improve performance. The sort by Order is set by and stored as a user preference for a table. User preferences allow administrators to adjust certain feature settings. To view a list of user preference records in the base system, refer to the User Preference List.

The preference <table>.db.order indicates which column is used to sort the records in a list. This record updates automatically each time the user changes the sort order for the table. The user must select the default column. If a default column is not selected, the query sets the default value to Order to sort the table records. It then reviews the input and if the Order field is empty, the Number field is used. If the Number field is empty, the Name field is then used. This means you will get the ORDER BY statement in your query every time. 

Removing or deleting user preferences sets the preference record to None and allows default behavior to take place. When deleting the user preference, it is important to clear the user and database cache. Otherwise, it will keep running the cached queries before it actually starts using the new database statements.

 

Note: The database used does not make a difference. ServiceNow handles relationships and queries on the application level.

 

 

To set the field value of your choice for every table:

    1. Log in to your ServiceNow instance as an admin.
    2. Navigate to User Administration > User Preferences.
    3. Search for and select the <table>.db.order preference record you want to update.
    4. In the Value field, set the desired field name.
    5. Verify the User field is empty. This field shows the name of the user for whom the setting is customized. If User is blank, the record is for a system-wide default. 
      Note: You can also make or edit <table name>.db.order.direction with the same recommendation for the User field. 
    6. Click Update

Article Information

Last Updated:2017-10-26 13:22:44
Published:2013-01-09