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.
____________________________________________________________________________
I kept getting hit with questions about what I thought on GlideQuery. Especially whether it was a better way to go than GlideRecord. What benefits were there? What detractions? Was one faster than the other?
First I would recommend reading
He has a lot of really great info and will bring you up to an intermediate level understanding of what can be done with GlideQuery.
So, after you have done that then check out the various functions available: Link
I will be using query examples from my GlideRecord article series I did awhile back: Link
Caveats
1. GlideQuery appears to built on top of the GlideRecord object (it actually seems to be a series of prototypes that extend the object functionality; it would be interesting to actually know - note my tests seem to prove this out).
2. GlideQuery is Globally scoped. This reenforces the GlideRecord object prototyping thought.
3. GlideQuery can only do SELECT [[field list]]. GlideRecord can only do SELECT *.
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"!
5 Comments
