Avoid querying the database inside a loop

Server-sideintermediateTested on Xanadu

Chrome Extension

Sourdough: ServiceNow Monitoring and Analytics

A Chrome extension for ServiceNow Admins and Developers with essential tools, analytics, graphs and monitoring features.

Instance HealthGraphs & ChartsAPI HealthDeveloper ToolsQuick SearchInstance Switcher

Add to Chrome

Free to install. Pro $5/month after a 14-day no-card trial.
Pro requires the ServiceNow admin role. Upgrade inside the extension.

Overview

Tasks

CMDB

API

Metrics

Monitor

Internals

Instance:sourdoughdev·Version:Yokohama

Instance StateONLINE

System StatusFully Operational

Session Timeout90 minutes

Logged-In Sessions2 (20 active)

Build Nameyokohama-12-18-2024_p1

IP Address10.159.128.43

Instance HealthHealth Score: 90%

🔥 5dSourdough (Chrome Plugin)Dark Mode

Batch database lookups instead of querying once per iteration

Script Include

var IncidentEnricher = Class.create();
IncidentEnricher.prototype = {
    
    enrichIncidentsWithCallerInfo: function(incidentSysIds) {
        if (!incidentSysIds || incidentSysIds.length === 0)
            return [];
        
        // First pass: collect all caller sys_ids from incidents
        var callerIds = [];
        var incidentGr = new GlideRecord('incident');
        incidentGr.addQuery('sys_id', 'IN', incidentSysIds.join(','));
        incidentGr.query();
        
        while (incidentGr.next()) {
            if (incidentGr.caller_id && !gs.nil(incidentGr.caller_id))
                callerIds.push(incidentGr.caller_id.toString());
        }
        
        // Second pass: batch lookup caller details
        var callerMap = {};
        if (callerIds.length > 0) {
            var callerGr = new GlideRecord('sys_user');
            callerGr.addQuery('sys_id', 'IN', callerIds.join(','));
            callerGr.query();
            
            while (callerGr.next()) {
                callerMap[callerGr.sys_id.toString()] = {
                    name: callerGr.name.toString(),
                    email: callerGr.email.toString(),
                    department: callerGr.department.getDisplayValue()
                };
            }
        }
        
        // Third pass: combine incident and caller data
        var results = [];
        incidentGr = new GlideRecord('incident');
        incidentGr.addQuery('sys_id', 'IN', incidentSysIds.join(','));
        incidentGr.query();
        
        while (incidentGr.next()) {
            var callerInfo = callerMap[incidentGr.caller_id.toString()] || {};
            results.push({
                incident_number: incidentGr.number.toString(),
                state: incidentGr.state.toString(),
                caller_name: callerInfo.name || '',
                caller_email: callerInfo.email || '',
                caller_department: callerInfo.department || ''
            });
        }
        
        return results;
    },
    
    type: 'IncidentEnricher'
};

The N+1 query problem is ServiceNow's most common performance killer — you execute one query to get a list of records, then execute N additional queries inside the loop to fetch related data. What looks like innocent lookups becomes hundreds of database hits that bring your instance to its knees. The platform doesn't warn you about this; it just starts timing out your scripts when the query volume gets ridiculous. The solution is always the same: collect all the IDs you need first, then use addQuery('field', 'IN', ids) to batch the lookup into a single query.

When to use this

When you need to enrich records with data from related tables in a Business Rule or Script Include
When processing a bounded set of records where you control the input size (under 1000 records)
When you need multiple field values from the related records, not just existence checks
When running in Scheduled Jobs that process batches of records for data cleanup or reporting

When NOT to use this

Don't use this in Client Scripts — use GlideAjax to call a Script Include that does the batching server-side
Don't use this when you only need counts or existence checks — use GlideAggregate or GlideRecord.hasNext()
Don't use this for unbounded result sets — you'll hit memory limits before you hit query limits
Don't batch reference field lookups that are already cached by getDisplayValue() — you're optimizing something that's already fast

Key behaviors and gotchas

The IN operator expects a comma-separated string, not an array — always use join(',')
Reference fields return GlideElementReference objects — call toString() to get the sys_id for comparisons
Empty reference fields and deleted records will create gaps in your lookup map — always provide fallback values
ACLs still apply to batch queries — records the user can't read will silently disappear from results
Domain separation affects IN queries — records in different domains may not be visible even with valid sys_ids
Large IN clauses (over 1000 values) can hit database query length limits — chunk them if needed

⚠️

Building lookup maps with sys_id keys requires toString() on both sides — GlideRecord sys_id fields and reference field values are objects, not strings. Missing toString() calls create lookup failures that are invisible until you test with real data.

Free Newsletter

Enjoying this? Get one deep-dive per week.

Join 1,000+ ServiceNow pros — scripts, GlideRecord patterns, Flow Designer techniques, and career moves. Free.

Alternative Approaches

GlideAggregate for counts only

var IncidentCounter = Class.create();
IncidentCounter.prototype = {
    
    getIncidentCountsByUser: function(userSysIds) {
        var counts = {};
        
        var ga = new GlideAggregate('incident');
        ga.addQuery('caller_id', 'IN', userSysIds.join(','));
        ga.addAggregate('COUNT');
        ga.groupBy('caller_id');
        ga.query();
        
        while (ga.next()) {
            counts[ga.caller_id.toString()] = parseInt(ga.getAggregate('COUNT'));
        }
        
        return counts;
    },
    
    type: 'IncidentCounter'
};

Use when you only need counts or sums, not the actual field values from related records.

Trade-offs: Much faster for aggregation queries, but can't retrieve field values for display or processing.

API Methods Used

GlideRecord.addQuery()GlideRecord.query()GlideAggregate.groupBy()

Test Your Knowledge

Quick 3-question quiz — see how your ServiceNow skills stack up.

Question 1 of 3Performance

A list view on a table with millions of records is slow. Best fix?

Select an answer to continue