NOTE: MY POSTINGS REFLECT MY OWN VIEWS AND DO NOT NECESSARILY REPRESENT THE VIEWS OF MY EMPLOYER, ACCENTURE.
DIFFICULTY LEVEL: INTERMEDIATE
Assumes good intermediate level knowledge and/or familiarity of Scripting in ServiceNow.
Something I see quite often, in the ServiceNow codebase, is a GlideRecord call followed by the requisite While-Loop with another GlideRecord call in the loop.
This can be a big performance hit!
Not only does it have the possibility of taking a long time to complete the code (regular Business Rules would be the worse as they could impact the user experience), but it could possibly bring the instance, it is running on, to a crawl. This is due to the number of round-trip hits to the database. The more times the code loops then the worse the situation becomes. Each database hit takes the computer time to set it up (connection to the database, submission of the query, execution of the query, collection of the results, packaging of the results, return of the results). Remember that with the opening of a regular GlideRecord that the connection with the database remains open for the entirety of execution (even after you do an update, insert, or delete).
If you encounter the need for a structure like this then my advice is to look at it carefully and determine if it is possible to refactor the code to limit the number of hits on the database. BTW, there are use cases that DO require a GlideRecord inside of a GlideRecord, but they should be very rare.
// Example of GlideRecord Inside a GlideRecord. THIS IS A BAD PRACTICE!
var problemRecords = new GlideRecord('problem');
problemRecords.addQuery('state', 1); // open
problemRecords.setLimit(10); // we don't want them all
problemRecords.orderBy('problem_id'); // helps with dedup speed
problemRecords.query();
// The more loops the worse the performance gets!
while(problemRecords.next()) {
var incidentRecords = new GlideRecord('incident');
incidentRecords.addQuery('problem_id', problemRecords.sys_id + '');
incidentRecords.query();
if (incidentRecords.hasNext()) {
gs.info('Number of incident records for problem {0} is: {1}.',
[problemRecords.number, incidentRecords.getRowCount()]);
}
}
Results:
[0:00:00.497] Script completed in scope global: script
*** Script: Number of incident records for problem PRB0000003 is: 3.
*** Script: Number of incident records for problem PRB0000005 is: 1.
*** Script: Number of incident records for problem PRB0000007 is: 2.
*** Script: Number of incident records for problem PRB0000008 is: 1.
*** Script: Number of incident records for problem PRB0000011 is: 5.
*** Script: Number of incident records for problem PRB0040008 is: 2.
Note the time. It is important as the refactor of this code is telling in performance.
After some consideration the following would be a way to reduce this to just two calls to the database:
var problemRecords = new GlideRecord('problem');
problemRecords.addQuery('state', 1); // open
problemRecords.setLimit(10); // we don't want them all
problemRecords.orderBy('problem_id'); // helps with dedup speed
problemRecords.query();
// push the problem sys_id's to a one-dimensional array. THIS IS A BEST PRACTICE!
var problemList = [];
while(problemRecords.next()) {
problemList.push(problemRecords.sys_id + '');
}
// get rid of duplicates
problemList = new ArrayUtil().unique(problemList);
gs.info('Number of problem records found: {0}', [problemList.length]);
// here I am using a GlideAggregate as a way to do faster counts, but the key here is
// the "IN" statement with the joined array.
var countIncidents = new GlideAggregate('incident');
countIncidents.addAggregate('COUNT');
countIncidents.addQuery('problem_id', 'IN', problemList.join(','));
countIncidents.groupBy('problem_id');
countIncidents.query();
while(countIncidents.next()) {
gs.info('Number of incident records for problem {0} is: {1}.',
[countIncidents.problem_id.getDisplayValue(), countIncidents.getAggregate('COUNT')]);
}
The results:
[0:00:00.233] Script completed in scope global: script
*** Script: Number of problem records found: 10.0
*** Script: Number of incident records for problem PRB0000003 is: 3.
*** Script: Number of incident records for problem PRB0000005 is: 1.
*** Script: Number of incident records for problem PRB0000007 is: 2.
*** Script: Number of incident records for problem PRB0000008 is: 1.
*** Script: Number of incident records for problem PRB0000011 is: 5.
*** Script: Number of incident records for problem PRB0040008 is: 2.
Note the time! Less than half. We went from 10+1 round-trips to the database to 2 round-trips. Of course that is going to reduce the processing time. This was only 10 records. Imagine what it would be with 10k or even 100k records? Impressive is what.
Enjoy!
Steven Bell.
If you find this article helps you, don't forget to log in and mark it as "Helpful"!
Originally published on: 08-11-2015 08:20 PM
I updated the code and brought the article into alignment with my new formatting standard.
4 Comments
