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

[Deepak Shaerma]

Hi @Thiago Pereira 

As per my knowledge and implementing best practices on daily basis,  i suggest 

- Performance: GlideAggregate is optimized for counting and therefore scales much better with the size of your dataset.

- Simplicity: While getRowCount() might be simpler to implement, it is not as efficient for large datasets.

- Use Case: For small datasets or non-performance-critical parts of your application, getRowCount() might be acceptable. For larger datasets or performance-sensitive contexts, prefer GlideAggregate.

please mark this Helpful and Accepted Solution if this helps you. Your action will help me and the community in understanding same requirements.

Thanks & Regards

Deepak Sharma

[Thiago Pereira]

Hi Deek Sharma!

These points are the ones that are normally used when we just talk about searching for the total number of records.

My case is a little more complex:

Imagine that I need to query a very large table (I'm using sys_audit as an example). In this table I will perform a query that will return approximately 500 records. To display the data to the user I will use pagination with chooseWindow to show it in blocks of 50:

var grActivities = new GlideRecord('sys_audit');
grActivities.addQuery('tablename', 'x_custom_table');
grActivities.addQuery('documentkey', 'kwjrt2jgh5tf23tg3çi1hkrj');
grActivities.orderByDesc('sys_created_on');
grActivities.chooseWindow(0,50);
grActivities.query();

I need to know the total number of records I have to be able to calculate the number of pages.

As I already have the query, should I use getRowCount or is it better to do a GlideAggregate just for that?

[saurabh_dubey]

Okay, so lets try to understand this code,

var now_GR = new GlideRecord('incident');
now_GR.orderBy('number');
now_GR.chooseWindow(0, 4);
now_GR.query();
while (now_GR.next()) {
  gs.info(now_GR.getValue('number') + ' is within window');
}

• In a window of (0,4) that is your first window, for the first 50 set the the glide record will execute, so actually what this code says that, hey look I will query my DB, but instead for 500 records initially I will query this only for the initial 4 sets, i.e 50 sets i.e your case. 
• Then after next set i.e 51-100 you might need to update the  "now_GR.chooseWindow(0, 4)" to "now_GR.chooseWindow(51, 100)" so now again you will execute the DB and call the next set of the values.
• As per my experience this is way better than initially querying the huge set of 500 or may be 5000 records that will clearly create some slowness initially, then order the data to show them in 50 chunks of data is clearly a bad idea,
• Here you are hitting the DB with the smaller chunks of values that will clearly improve the user experience and less head of waiting for initial set of data, even for this paginated approach is recommended in importing or integrating a system where you clearly have a data in lakhs or 5 of thousands, this will surely create or boost some performance.
• So here's my take the approach yours is surely a feasible option to go with.

If you found my answer/explanation helpful do hit the helpful and if it serves the purpose do mark my answer correct.

Thanks and regards,

Saurabh Dubey.

[Thiago Pereira]

Hi Saurabh Dubey.

My question is not about how chooseWindow works.

As I said previously, I need to calculate the number of pages to show it to users on the portal so that they can navigate between the pages. To do this calculation I need to get the total number of records: 500 records with a "window" of 50 will return 10 pages. At the bottom of the list I need to display: 1, 2, 3, 4,... 9, 10 so they can navigate.

After making the query I have 2 possibilities to get the total:

1) getRowCount: It will return the total number of records in the query ignoring the chooseWindow

2) Make a new query using GlideAgrgegate.

What is the best practice?