Prevent data retrieval from growing linear for each result

General Add comments
by:

There are many ServiceNow customers and they all have their own specific demands. Some of them can be fulfilled by customizations using UI actions, but some of them require a fully customized page.
For this ServiceNow has provided a few methods, one of which is a UI page. As the demands often tend to be quite specific, GlideAjax and Script Includes can be used to retrieve and store data following those specific demands.

Data retrieval

The performance tip we talk about in this blog lies in retrieving data. Often you want to retrieve records which also have one or multiple relations. Let’s say you need the following data for your UI page:

  • A list of active incidents created in the last year.
  • For each incident the ‘number’, ‘short description’, ‘priority’, ‘caller first name’, ‘caller last name’ and ‘caller department name’.
  • For each found department the ‘Department head name’.

The code to achieve this could look as follows:

Can we optimize this?
As you can see we use the ‘dot-walk’ method is used to reach the related records and get the information. But what actually happens? We loop through the incident records one by one and for each of them we need information in a related table. This means that when we ‘dot-walk’ the server must make a call to that related table to get the related information. In this example we have 2 related tables from which we need data, one is sys_user that is required twice. Each time when we run the .next() multiple queries are run to retrieve the data from the three tables. The time it takes to request this information will grow linearly for each found incident. So can we optimize this? Yes we can!

How do we optimize?
We have to take the linear growth out of the equation. We can do this by following a simple principle:
Request all data from each individual table in one request.

Below is a code example:

As you can see we are now able to use the ServiceNow prefered .getValue method safely because we do not ‘dot-walk’ to related records. De results object will have the record sys_id as it’s property and the record as its value. In the code example above we still do not have our related fields. For this we need to make this call again only it needs to be to the sys_user table, it requires a different query and requires different fields to be returned. Please note that since we use an object to store our results and the sys_id is the object’s property, ordering on any field (e.g. number) will need to be performed again client-side.

Helper script
To prevent ourselves from repeating a lot of code a helper script is created to do this for us. Click here to open the helper script ‘LSMC’. This script is to be added to your environment as a “Script Include”. This script only asks you for the unique values you require. Now we can write the same request a lot easier and keep the code a lot clearer as well. Feel free to read the code before uploading it to see how it works.

Now we just need to ask the values we require from the sys_user table using the caller_id.

We continue this approach to request the remaining data as well resulting in the following code:

Now we have all the unique data we need and we only made four separate requests to the database. This number will not change based on the amount of incident records we request. So this growth as opposed to our first example is not linear, which will result in a faster data retrieval as the amount records to retrieve gets bigger.

Am I done now?
No your not done! In the first example all relevant data for each record was stored within that record and it was ordered by number. Now everything is related to each other using sys_id’s, but not all data is in the same record. So when this data is send client-side it needs to be linked and possibly ordered by the client. Is this a problem? No, you actually place some of the calculation power the server normally would perform to the client-side, thereby relieving the server from performing calculations the client can also perform! This always tends to be a good thing especially if your application gets bigger.

.img[at].img

Leave a Reply