Querying Tables Best Practices

Querying tables is one of those things that looks harmless until the same script runs against production-sized data. A query that feels fine with a few hundred records can become slow, expensive, and noisy when the table grows.

These are the habits I try to keep in every ServiceNow script that reads data.

1. Filter in the query, not in the loop

The most common mistake is querying a broad set of records and then doing the real filtering inside while (gr.next()).

Bad:

var gr = new GlideRecord("incident");
gr.query();
while (gr.next()) {
  if (gr.active && gr.priority == 1) {
    // process record
  }
}

Better:

var gr = new GlideRecord("incident");
gr.addActiveQuery();
gr.addQuery("priority", 1);
gr.query();

while (gr.next()) {
  // process record
}

Make the database do the filtering. The less data you pull into the script, the better.

2. Prefer indexed or unique fields

If you know the sys_id, use get(). If you know a unique field such as number, query by that. Queries on indexed fields are usually much safer than scanning a large table on a non-indexed text field.

var inc = new GlideRecord("incident");
if (inc.get("46d44a2f2f1221107fd78a785699b6a7")) {
  gs.info(inc.getValue("number"));
}

If you are repeatedly filtering on the same field in custom code, it may be a sign that the table needs a better index strategy instead of more scripting.

3. Always add a limit when you do not need the full result set

If the script needs only the first matching record, or just a small batch, say that explicitly.

var gr = new GlideRecord("sys_user");
gr.addActiveQuery();
gr.addQuery("department", deptSysId);
gr.setLimit(20);
gr.query();

setLimit() is a small line of code that prevents accidental full-table reads.

4. Use GlideAggregate for counts

If your goal is to know how many records match a condition, a normal GlideRecord loop is the wrong tool.

Bad:

var count = 0;
var gr = new GlideRecord("incident");
gr.addActiveQuery();
gr.query();
while (gr.next()) {
  count++;
}

Better:

var agg = new GlideAggregate("incident");
agg.addActiveQuery();
agg.addAggregate("COUNT");
agg.query();

if (agg.next()) {
  gs.info(agg.getAggregate("COUNT"));
}

That is clearer and usually much cheaper.

5. Avoid nested queries inside loops

This pattern scales badly:

var task = new GlideRecord("task");
task.addActiveQuery();
task.query();

while (task.next()) {
  var user = new GlideRecord("sys_user");
  if (user.get(task.getValue("assigned_to"))) {
    // do something
  }
}

One query inside another query can become hundreds or thousands of extra database hits.

If possible, collect the IDs you need first and do one follow-up query, or redesign the logic so the second lookup is not repeated for every row.

6. Use encoded queries carefully

Encoded queries are practical, especially when copied from a list filter, but they become hard to maintain when they are dropped into a script with no explanation.

var gr = new GlideRecord("incident");
gr.addEncodedQuery("active=true^priority=1^assignment_group=" + groupSysId);
gr.query();

This is acceptable, but if the query is important or non-obvious, add a short comment above it. Future you should not need to decode the whole string from scratch.

7. Be intentional about display values

Inside large loops, prefer getValue() unless you truly need the human-readable label.

var callerId = gr.getValue("caller_id");
var callerName = gr.getDisplayValue("caller_id");

getDisplayValue() is useful, but it is not free. Use it for output, not by default for every internal check.

8. Use the secure API when security matters

If the code runs in a context where ACL enforcement matters, use GlideRecordSecure instead of plain GlideRecord.

var gr = new GlideRecordSecure("incident");
gr.addQuery("caller_id", gs.getUserID());
gr.query();

The query may still work with GlideRecord, but the behavior is not equivalent from a security point of view.

9. Log the final query while debugging

When a query behaves unexpectedly, log the encoded query before you start guessing.

var gr = new GlideRecord("incident");
gr.addActiveQuery();
gr.addQuery("category", "network");
gs.info(gr.getEncodedQuery());
gr.query();

It is a simple way to confirm that the conditions you think you built are the conditions the instance will run.

A safe default pattern

For most scripts, this shape is a good starting point:

var gr = new GlideRecord("incident");
gr.addActiveQuery();
gr.addQuery("assignment_group", groupSysId);
gr.orderByDesc("sys_created_on");
gr.setLimit(100);
gr.query();

while (gr.next()) {
  var number = gr.getValue("number");
  var shortDescription = gr.getValue("short_description");

  // process only what you really need
}

The query is specific, the result set is bounded, and the loop does not do unnecessary work.

Final thought

Most query performance problems are not caused by one dramatic mistake. They come from small habits: broad filters, unnecessary loops, repeated lookups, and using the wrong API for the job.

Writing tighter queries usually makes the script faster and makes your intent easier to read at the same time.