Unlearn Series - Check Your SQL Query And Its Performance

[Kalaiarasan Pus]

As a follow up to my earlier blog, I was checking some of my old notes (can't find it now on community search) and stumbled upon a function that will let you check the SQL query and it's performance. gs.trace() appears to be yet another undocumented function in ServiceNow that will let you see the SQL query that the system generates whenever we issue a query() command. Along with the SQL query, it also let's you know how much time the system took to perform the query.

Where can I use this?

- During development on your development instance to fine tune your query for performance, evaluate which version of query/script works best, etc.

Sample:

var tableName = 'task';
var filterString = 'active=true';
var answer = false;

var getRecord = new GlideRecord(tableName);
getRecord.addEncodedQuery(filterString);
getRecord.setLimit(1);
gs.trace(true);
getRecord.query();
gs.trace(false); //set this to false to get only the current query
if (getRecord.hasNext()) {
    answer = true;
}

gs.print('Record found:' + answer);

Note: trace() works only in global scope and seems to be working like session flag. So you have to reset it to false to stop seeing the SQL queries. 

If you liked the content, please share, click helpful, bookmark or leave your valuable comments.

[Pedro Silva4]

Can you post an example or the printout? 

Is this similar to gs.getEncodedQuery() ? 

[Kalaiarasan Pus]

The output getEncodedQuery() and trace() are different. You can execute the script provided here to see the difference.

I have intentionally omitted the output of the script as I want interested people to execute it and see the results for themselves.

[Rick Forristall]

Well, bummer!

Doesn't seem to want to play nicely in a scoped app. Here's my output in scripts - background. I had to set the scope to global to get this to run:

Evaluator: com.glide.script.fencing.MethodNotAllowedException: Function trace is not allowed in scope x_###########
   Caused by error in script at line 14

     11: 
     12: 	latestCase.setLimit(1);
     13: 	latestCase.orderByDesc('sys_created_on');
==>  14: 	gs.trace(true);
     15: 	latestCase.query();
     16: 	gs.trace(false); //set this to false to get only the current query
     17: 

[Kalaiarasan Pus]

Not surprised. Scoped apps just don't seem to have many global equivalent functions.

Have updated the notes section to indicate that it works only on global scope. Thank you for pointing this out.

[chaithu1489]

@Kalaiarasan Pushpanathan not able to find the output in my debug session logs