Pagination: GlideRecord + getRowCount OR GlideRecord + GlideAggregate

[Thiago Pereira]

Hi!

I'm writing a script for record pagination and I have a question about good practices when getting the total number of records to know the number of existing pages.

The script is based on the @Chuck Tomasi  video.

In the code he uses getRowCount to get the total number of records even though it used chooseWindow. At this point I understand that getRowCount "ignores" chooseWindow and returns all records from the query.

My point is about best practices to avoid using getRowCount. Would it be better to do a GlideAggregate to get the total number of records or in this case, as I already have GLideRecord, is there no problem using getRowCount?

best,
Thiago

[Bert_c1]

Here's a related KB: KB0868974

On use of 'chooseWindow()'. 

[Mwatkins]

Hi @Thiago Pereira ,

I know this is an old post, so you probably don't still have this question, but I'll answer anyway for the sake of posterity.

To directly answer your question, no, there isn't a performance best practice problem with running getRowCount in this scenario. GlideAggregate is more performant than getRowCount() when you only want to get the total count of records that match a query, but in this case, we want to get the total count of records and we want to get the records themselves to display them in the list. If we didn't want all the records and we just wanted the count, then, yes, you are right, GlideAggregate would be the way to go.

Another thing I want to mention is that that there is a potentially major performance problem with the solution in the video. Using chooseWindow triggers a very expensive, and unnecessary, database query. To avoid the extra query, you can use setNoCount() in the GlideRecord before calling GlideRecord.query().

With just chooseWindow

var incidentGr = new GlideRecord("incident");
incidentGr.chooseWindow(1000,1200);
incidentGr.query();

On an instance with 1 million incident records, the 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 )))) /* empmwatkins2020, gs:AD2C480787A9EA504F11DDBABBBB351B, tx:d5cc40c7876dea504f11ddbabbbb35bd, hash:-837640149 */

With setNoCount

var incidentGr = new GlideRecord("incident");
incidentGr.chooseWindow(1000,1200);
incidentGr.setNoCount();
incidentGr.query();

Above runs super fast and just runs this query:

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

References:

1.) Performance Improvement - Remove Pagination Count: https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0817996

2.) Optimizing Pagination in GlideRecord with chooseWindow and setNoCount: https://www.servicenow.com/community/developer-blog/optimizing-pagination-in-gliderecord-with-choose...

3.) Best practices about ServiceNow coding performance: https://www.servicenow.com/community/developer-articles/performance-best-practices-for-server-side-c...