GlideQuery vs. GlideRecord: A Comparison - Part 1

Starting with this article I will work through a series of comparisons between GlideRecord and GlideQuery. So hang onto your gaming chairs and let's go!

Case 1: A basic query

SQL:

SELECT * FROM task

WHERE priority = 1

// 0.060
var tasks = new GlideRecord('task');
tasks.addQuery('priority',1);
tasks.query();

while (tasks.next()) {
    gs.info('Task "' + tasks.short_description + '" was opened at ' + tasks.opened_at);
}

SQL:

SELECT short_description, opened_at FROM task

WHERE priority = 1

// 0.063
new GlideQuery('task')
    .where('priority', 1)
    .select('short_description', 'opened_at')
    .forEach(function (task) {
        gs.info('Task "' + task.short_description + '" was opened at ' + task.opened_at);
});

Case 1 conclusion:

GlideQuery appears to be slower (I ran several tests and it was consistently slower). This is odd in that it has a much tighter on the surface object model (fields vs. *) which should actually give it a performance boost. It does not. This seems to support the thought that it does not pass through the SQL, but rather runs off of the returned underlying GlideRecord return.

I am liking the format as it feels much more like SQL.

However, don't do this:

new GlideQuery('task')
    .select('short_description', 'opened_at')
    .where('priority', 1)
    .forEach(function (task) {
        gs.info('Task "' + task.short_description + '" was opened at ' + task.opened_at);
    });

Even though it is better formatted to be like SQL it is as General Akbar says: A trap! You will get nothing back, and there is no error!

Case 2: IN statement (a simple OR)

SQL:

SELECT * FROM task

WHERE priority IN (1,2)

// 0.081
var tasks = new GlideRecord('task');
tasks.addQuery('priority','IN', [1,2]);
tasks.query();

while (tasks.next()) {
    gs.info('Task "' + tasks.short_description + '" was opened at ' + tasks.opened_at);
}

SQL:

SELECT short_description, opened_at FROM task

WHERE priority IN (1,2)

// 0.093
new GlideQuery('task')
    .where('priority', 'IN', [1,2])
    .select('short_description', 'opened_at')
    .forEach(function (task) {
        gs.info('Task "' + task.short_description + '" was opened at ' + task.opened_at);
    });

Case 2 Conclusion:With the addition of more records to the result set we are seeing a dip in the GlideQuery performance.

Case 3: SET LIMIT

SQL

SELECT * FROM task

LIMIT 1

// 0.030
var taskList = new GlideRecord('task');
taskList.setLimit(1);
taskList.query();

if (taskList.next()) {
    gs.info('---> number: ' + taskList.getValue('number')); 
}

SQL:

SELECT short_description, opened_at FROM task

LIMIT 1

// 0.033
new GlideQuery('task')
    .limit(1)
    .select('short_description', 'opened_at')
    .forEach(function (task) {
        gs.info('Task "' + task.short_description + '" was opened at ' + task.opened_at);
    });

Case 3 conclusion:

Pretty close timings, but what do you expect from one record. My concern was that the entire record set was being brought back then trimmed to a single record. That does not appear to be the case.

Case 4: Operators

Good articles to read on this topic

GlideQuery Reference

Query Operators

SQL:

SELECT * FROM task

WHERE number='PRB0000001'

// 0.032
// Test inline queries
var taskList = new GlideRecord('task');
taskList.addQuery('number', '=', 'PRB0000001'); // implied equals is fine here as well
taskList.query();

if (taskList.next()) {
    gs.info('---> number: ' + taskList.getValue('number')); 
}

SQL:

SELECT short_description, opened_at FROM task

WHERE number='PRB0000001'

// 0.032
// inline queries appear to work fine
new GlideQuery('task')
    .where('number', '=', 'PRB0000001') // implied equals is fine here as well
    .select('short_description', 'opened_at')
    .forEach(function (task) {
        gs.info('Task "' + task.short_description + '" was opened at ' + task.opened_at);
    });

Case 4 conclusion:

Same time. BTW, I like that it handles inline queries in a dot-walk. Nice. Same functionality for operators.

I am going to stop there. I will pick up with additional examples in my next article.

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

Labels: