Optimizing Pagination in GlideRecord with chooseWindow and setNoCount

[Mwatkins]

In this article we give some examples of how to improve performance with chooseWindow, setNoCount, and setLimit methods. This started out as a pretty small article, but a big thanks goes out to  @Daniel Oderbolz ,  @OliverDoesereck , and @Niamul Arifin for their feedback and guidance to help improve it.

Warning:

Using chooseWindow gives you the advantage of simplicity, however, there are a couple potential performance problems with using chooseWindow for pagination that will be discussed later in this article.

Usually when ServiceNow GlideRecord is used, folks just want to loop over the first X number of results. To do this GlideRecord does at least two queries:

1. A query to get all the sys_id's of records matching your filter.
2. A query to get all the columns of the next 100 records, using a filter with the first 100 sys_id's from query #1. This query will repeat every 100 times that .next() gets called.

var incidentGr = new GlideRecord("incident");
incidentGr.addActiveQuery();
incidentGr.query(); // triggers the first query
while (incidentGr.next()) { // triggers the second query
...do something...
}

However, sometimes you want to loop over the first X results and you also want know how many records in total match your query. You can accomplish this by using GlideRecord.getRowCount(). That triggers a third query. 

var incidentGr = new GlideRecord("incident");
incidentGr.addActiveQuery();
incidentGr.query(); 
incidentGr.getRowCount(); // triggers another query to count all active incidents
while (incidentGr.next()) {
...do something...
}

NOTE: By the way, if you just want to know how many records in total match your query and you don't want to loop over the results, use GlideAggregate instead of GlideRecord.  That way you won't trigger the query that returns all the matching sys_id's.

To Paginate or Not to Paginate?

A key principle in application UI performance is displaying only the data that your user's need. The most common way to do this is by constructing pagination techniques so that only X number of records are displayed/processed at a time. If you are just displaying a list of items in the UI, then pagination is a good choice. Pagination is also very helpful for simple integrations where chunks of data are being moved in/out of a system.

On the other hand, you may have a design requirement that involves processing on a large set of records all at once. This scenario might benefit from letting the database return the entire result set in one shot instead of paginating over smaller chunks. Each call to the database carries its own overhead, not to mention that pagination requires more complex code. If you choose to forgo pagination, you need to consider how much processing will be done on the results. If your processing is very light and you can process all the results in a few seconds, then a simple synchronous GlideRecord loop with all the processing inside it, might be fine. However, if processing the results takes an extensive amount of time or puts a strain on system resources (e.g., creating large objects memory), then a strategy to quickly iterate the result set and offload any heavy processing to an asynchronous architecture is needed. You might look at some type of event based processing model.

Using SetLimit for Pagination

If you've been working with ServiceNow coding for while, you are probably familiar with the GlideRecord.setLimit method. It can be used to only return a certain number of records from a GlideRecord operation and is very handy for pagination. As with most other scenarios of using GlideRecord, setLimit() will not trigger a count of all matching records. To paginate with setLimit becomes a bit tricky because in order to get the next X number of records you must use an addQuery("field", ">", Y) condition and then order your results by Y.

var incidentGr = new GlideRecord("incident");
incidentGr.addQuery("number", ">", lastRecordValue);
incidentGr.setLimit(100);
incidentGr.orderBy("number");
incidentGr.query();
var count = incidentGr.getRowCount(); //count will be 100

Using chooseWindow for Pagination

There is a lesser known method of GlideRecord called "chooseWindow" that makes GlideRecord do a windowed query, which is great for pagination since it allows you to grab the next window of records. The method signature is GlideRecord.chooseWindow(firstRow, lastRow, forceCount). This is simpler than the setLimit way of pagination since you don't need to include the addQuery("field", ">", Y) condition. In the database, the query will look something like this, using an offset limit clause:

SELECT ... FROM my_table WHERE my_field = 'x' LIMIT 2100, 100 ORDER BY my_other_field

Warning:

1. Using the limit clause with a larger offset causes queries to run more slowly. The larger the offset, the slower the query. There is no way around this (see discussion in StackOverflow.com). You should only use chooseWindow with relatively small offsets.
2. The chooseWindow method triggers a query to count all matching records, like the one triggered by getRowCount. This makes chooseWindow perform slowly when your query matches a large number of records.

While there is nothing we can do about the first problem, we can mitigate the second problem.

Note: Even if you pass the forceCount parameter as "false", chooseWindow almost always makes GlideRecord count all the matching results for your conditions.

If we use chooseWindow with our previous example, it makes some interesting changes to the query behavior. Notice the lines of code that will trigger the queries now. Now when the query() method is executed there are two queries that occur:

1. A query to count the total number of records that match the filter (e.g., all active incidents) 
2. A query to get all the sys_id's of records in the window

var incidentGr = new GlideRecord("incident");
incidentGr.addActiveQuery();
incidentGr.chooseWindow(offset, windowSize);
incidentGr.orderBy("number");
incidentGr.query(); // now triggers two queries - one to count all matches and one for all the sys_id's
incidentGr.getRowCount(); // returns count of all active incident records, but does not trigger a query
while (incidentGr.next()) { // works as before, getting the next 100 results
...do something...
}

Query #1 above, to count all records that match the filter, will occur regardless of if you need it and regardless of if you run GlideRecord.getRowCount().

