Large Table Query Optimization Pattern…

advancedTested on Xanadu

The Large Table Query Optimization Pattern addresses performance bottlenecks when querying tables containing millions of records in ServiceNow. Standard GlideRecord queries that work fine on smaller tables can cause timeout errors, memory exhaustion, and poor user experience when applied to large datasets like audit logs, metrics, or historical incident data.

This pattern employs several key strategies: leveraging database indexes by placing indexed fields first in query conditions, avoiding OR conditions in leading filters which prevent index usage, using getAggregate() methods instead of iteration for counting and calculations, and partitioning large queries into smaller chunks based on date ranges or other logical divisions. The pattern also emphasizes proper query structure to ensure the ServiceNow database layer can execute queries efficiently.

By following these optimization techniques, queries that previously took minutes or failed entirely can execute in seconds, improving both user experience and system stability while reducing database load across the entire ServiceNow instance.

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

Use This When

Use this pattern when querying tables with over 100,000 records, especially tables that grow continuously like audit logs, metrics, or transaction records. Apply these techniques when experiencing query timeouts, slow dashboard loading, or when building reports on large historical datasets.

Avoid When

Avoid over-optimizing queries on small tables where standard GlideRecord patterns perform adequately, as the added complexity isn't justified. Don't use query partitioning for real-time operations that require immediate consistency across the entire dataset, and avoid these patterns when query flexibility is more important than performance.

Implementation

Server-side

var LargeTableQueryUtil = Class.create();
LargeTableQueryUtil.prototype = {
    initialize: function() {
        this.BATCH_SIZE = 10000;
        this.DATE_FORMAT = 'yyyy-MM-dd HH:mm:ss';
    },

    // Optimized count using getAggregate instead of iteration
    getIncidentCountByPriority: function(priority, startDate, endDate) {
        var ga = new GlideAggregate('incident');
        // Place indexed field (priority) first in query
        ga.addQuery('priority', priority);
        ga.addQuery('opened_at', '>=', startDate);
        ga.addQuery('opened_at', '<=', endDate);
        ga.addAggregate('COUNT');
        ga.query();
        
        if (ga.next()) {
            return parseInt(ga.getAggregate('COUNT'));
        }
        return 0;
    },

    // Partitioned query for processing large datasets
    processLargeIncidentUpdate: function(startDate, endDate) {
        var results = [];
        var currentDate = new GlideDateTime(startDate);
        var finalDate = new GlideDateTime(endDate);
        
        // Process in daily chunks to avoid memory issues
        while (currentDate.before(finalDate)) {
            var nextDate = new GlideDateTime(currentDate);
            nextDate.addDaysUTC(1);
            
            var gr = new GlideRecord('incident');
            // Use indexed field first - sys_created_on is always indexed
            gr.addQuery('sys_created_on', '>=', currentDate.getValue());
            gr.addQuery('sys_created_on', '<', nextDate.getValue());
            // Additional filters after date range
            gr.addQuery('active', true);
            gr.setLimit(this.BATCH_SIZE);
            gr.query();
            
            var batchCount = 0;
            while (gr.next() && batchCount < this.BATCH_SIZE) {
                // Process each record
                this._updateIncidentMetrics(gr);
                batchCount++;
            }
            
            results.push({
                date: currentDate.getDisplayValue(),
                processed: batchCount
            });
            
            currentDate = nextDate;
        }
        
        return results;
    },

    // Optimized query avoiding OR conditions in leading position
    getHighPriorityIncidents: function(categories) {
        var allResults = [];
        
        // Instead of OR condition, run separate queries for each category
        for (var i = 0; i < categories.length; i++) {
            var gr = new GlideRecord('incident');
            // Place indexed fields first
            gr.addQuery('category', categories[i]);
            gr.addQuery('priority', 'IN', '1,2'); // High priority only
            gr.addQuery('active', true);
            gr.orderByDesc('sys_created_on');
            gr.setLimit(1000); // Reasonable limit for each category
            gr.query();
            
            while (gr.next()) {
                allResults.push({
                    number: gr.getValue('number'),
                    category: gr.getValue('category'),
                    priority: gr.getValue('priority'),
                    created: gr.getValue('sys_created_on')
                });
            }
        }
        
        return allResults;
    },

    // Use aggregate for statistical calculations
    getIncidentStatistics: function(assignmentGroup, days) {
        var startDate = gs.daysAgoStart(days);
        
        var ga = new GlideAggregate('incident');
        ga.addQuery('assignment_group', assignmentGroup);
        ga.addQuery('sys_created_on', '>=', startDate);
        ga.groupBy('state');
        ga.addAggregate('COUNT');
        ga.addAggregate('AVG', 'business_duration');
        ga.query();
        
        var stats = {};
        while (ga.next()) {
            stats[ga.getValue('state')] = {
                count: parseInt(ga.getAggregate('COUNT')),
                avgDuration: parseFloat(ga.getAggregate('AVG', 'business_duration'))
            };
        }
        
        return stats;
    },

    _updateIncidentMetrics: function(incidentGR) {
        // Helper method for batch processing
        // Update metrics without triggering business rules
        incidentGR.setWorkflow(false);
        incidentGR.setValue('u_last_processed', gs.nowDateTime());
        incidentGR.update();
    },

    type: 'LargeTableQueryUtil'
};

The code demonstrates indexed field placement by putting priority and category filters before date ranges, uses GlideAggregate for counting instead of iterating through records, and implements date-based partitioning to process large datasets in manageable chunks. Notice how OR conditions are avoided by running separate optimized queries for each category, and batch limits prevent memory exhaustion during large data processing operations.

Alternatives

Database Views with Custom Tables

Create custom tables with pre-aggregated data or database views for frequently accessed large datasets. Provides faster query performance but requires additional storage space and maintenance overhead for keeping aggregated data synchronized.

Scheduled Jobs with Caching

Use scheduled jobs to pre-calculate large query results and store them in cache tables or system properties. Eliminates real-time query performance issues but introduces data staleness and complexity in cache invalidation strategies.

REST API with External Processing

Export large datasets to external systems for processing and analysis, then import results back to ServiceNow. Removes processing load from ServiceNow but adds integration complexity and potential data synchronization challenges.

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