See my article: Community Code Snippets: A GlideRecord Survey - Part 2

// 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')); 
}

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')]);
}

// 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]);
    });

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]);
    });

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

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.

WHERE form_factor = 'desktop'

OR form_factor = 'laptop'

// 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')]
    );
}

WHERE form_factor IN ('desktop','laptop')

// 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')]
    );
}

SELECT name, serial_number, form_factor

WHERE form_factor = 'desktop'

OR form_factor = 'laptop'

// 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]
        );
    });

SELECT name, serial_number, form_factor

WHERE form_factor IN ('desktop','laptop')

// 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]
        );
    });

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.

WHERE form_factor IN ('desktop','laptop')

// 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]);
}

SELECT name, serial_number, form_factor

WHERE form_factor IN ('desktop','laptop')

Not available on non-aggregate GlideQuery queries

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).

So this particular test brought out some interesting differences between the two object types.

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

// 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();

// 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));

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"!