The query to count all the rows that match the filter is often slow if you are querying a very large table. While our new Hybrid Transactional/Analytical Processing (HTAP) database RaptorDB® vastly improves aggregate queries like count operations, it is still a non-trivial operation. Sometimes you need to run this query and sometimes you don't. With large tables it is really best to try to avoid this query whenever possible or make it run asynchronously or on-demand so that it doesn't slow down your user experience.

Good news! If you want to avoid the count query, you can avoid it by using setNoCount(bool). But pay attention, it changes the behavior of getRowCount.

So, for example, if I want to pull back just 200 incidents in my 1 million incident table starting at row 1,000 without triggering a count(*) I can do the following:

With setNoCount

var incidentGr = new GlideRecord("incident");
incidentGr.chooseWindow(1000,1200);
incidentGr.setNoCount();
incidentGr.orderBy("number");
incidentGr.query(); // runs only the query for matching rows
var count = incidentGr.getRowCount(); // count will be 200

Above runs super fast and just runs this query:

12:01:01.372 Time: 0:00:00.050 for: myinstance_2[glide.10] [-129790715] SELECT ...column list...  FROM task task0 WHERE task0.`sys_class_name` = 'incident' AND (task0.`active` = 1 OR (task0.`opened_by` = '6a826bf03710200044e0bfc8bcbe5dec' OR ((task0.`priority` = 5 OR task0.`priority` IS NULL )))) limit 1000,200 ORDER BY `number`

Without setNoCount

var incidentGr = new GlideRecord("incident");
incidentGr.addActiveQuery();
incidentGr.chooseWindow(1000,1200);
incidentGr.orderBy("number");
incidentGr.query(); // triggers a count query and query for all matching rows
var count = incidentGr.getRowCount(); // count will be all active incidents that match the filter

Above runs super slow cause it does this query:

Time: 0:00:10.192 id: myinstance_2[glide.5] primary_hash=-837640149 (connpid=263302) for: SELECT count(*) AS recordcount FROM task task0  WHERE task0.`sys_class_name` = 'incident' AND (task0.`active` = 1 OR (task0.`opened_by` = '6a826bf03710200044e0bfc8bcbe5dec' OR ((task0.`priority` = 5 OR task0.`priority` IS NULL ))))

NOTE: In my instance I have a before/query Business Rule that adds on some additional filters. See https://www.servicenow.com/community/developer-articles/performance-best-practice-for-before-query-b...

Warning:

Using setNoCount(bool) changes the behavior of the getRowCount method. Instead of returning the count of all records that match the filter, getRowCount will just return the count of the window size.

Warning:

Do not use setLimit and chooseWindow together. If you use setLimit after chooseWindow, then setLimit overrides chooseWindow. You will avoid the query to count all the matching rows, but the offset and limit of your chooseWindow will be overridden. Offset will become 0. And the limit will be whatever is specified in setLimit. Conversely, if you use setLimit before chooseWindow, then chooseWindow overrides setLimit and whatever limit you used in setLimit will be lost. Note, however, that the count query will be gone in this scenario, similar to using setNoCount. So, just use setNoCount because it is less confusing.

Example from Data Table Widget

Let's consider how this might impact the out-of-box Service Portal Widget "Data Table". This widget uses the chooseWindow method for pagination. Here's the out-of-box code:

901   data.window_start = data.page_index * data.window_size;
902   data.window_end = (data.page_index + 1) * data.window_size;
903   gr.chooseWindow(data.window_start, data.window_end);
904   gr.setCategory("service_portal_list");
905   gr._query();
906 
907   data.row_count = gr.getRowCount();

The code above is going to result in two queries when it runs line 905:

1. Query to get all sys_id's in the window
2. Query to get the count of all records that match the filter

It could be optimized for speed by adding setNoCount() like this:

901 	data.window_start = data.page_index * data.window_size;
902 	data.window_end = (data.page_index + 1) * data.window_size;
903 	gr.chooseWindow(data.window_start, data.window_end);
->>	 	gr.setNoCount();
904 	gr.setCategory("service_portal_list");
905 	gr._query();
906 
907 	data.row_count = gr.getRowCount(); // but this is now going to return only data.window_size

While this will improve performance, since it will avoid the query to count all the matching rows, it will break the expected behavior of the "Data Table" widget because data.row_count will only show the window size and not the entire count of all records that match the filter.

Instead of seeing:

Showing 1 to 20 of 13453

You would see:

Showing 1 to 20 of 20

Assuming you are okay with loosing this part of the pagination functionality, you might want to also change the pagination UI to not include the "of 20" part when it hits the max window size.

To see some best practices about ServiceNow coding performance, check out: https://www.servicenow.com/community/developer-articles/performance-best-practices-for-server-side-c...

References:

KB0817996 - Performance Improvement - Remove Pagination Count 

service-portal-pagination-widget-using-choosewindow-method [community] 

simple-list-widget-with-pagination [community] 

issue-in-pagination-in-service-portal-widget [community] 

streamlining-data-retrieval-in-servicenow-the-power-of [community] 

pagination-gliderecord-getrowcount-or-gliderecord-glideaggregate [community] 

KB0868974 - Differences chooseWindow and setLimit in the GlideAggregate Implementation