We've all heard that GlideAggregate should be used instead of GlideRecord.getRowCount() if we just want a count of records, GlideAggregate is much faster.
Take a look at the following script
var encoded_query = 'sys_class_name=sys_db_object';
var files = new GlideRecord('sys_metadata');
files.addEncodedQuery(encoded_query);
files.query();
var developers = [];
while(files.next()) {
var developer = files.getValue('sys_updated_by');
if(developers.indexOf(developer)>-1) continue;
developers.push(developer);
}
This pattern is something that we ServiceNow developers do a lot. Query a table with some condition, loop over the results set and create a list of some field value. The list will often get piped into another query, or used in some other way, never the less, looping over every record has been the norm, until now.
Spoiler
Yes I could query the sys_db_object table directly, but I wanted to test what's coming up against the largest table I could so sys_metadata it is.
Did you know about the Aggregate GROUP_CONCAT_DISTINCT ? This little nugget has been around since Tokyo, but hasn't been getting the praise it deserves. By using this aggregate, GlideAggregate can now return field values, not only group values.
I've re-written the previous script using GROUP_CONCAT_DISTINCT, check it out
var encoded_query = 'sys_class_name=sys_db_object';
var files = new GlideAggregate('sys_metadata');
files.addEncodedQuery(encoded_query);
files.addAggregate('GROUP_CONCAT_DISTINCT', 'sys_updated_by');
files.groupBy('sys_class_name');
files.query();
var developers = [];
if (files.next())
developers = files.getAggregate('GROUP_CONCAT_DISTINCT', 'sys_updated_by').split(',');
Both these scripts return the same result, so why should we choose one over the other?
GlideAggregate is fast! When I tested these two scripts as a background script, the GlideAggregate script wins in every category. It executed less than half of the SQL commands. SQL execution time was 3x faster and processing time was 70ms compared to 200ms (local instance of ServiceNow, average of 5 runs with cache flush in between) YMMV of course depending on your own instance configuration and hosting options.
So what's the catch? The only catch I've experienced is in implementation. GROUP_CONCAT_DISTINCT only works when a GlideAggregate has a groupBy() set (it's in the name really). Notice how I've grouped by the same field I've set an equals condition on? This is a trick to return just a single group, splitting the results set would return multiple concat strings and we would be stuck with trying to de-duplicate multiple concat strings. Not horrible, and still faster than GlideRecord, but still not one pass. The solution to this catch is to group by something that you are sure will only return a single group. sys_class_name is a good field for this.
So I'm curious to know, copy the scripts above, run them in your dev environment, and comment below with your speed results. Will you be using this to speed up queries in your next script?
2 Comments
