Optimizing Pagination in GlideRecord with chooseWindow and setNoCount

[Mwatkins]

In this article we give some examples of how to improve performance with chooseWindow, setLimit methods. 

Whenever ServiceNow GlideRecord is used, it does at least two queries.

1. A query to get the sys_id's of records in the window.
2. A query to get all columns of the next 100 records in the window.

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

Sometimes you want to 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(); ← this triggers another query to count all active incidents
while (incidentGr.next()) {
...do something...
}

Another thing that is key for performance is returning only the data that your user's need. The most common way to do this is by constructing pagination techniques so that only the next X number of records are returned. 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.

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 specify a certain order for the results or include the addQuery("field", ">", Y) condition. However, there is one problem. Even if you pass the forceCount parameter as "false", chooseWindow almost always makes GlideRecord count all the matching results for your conditions (not just the ones you are trying to return). This makes chooseWindow perform poorly on large tables in some cases.

If we use chooseWindow with our previous example it makes some interesting changes to the query behavior. 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.query(); ← this now triggers two queries.
incidentGr.getRowCount();
while (incidentGr.next()) { ← this 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) or setLimit(int).

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 either of the following:

With setNoCount

var incidentGr = new GlideRecord("incident");
incidentGr.chooseWindow(1000,1200);
incidentGr.setNoCount();
incidentGr.query();
var count = incidentGr.getRowCount(); //count will be 200

With setLimit

var incidentGr = new GlideRecord("incident");
incidentGr.chooseWindow(1000,1200);
incidentGr.setLimit(200);
incidentGr.query();
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  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

Without setNoCount or setLimit

var incidentGr = new GlideRecord("incident");
incidentGr.addActiveQuery();
incidentGr.chooseWindow(1000,1200);
incidentGr.query();
var count = incidentGr.getRowCount(); //count will be all active incidents that match the filter

Above runs super slow cause it has to do this query too:

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:

Notice in the examples above that using setLimit(int) or 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.

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 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

This will improve performance since it will avoid the query to count all the matching rows, but it will break the expected behavior of the "Data Table" widget because data.row_count will only show the data.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