Efficient Data Migration in ServiceNow with GlideRecord.chooseWindow()

Ratnakar7

When migrating or updating thousands of records in ServiceNow, running one massive GlideRecord query can slow down your instance and make the operation harder to control.

A script that works perfectly for 200 records may behave very differently with 200,000 records. Large result sets can consume more memory, hold database resources longer, trigger long-running transactions, and put pressure on business rules, flows, audit history, and integrations.

As a ServiceNow architect, I usually look at bulk operations with one question in mind:

Can this be processed safely in smaller, predictable chunks?

That is where GlideRecord.chooseWindow() becomes useful.

The Problem with Unbatched Bulk Operations

A common bulk update script may look like this:

var gr = new GlideRecord('ast_contract');
gr.addQuery('active', true);
gr.query();

while (gr.next()) {
    gr.setValue('u_migration_status', 'migrated');
    gr.update();
}

This is simple, but it can be risky at scale.

If the query returns a very large number of records, the script may:

Run for too long in a single transaction.
Put unnecessary load on the database.
Lock or heavily touch busy tables.
Trigger business rules or flows thousands of times.
Fail halfway through with limited restart control.
Make troubleshooting difficult because everything happens in one large run.

Bulk work should be designed like a controlled process, not a one-time gamble.

Solution Overview: Batch Records with chooseWindow()

GlideRecord.chooseWindow(firstRow, lastRow) allows a script to retrieve only a specific range of records from a query result.

Think of it like this:

chooseWindow() is pagination for server-side scripts.

In a list view, pagination lets a user see records page by page. With chooseWindow(), your script processes records batch by batch.

For example:

Batch Method Call Records Processed

Batch 1	chooseWindow(0, 500)	Rows 0-499
Batch 2	chooseWindow(500, 1000)	Rows 500-999
Batch 3	chooseWindow(1000, 1500)	Rows 1000-1499

The first number is inclusive. The second number is exclusive. So chooseWindow(0, 500) returns up to 500 records.

That mental model makes the method easy to grasp: instead of asking ServiceNow to process the whole result set at once, you ask for one “page” of records at a time.

Example Scenario: Migrating Contract Records

Let’s say we are migrating Contract records from one ServiceNow instance to another.

A safe migration approach is to load source data into a staging table first, then process that staging data in batches.

Example staging table:

u_contract_migration_staging

Example fields:

u_source_sys_id
u_vendor
u_short_description
u_start_date
u_end_date
u_migration_status
u_target_sys_id
u_error_message

The script below processes 500 staging records at a time and inserts or updates records in the Contract table.

Code Example: Batch Migration Using chooseWindow()

(function migrateContractsInBatches() {
    var BATCH_SIZE = 500;
    var batchNumber = 1;
    var totalProcessed = 0;

    while (true) {
        var staging = new GlideRecord('u_contract_migration_staging');
        staging.addQuery('u_migration_status', 'ready');

        // Use a stable order so batches are predictable.
        staging.orderBy('sys_created_on');
        staging.orderBy('sys_id');

        // Process the first 500 remaining ready records.
        staging.chooseWindow(0, BATCH_SIZE);

        // Avoid an unnecessary COUNT(*) query when total count is not needed.
        staging.setNoCount();

        staging.query();

        var processedThisBatch = 0;

        while (staging.next()) {
            processedThisBatch++;

            try {
                var contract = new GlideRecord('ast_contract');

                // Use a legacy/source ID field as the migration key.
                var found = contract.get(
                    'u_legacy_contract_sys_id',
                    staging.getValue('u_source_sys_id')
                );

                if (!found) {
                    contract.initialize();
                    contract.setValue(
                        'u_legacy_contract_sys_id',
                        staging.getValue('u_source_sys_id')
                    );
                }

                contract.setValue('vendor', staging.getValue('u_vendor'));
                contract.setValue('short_description', staging.getValue('u_short_description'));
                contract.setValue('starts', staging.getValue('u_start_date'));
                contract.setValue('ends', staging.getValue('u_end_date'));

                var targetSysId = found ? contract.update() : contract.insert();

                staging.setValue('u_target_sys_id', targetSysId);
                staging.setValue('u_migration_status', 'migrated');
                staging.setValue('u_error_message', '');
                staging.update();

            } catch (ex) {
                staging.setValue('u_migration_status', 'error');
                staging.setValue('u_error_message', ex.message || ex.toString());
                staging.update();
            }
        }

        totalProcessed += processedThisBatch;

        gs.info(
            'Contract migration batch ' + batchNumber +
            ' processed ' + processedThisBatch +
            ' records. Total processed: ' + totalProcessed
        );

        // If fewer than 500 records were processed, there are no more full batches.
        if (processedThisBatch < BATCH_SIZE) {
            break;
        }

        batchNumber++;
    }

    gs.info('Contract migration completed. Total processed: ' + totalProcessed);
})();

