A workflow should not be allowed to start until there is at least one supporting task in a related list.
A Major Incident should not be allowed to close if there is not at least on VIP on the watchlist.
A notification should fire if there is not at least one active user in a group.
We often find ourselves given requirements that mean we need to find out if there is at least one record that matches a condition. But there are several ways to accomplish this.
You may have already heard that gs.getRowCount() is inferior due to the fact that it iterates the entire record set in order to count the elements. But writing the code for the Aggregate is just more verbose, and therefore more effort.
Consider
function getRowCount(){
var gr = new GlideRecord("sys_user");
gr.addQuery("active", "true");
gr.query();
if(gr.getRowCount() > 0){
// Do something awesome
}
}
Versus
function getAggregateCount(){
var gr = new GlideAggregate('sys_user');
gr.addAggregate('COUNT');
gr.addQuery('active', 'true');
gr.query();
if (gr.next() && gr.getAggregate('COUNT') > 0) {
// Do something awesome faster
}
}
How much faster is the aggregate method?
howLong(getRowCount);
howLong(getAggregateCount);
function howLong(func){
var cal1 = Packages.java.util.Calendar.getInstance();
var startMillis = cal1.getTimeInMillis();
func.call();
var cal2 = Packages.java.util.Calendar.getInstance();
var endMillis = cal2.getTimeInMillis();
gs.print('Millis for Query:'+(endMillis - startMillis ));
}
The GlideAggregate gets the job done in almost 1/4 of the time. Wow. That's compelling. We really ought to use the GlideAggregate COUNT over getRowCount().
Just watch out for one thing: You have to call next() in order for getAggregate('COUNT') to work!
However, this got me thinking. By writing these conditions...
if ( gs.getRowCount() > 0 ) { }
//OR
if (gr.next() && gr.getAggregate('COUNT') > 0) { }
We're really on trying to figure out if there is at least one row — right?
So, what about counting the rows after limiting the result set to a single row.
function getOneRow(){
var gr = new GlideRecord("sys_user");
gr.addQuery("active", "true");
gr.setLimit(1);
gr.query();
if(gr.getRowCount() > 0){
// Do something awesome at blinding speed
}
}
//Drum roll please
howLong(getOneRow);
So that's 1/11th of the time that it took without the setLimit(1) statement. But, surprisingly, it beat the getAggregate('COUNT') method by a factor of 3.
Another factor to consider is that for this example performance has been measured using the sys_user table, which has relatively few entries. Execution time for getRowCount() is going to grow proportionally with the number of results; so performance using getRowCount() with a table with a large number of records is going to be much worse.
So the next time I need you need know if there is at least one record that matches a particular condition, try using getRowCount() with a setLimit(1) statement.
ctomasi Thanks for challenging me on that sloppy answer.
Now my question to any MSSQL database gurus out there: What are the database impacts of each of these approaches? I wouldn't want to count something as a performance win if it was just pushing the work into the database where it was still a performance bottleneck for the instance!
13 Comments
