Preface
The range of functions available in ServiceNow is very broad. Some of them are well-known and widely used, and there are others that are less familiar, but real hidden gems. I would like to detail one of them, which can be used to reach significantly improvement in the data queries. This is the addExtraField function in the GlideRecord class, which - based on my experience - belongs to the previously mentioned category.
There are several contents available on this topic. In this article, I would like to highlight why we need to consider using this function.
Let me give a short summary of the function, in case you are not familiar with it.
The addExtraField function in ServiceNow GlideRecord class is used to include additional fields in the query result that are not normally retrieved.
Key Points:
- It adds extra fields to the result set of a GlideRecord query.
- Useful when retrieving fields not included by default in queries.
- Works before calling .query() to modify the data selection.
If you would like to read more information you can reach content from HERE, and HERE. This is also great one.
The question I would like to answer in this article is: why does it add value to data collection, visualisation or even reporting? Read on for details.
From Database Perspective
First, we need to understand what happens at application and database layers in the system, when we try to retrieve data from the database. Let's take the following example:
We need to retrieve some records from the User (sys_user) table and generate a JSON output.
The following code snippet represents one possible solution:
getUserData: function() {
const userGr = new GlideRecord("sys_user");
userGr.addEncodedQuery("departmentISNOTEMPTY");
userGr.setLimit(1);
userGr.query();
let userData = [];
while (userGr.next()) {
userData.push({
id: userGr.getUniqueValue(),
name: userGr.getValue("name"),
email: userGr.getValue("email"),
department: userGr.department.name + "",
department_head: userGr.department.dept_head.name + ""
});
}
return userData;
},
Only one user is queried from the database (userGr.setLimit(1)) so the result is a JSON array with only one element:
[
{
"id": "00002e6ec3779210fc037205e40131c4",
"name": "Test User100597",
"email": "Test.User100597@example.com",
"department": "Sales",
"department_head": "Nelly Jakuboski"
}
]
If we take a look under the hood, we can see some SQL queries that are generated to retrieve the requested data. Let's check them out:
As a first step, ServiceNow collects all sys_ids. (Remember, we set the limit to 1, so only one sys_id is collected.)
SELECT sys_user0.`sys_id`
FROM sys_user sys_user0
WHERE sys_user0.`department` IS NOT NULL
LIMIT 0, 1
Then, the second SQL query gets the user record from the sys_user table based on the sys_id.
SELECT sys_user0.`country`,
sys_user0.`calendar_integration`,
sys_user0.`user_password`,
sys_user0.`sys_updated_on`,
sys_user0.`sys_created_by`,
sys_user0.`active`,
sys_user0.`name`,
sys_user0.`user_name`,
sys_user0.`title`,
... LOT OF OTHER FIELDS ...
sys_user0.`sys_class_name`,
sys_user0.`sys_id`,
sys_user0.`company`,
sys_user0.`department`,
sys_user0.`first_name`,
sys_user0.`email`,
sys_user0.`manager`,
sys_user0.`last_name`,
sys_user0.`date_format`,
sys_user0.`location`
FROM sys_user sys_user0
WHERE sys_user0.`sys_id` = '00002e6ec3779210fc037205e40131c4'
Now the user record can be used for get the necessary fields. Since we need to retrieve additional data from different tables (Department and Department head), more SQL queries are generated. The next one fetches the department information from the cmn_department table:
SELECT cmn_department0.`parent`,
cmn_department0.`code`,
cmn_department0.`sys_mod_count`,
cmn_department0.`description`,
cmn_department0.`head_count`,
cmn_department0.`sys_updated_on`,
cmn_department0.`source`,
cmn_department0.`business_unit`,
cmn_department0.`sys_id`,
cmn_department0.`dept_head`,
cmn_department0.`sys_updated_by`,
cmn_department0.`cost_center`,
cmn_department0.`sys_created_on`,
cmn_department0.`name`,
cmn_department0.`correlation_id`,
cmn_department0.`company`,
cmn_department0.`id`,
cmn_department0.`primary_contact`,
cmn_department0.`sys_created_by`
FROM cmn_department cmn_department0
WHERE cmn_department0.`sys_id` = '221db0edc611228401760aec06c9d929'
The sys_id in the WHERE clause comes from the sys_user0.department field of the sys_user (sys_user0) table.
And the last SQL query retrieves the department head, so needs to be executed on the sys_user table:
SELECT sys_user0.`country`,
sys_user0.`calendar_integration`,
sys_user0.`user_password`,
sys_user0.`sys_updated_on`,
sys_user0.`sys_created_by`,
sys_user0.`active`,
sys_user0.`name`,
sys_user0.`user_name`,
sys_user0.`title`,
... LOT OF OTHER FIELDS ...
sys_user0.`sys_class_name`,
sys_user0.`sys_id`,
sys_user0.`company`,
sys_user0.`department`,
sys_user0.`first_name`,
sys_user0.`email`,
sys_user0.`manager`,
sys_user0.`last_name`,
sys_user0.`date_format`,
sys_user0.`location`
FROM sys_user sys_user0
WHERE sys_user0.`sys_id` = '06826bf03710200044e0bfc8bcbe5d6f'
The sys_id, which is used in the WHERE clause comes from the cmn_departmen0.dept_head field from the previously executed SQL query on the Department table.
As you can see, several SQL queries are executed in order to collect some data from the database.
💡During my testing I made an interesting observation!
I noticed, that when I used the getDisplayValue function (instead of simple dotwalk), the department SQL query was executed twice. You can see the used JavaScript code below:
getUserData: function() {
const userGr = new GlideRecord("sys_user");
userGr.addEncodedQuery("departmentISNOTEMPTY");
userGr.setLimit(1);
userGr.query();
let userData = [];
while (userGr.next()) {
userData.push({
id: userGr.getUniqueValue(),
name: userGr.getValue("name"),
email: userGr.getValue("email"),
department: userGr.getDisplayValue("department"), // !!!
department_head: userGr.getDisplayValue("department.dept_head") // !!!
});
}
return userData;
},
Additional, but unnecessary SQL query was executed, which also affects the performance. Need to pay attention to this.
‼️Important!
I have to highlight, that if multiple users are retrieved, the system optimises the collection of reference data.
For example, if three users are fetched - one from HR and the other two from IT - only two SQL queries are executed to get the two departments. The system uses a kind of cache to avoid executing the same query multiple times. This is smart, but could be improved even further.
Put the addExtraField function into the game
Now, let's see what the addExtraField function can provide:
I changed the previous script a bit. I added two additional lines to the code using the addExtraField function.
getUserData: function() {
const userGr = new GlideRecord("sys_user");
userGr.addEncodedQuery("departmentISNOTEMPTY");
userGr.addExtraField("department.name");
userGr.addExtraField("department.dept_head.name");
userGr.setLimit(1);
userGr.query();
let userData = [];
while (userGr.next()) {
userData.push({
id: userGr.getUniqueValue(),
name: userGr.getValue("name"),
email: userGr.getValue("email"),
department: userGr.department.name + "",
department_head: userGr.department.dept_head.name + ""
});
}
return userData;
},
The result was the same, but let's see how the SQL query looks now.
At first - as usual - need to get the sys_is of user(s):
SELECT sys_user0.`sys_id`
FROM sys_user sys_user0
WHERE sys_user0.`department` IS NOT NULL
LIMIT 0, 1
And here comes the important part. The second query is a bit different from the previous example:
SELECT sys_user0.`country`,
sys_user0.`calendar_integration`,
sys_user0.`user_password`,
sys_user0.`sys_updated_on`,
sys_user0.`sys_created_by`,
sys_user0.`active`,
sys_user0.`name`,
sys_user0.`user_name`,
sys_user0.`title`,
... LOT OF OTHER FIELDS ...
sys_user0.`sys_class_name`,
sys_user0.`sys_id`,
sys_user0.`company`,
sys_user0.`department`,
sys_user0.`first_name`,
sys_user0.`email`,
sys_user0.`manager`,
sys_user0.`last_name`,
sys_user0.`date_format`,
sys_user0.`location`,
cmn_department1.`name` AS department_name,
cmn_department1.`sys_id` AS department_sys_id,
sys_user2.`name` AS department_dept_head_name,
sys_user2.`sys_id` AS department_dept_head_sys_id,
sys_user2.`sys_class_name` AS d8t_dept_head_sys_class_name
FROM ((sys_user sys_user0
LEFT JOIN cmn_department cmn_department1 ON sys_user0.`department` = cmn_department1.`sys_id`)
LEFT JOIN sys_user sys_user2 ON cmn_department1.`dept_head` = sys_user2.`sys_id`)
WHERE sys_user0.`sys_id` = '00002e6ec3779210fc037205e40131c4'
You can see that now the SQL statement is more optimal, because the two necessary tables are joined to the original SQL, using LEFT JOIN. The result of this SQL query contains everything which we need and there is no additional query here. In this example instead of four SQL queries just two were executed.
Fewer SQL queries executed means better performance! 🙂
Here we arrive at the point where we can consider starting to use the addExtraField function.
This is not the end of the story, I would like to show a spectacular example.
Let's see what the numbers say
The example I would like to provide demonstrates the benefit of using the function in a more tangible way.
The task is the following:
I need a report from HR profiles. The result should be a list with the HR profile numbers and the user names.
(The reason I selected this example is that each HR Profile has its own User reference, so the previously mentioned SQL optimisation doesn't work in this case.)
I use two different solutions: the first one does not contain the addExtraField function, but the second one does.
userQueryTest1: function() {
let hrProfiles = [];
let hrProfileGr = new GlideRecord("sn_hr_core_profile");
hrProfileGr.setLimit(1);
hrProfileGr.query();
while(hrProfileGr.next()) {
hrProfiles.push({
"number": hrProfileGr.getValue("number"),
"user_name": hrProfileGr.user.name + ""
});
}
return hrProfiles;
}
userQueryTest2: function() {
let hrProfiles = [];
let hrProfileGr = new GlideRecord("sn_hr_core_profile");
hrProfileGr.addExtraField("user.name");
hrProfileGr.setLimit(1);
hrProfileGr.query();
while(hrProfileGr.next()) {
hrProfiles.push({
"number": hrProfileGr.getValue("number"),
"user_name": hrProfileGr.user.name + ""
});
}
return hrProfiles;
}
The only difference between the two solutions is the following line:
hrProfileGr.addExtraField("user.name");
I prepared my PDI and created 500,000 users and HR profiles. Then, I executed some test cases and measured the processing time. (the time it takes until I receive the data back in JSON format.)
I executed each test case multiple times and calculated an average.
I cleared the cache before each test execution, to make the circumstances as similar to each other as possible.
The table below shows the results of different test cases:
The difference is considerable! If you don't believe it, just test it yourself. 🙂
It's all nice and good, but what's the fine print?
I used this function multiple times and I faced with the following issues:
- The function cannot be used in Scoped App
- The function doesn't work if you use GlideRecord.get()
There are solutions (or workarounds) for both issues:
For the first one I created a wrapper class into the Global scope.
addExtraField: function(currentGr, field) {
currentGr.addExtraField(field);
return currentGr;
}
The solution for the second problem is easier, just need to use the query instead of get function.
Closing words
I believe, that the presented feature, supported by examples and measurement results, will help us consider its value. It may not always be necessary, it may not always be the best solution, and it is certainly not guaranteed that such a performance improvement can always be achieved which I presented in the article. It is important that when implementing a solution, we do it smart and the addExtraField function could be one element of that.
