NOTE: MY POSTINGS REFLECT MY OWN VIEWS AND DO NOT NECESSARILY REPRESENT THE VIEWS OF MY EMPLOYER, ACCENTURE.
DIFFICULTY LEVEL: BEGINNER
Assumes basic knowledge and/or familiarity of Scripting in ServiceNow.
____________________________________________________________________________
Continuing on from my previous article: GlideQuery vs. GlideRecord: A Comparison - Part 1
Picking up where I left off I am continuing to pull GlideRecord examples from my article: Community Code Snippets: A GlideRecord Survey - Part 1 (Beginner)
So, lets get started!
Case 5: BETWEEN Operator
See my article: Community Code Snippets: A GlideRecord Survey - Part 2
SQL:
SELECT *
FROM cmdb_ci_server
WHERE cpu_count > 2
AND cpu_count < 5
// 0.033 - 5 records returned
var servers = new GlideRecord('cmdb_ci_server');
servers.addQuery('cpu_count', '>=', 2);
servers.addQuery('cpu_count', '<=', 5);
servers.query();
while (servers.next()) {
gs.info('---> Server Name: ' + servers.getValue('name'));
}SQL
SELECT name
FROM cmdb_ci_server
WHERE cpu_count BETWEEN 2 AND 5
// 0.011 - Very fast!
var servers = new GlideRecord('cmdb_ci_server');
// not super intuitive
servers.addQuery('cpu_count', 'BETWEEN', '2@5');
servers.query();
while (servers.next()) {
gs.info('---> Server name: {0}, count: {1}',
[servers.getValue('name'), servers.getValue('cpu_count')]);
}SQL
SELECT name
FROM cmdb_ci_server
WHERE cpu_count > 2
AND cpu_count < 5
// 0.040
new GlideQuery('cmdb_ci_server')
.where('cpu_count','>=',2)
.where('cpu_count','<=',4)
.select('name')
.forEach(function (servers) {
gs.info('---> Server Name: {0}', [servers.name]);
});SQL
SELECT name
FROM cmdb_ci_server
WHERE cpu_count BETWEEN 2 AND 5
// Alternative 0.057
new GlideQuery('cmdb_ci_server')
.where('cpu_count','BETWEEN',[2,4])
.select('name')
.forEach(function (servers) {
gs.info('---> Server Name: {0}', [servers.name]);
});Case 5 conclusion:
GlideQuery is much slower than GlideRecord (which is 2-times faster) without any real trade-off. Since there is no BETWEEN functionality in GlideRecord I do like this a bit better in the way of code layout. I created a prototype function for a date BETWEEN awhile back. I now feel challenged to make this generic and allow any field values. See: Mini-Lab: GlideRecord Between Query Extension
Case 6: OR Command
OR's can take a number of forms with both GlideRecord and GlideQuery. I am only going to take on a couple of examples here.
SQL
SELECT *
FROM cmdb_ci_computer
WHERE form_factor = 'desktop'
OR form_factor = 'laptop'
ORDER BY name
LIMIT 10
// 0.040
// From the list view add the form_factor field.
// set the form_factor on a couple of records. A few for desktop, a few for laptop
var computers = new GlideRecord('cmdb_ci_computer');
computers.addQuery('form_factor', 'desktop')
.addOrCondition('form_factor', 'laptop');
computers.setLimit(10);
computers.query();
while (computers.next()) {
gs.info('---> name: {0}, serial_number: {1}, form_factor: {2}',
[computers.getValue('name'),
computers.getValue('serial_number'),
computers.getValue('form_factor')]
);
}SQL
SELECT *
FROM cmdb_ci_computer
WHERE form_factor IN ('desktop','laptop')
ORDER BY name
LIMIT 10
// 0.039
// I always like using the IN operator for two or more values. It seriously uncomplexes the query.
var formFactorList = ['desktop','laptop'];
var computers = new GlideRecord('cmdb_ci_computer');
computers.addQuery('form_factor', 'IN', formFactorList)
computers.setLimit(10);
computers.query();
while (computers.next()) {
gs.info('---> name: {0}, serial_number: {1}, form_factor: {2}',
[computers.getValue('name'),
computers.getValue('serial_number'),
computers.getValue('form_factor')]
);
}SQL
SELECT name, serial_number, form_factor
FROM cmdb_ci_computer
WHERE form_factor = 'desktop'
OR form_factor = 'laptop'
ORDER BY name
LIMIT 10
// 0.049
new GlideQuery('cmdb_ci_computer')
.where('form_factor','Desktop')
.orWhere('form_factor','Laptop')
.select('name', 'serial_number', 'form_factor')
.forEach(function (computers) {
gs.info('---> name: {0}, serial_number: {1}, form_factor: {2}',
[computers.name,
computers.serial_number,
computers.form_factor]
);
});SQL
SELECT name, serial_number, form_factor
FROM cmdb_ci_computer
WHERE form_factor IN ('desktop','laptop')
ORDER BY name
LIMIT 10
// Alternative 0.046
var formFactorList = ['desktop','laptop'];
new GlideQuery('cmdb_ci_computer')
.where('form_factor','IN',formFactorList)
.select('name', 'serial_number', 'form_factor')
.forEach(function (computers) {
gs.info('---> name: {0}, serial_number: {1}, form_factor: {2}',
[computers.name,
computers.serial_number,
computers.form_factor]
);
});Case 6 conclusion:
I am starting to notice something interesting: If the GlideRecord is more complex, then a comperable GlideQuery really isn't that much slower (well unless you get into the 10k+ records arena). Also, with this type of query it really isn't that much different in maintenance complexity.
Observation: For ease of maintenance don't mix GlideQuery and GlideRecord in scripts unless one is has an advantage over the other. I wouldn't call this a best practice yet, but close.
Case 7: GroupBy
SQL
SELECT *
FROM cmdb_ci_computer
WHERE form_factor IN ('desktop','laptop')
GROUP BY form_factor
LIMIT 10
// 0.047
var formFactorList = ['desktop','laptop'];
var computers = new GlideRecord('cmdb_ci_computer');
computers.addQuery('form_factor', 'IN', formFactorList)
computers.groupBy('form_factor')
computers.query();
while (computers.next()) {
gs.info('---> name: {0}\t\tserial number: {1}\t\tform factor: {2}',
[computers.getValue('name'), computers.getValue('serial_number'), computers.form_factor]);
}SQL
SELECT name, serial_number, form_factor
FROM cmdb_ci_computer
WHERE form_factor IN ('desktop','laptop')
GROUP BY form_factor
LIMIT 10
Not available on non-aggregate GlideQuery queries
Case 7 conclusion:
Well, none actually. This is an oddity in GlideRecord. Really it only has a usage with GlideAggregate. OrderBy and a limiter will give you a better list than GroupBy anyway for both cases. I included this just to show an omission (that I agree with actually).
Case 8: WHERE Dot-walk
So this particular test brought out some interesting differences between the two object types.
SQL
UPDATE cmdb_ci_computer
INNER JOIN core_company AS manufacturer ON cmdb_ci_computer.manufacturer = manufacturer.sys_id
SET cmdb_ci_computer.form_factor = 'Laptop'
WHERE manufacturer.name = 'apple'
AND cmdb_ci_computer.form_factor IS NOT NULL
LIMIT 10
// 0.211
// Note I deliberately did not use .updateMultiple()
var computers = new GlideRecord('cmdb_ci_computer');
computers.addQuery('manufacturer.name', 'apple');
computers.addNullQuery('form_factor');
computers.setLimit(10);
computers.query();
while (computers.next()) {
gs.info('---> BEFORE. name: {0}\t\tserial number: {1}\t\tform factor: {2}',
[computers.getValue('name'), computers.getValue('serial_number'), computers.form_factor]);
computers.form_factor = 'Laptop';
gs.info('---> AFTER. name: {0}\t\tserial number: {1}\t\tform factor: {2}',
[computers.getValue('name'), computers.getValue('serial_number'), computers.form_factor]);
computers.update(); // test first before making the update!!!
}// 0.186
// More optimized version
var computers = new GlideRecord('cmdb_ci_computer');
computers.addQuery('manufacturer.name', 'apple');
computers.addNullQuery('form_factor');
computers.setLimit(10);
computers.query();
computers.form_factor = 'Laptop';
computers.updateMultiple();SQL
No change
// 0.261 - meh
// This method worked fine, but there is a better way.
var computerList = [];
var computers = new GlideQuery('cmdb_ci_computer')
.where('manufacturer.name', 'Apple')
.whereNull('form_factor')
.limit(10)
.select()
.forEach(function (computer) {
computerList.push(computer.sys_id)
});
var updatePayload = {form_factor:'Laptop'};
var updatedComputers = new GlideQuery('cmdb_ci_computer')
.where('sys_id','IN',computerList)
.updateMultiple(updatePayload);
gs.info(JSON.stringify(updatedComputers));// 0.153 - so wicked fast in comparison to GlideRecord! What the!
// more elegant by far!
var updatePayload = {form_factor:'Laptop'};
var updatedComputers = new GlideQuery('cmdb_ci_computer')
.where(
new GlideQuery()
.where('manufacturer.name', 'Apple')
.whereNull('form_factor')
)
.limit(10)
.updateMultiple(updatePayload);
gs.info(JSON.stringify(updatedComputers));Case 8 conclusion:
Well, up until that last query it was pretty much business as usual with GlideQuery being slower. Remember that nesting, in this case, actually creates a query under-the-hood and that will execute as one statement. I am not sure exactly the updateMultiple GlideRecord was slower. So, the trade-off here: GlideRecord with updateMultiple: fast with easier maintainability. GlideQuery with updateMultiple faster with more complex maintainability. I do, however, like the GlideQuery here. It appeals to the programmer in me. Could it be that GlideQuery is starting to grow on me??? We will see.
In my next article I will continue our march of comparisons!
Don't forget to check out my comprehensive list of articles here: Link
Enjoy! More to come!
Steven Bell.
If you find this article helps you, don't forget to log in and mark it as "Helpful"!
2 Comments
