Summary
I'm writing this article to talk about some of the performance implications of Database Views. I've posted a video at https://youtu.be/ygy50Pxn3wk?si=l7dI3apveDtZTcln.
What are Database Views?
Real quick - in ServiceNow, the concept of a Database View is very similar to the concept of a Database View in Relational Database Management Systems (RDBMS). In RDBMS, a view is a virtual table that's derived from one or more base tables. It is not a physical table, it is a saved SELECT statement. When you query the View, the saved SELECT statement will execute dynamically and present the results as a new table. Similarly, in ServiceNow, a Database View is a way to access one or more tables as if they were a new single table. The most common use case for a Database View is to JOIN data from various tables and present it as if it was a single table.
Performance RISK: the JOIN operation
If your Database View returns data from more than one table, then it will require what is called a Database JOIN operation. In order to JOIN two tables, the Database View requires a join condition for matching values from a column in one table to values in a column from another table. In ServiceNow this almost always means matching the sys_id column of one table (i.e., the Primary Key) against a reference field in another table that points to the first table (i.e., a Foreign Key).
When performing the JOIN operation the database does not necessarily start with the first table defined in your Database View. It can start with either table and then look for matches in the other table. For example, suppose you are joining task.opened_by to sys_user.sys_id. The database could grab all users in the sys_user table and look for any task records whose opened_by field matches any of their sys_id's. Or the database could start with all the task records and look for any sys_user records whose sys_id's match their opened_by fields. The table that the database starts with is often called the "driving" table. The first step in the join is to grab a group of records from the driving table and then match those records against the second table. If the group of records from the driving table is very large (e.g., hundreds of thousands) then the join operation might be slow. In addition, if the second table is very large then the join operation will be even slower since the database must try to match every record from the driving table group to every record in the second table.
In order to make the matching process fast, the Database prefers to drive from the smaller side. This doesn't necessarily mean the smaller table, because the WHERE clause of the query might reduce the results from one table to a small number. If the Database can use a condition in the WHERE clause to quickly reduce the number of records from one table into a small group, then it will do that first before using that table as the driving table for the JOIN operation.
What this means for you is that you should plan to access your Database View using a condition on a column that can quickly reduce the size of one of the tables in your JOIN. The most common example of this is to include the condition: active = true. This can quickly reduce the number of records in the JOIN allowing it to execute quickly; assuming that the percentage of active records in the table is sufficiently small.
Notice that I said you should use a condition on a column that can quickly reduce the size of one of the tables in your JOIN. In order to do this reduction quickly, you need a DB index on a selective field in your query. DB indexes are beyond the scope of this article, but you might want to review the following articles:
The following doc page explains how to create a table index:
https://www.servicenow.com/docs/bundle/yokohama-platform-administration/page/administer/table-admini...
Here are some additional references about how and why to use indexes:
1. Using SQL Debugger to troubleshoot slow queries
https://www.servicenow.com/community/developer-blog/using-sql-debugger-to-troubleshoot-slow-queries/...
2. Best practices for efficient queries
3. Improve performance by creating database indexes via the User Interface
https://www.servicenow.com/community/developer-blog/improve-performance-by-creating-database-indexes...
4. Improve performance: database indexes and slow queries
https://www.servicenow.com/community/developer-blog/improve-performance-database-indexes-and-slow-qu...
5. Use the Slow Query Log
6. What index should it be? - OR - Slow Queries explained
https://www.servicenow.com/community/developer-blog/what-index-should-it-be-or-slow-queries-explaine...
7. Index suggestions for slow queries. This tool, introduced in Jakarta, automatically suggests database indexes that might improve the slowest queries in your instance.
https://www.servicenow.com/docs/bundle/yokohama-platform-administration/page/administer/platform-per...
Performance GAIN: return only desired fields
When querying a table from a List or via GlideRecord in ServiceNow, the database returns all the fields, regardless of if you are interested in using those fields. For tables with over one hundred columns, this can impact performance. There is no ability in the List or GlideRecord APIs to limit the columns that should be returned from the database. However, Database Views do have the ability to explicitly define which records should be returned from the database. One potential performance improvement then, is to create a Database View for a single table and include only the desired fields. You can use this field instead of the original table when you want to expedite retrieval of just a subset of the table's fields.
To display specific fields when using a database view in ServiceNow, follow these steps:
- Open the Database View: Navigate to the database view you've created.
- Open the "View Table" record: For each table included in your database view, open the corresponding "View Table" record.
- Configure 'View Fields': In the "View Fields" related list (located within the "View Table" record), you define which fields from that specific table will be displayed in the database view.
Reference:
KB0719186 - Filtering using the 'Where clause' in database views