Why This Pattern Works

You may notice this line:

staging.chooseWindow(0, BATCH_SIZE);

At first glance, you might expect the script to use:

staging.chooseWindow(500, 1000);

for the second batch.

But in this migration pattern, each processed record is changed from ready to migrated. That means the query result changes after every batch. If we move to rows 500-999 after the first 500 records are removed from the “ready” result set, we may accidentally skip records.

So for status-based processing, this is safer:

staging.chooseWindow(0, BATCH_SIZE);

In plain English:

Always process the first 500 records that are still ready.

This makes the script easier to restart and reduces the risk of missed records.

Alternative Pattern: Paging Through a Stable Result Set

If your script is reading from a stable result set and not changing the query criteria, you can page forward using a start position.

var batchSize = 500;
var start = 0;

while (true) {
    var gr = new GlideRecord('ast_contract');
    gr.addQuery('active', true);
    gr.orderBy('sys_created_on');
    gr.orderBy('sys_id');

    gr.chooseWindow(start, start + batchSize);
    gr.setNoCount();
    gr.query();

    var count = 0;

    while (gr.next()) {
        count++;

        // Example processing logic.
        gr.setValue('u_review_required', true);
        gr.update();
    }

    if (count < batchSize) {
        break;
    }

    start += batchSize;
}

Use this approach only when the underlying result set remains stable during processing.

Architect's Tip: Make It Restartable

For migrations, I prefer using a staging table with a migration status field because it gives you control.

A record can move through statuses such as:

ready
migrated
skipped
error

This makes the migration easier to monitor, restart, and audit. If the job fails, you do not need to guess where it stopped. You simply rerun records where u_migration_status is ready or review records marked error.

Best Practices

- Always test in sub-production before running in production. Validate the query, batch size, business rules, and expected data changes.

- Use batching to avoid large, long-running operations. A batch size of 500 is a practical starting point, but the right size depends on table size, rules, integrations, and instance load.

- Use indexed filters where possible. Batching helps, but it does not fix an expensive query.

- Use a stable sort order. Combining orderBy('sys_created_on') and orderBy('sys_id') helps keep batch results predictable.

- Be careful when updating fields used in your query filter. If your script changes the same field it queries on, process the first batch of remaining records each time.

- Avoid mixing setLimit() and chooseWindow() unless you fully understand the behavior. ServiceNow documentation notes that setLimit() can interact with chooseWindow() in ways that may produce unexpected results.

- Use setNoCount() when you do not need the total row count. This can help avoid unnecessary count queries on very large tables.

- Monitor logs responsibly. Log batch-level summaries instead of logging every record in production.

- Avoid peak hours. Even well-designed batch scripts still perform database work and can trigger platform logic.

- Validate after migration. Compare counts, spot-check records, review errors, and confirm references were mapped correctly.

Summary: Key Takeaways

GlideRecord.chooseWindow() is like pagination for server-side scripts.

Batching records improves scalability and helps prevent performance bottlenecks.

Processing records in chunks keeps bulk updates and migrations more predictable.

For migration work, use staging tables and status fields to make the process restartable.

Use chooseWindow() before query().

Use setNoCount() when total row count is not required.

Always test in sub-production before running against production data.

Conclusion

Bulk updates and migrations are part of real ServiceNow platform ownership. The difference between a risky script and a production-ready script is often the design around it.

GlideRecord.chooseWindow() gives us a clean way to process records in manageable batches. When combined with stable ordering, staging tables, status tracking, and careful testing, it helps keep large data operations controlled, scalable, and easier to support.

In short: do not make ServiceNow carry the whole dataset at once. Give it one well-defined batch at a time.

References

Thanks,

Ratnakar