SuiteQL Pagination: How to Query More Than 1,000 Rows

When working with SuiteQL in NetSuite, you may need to retrieve more records than a single query execution returns.

The N/query module provides two methods for executing SuiteQL:

• query.runSuiteQL() for unpaged results.
• query.runSuiteQLPaged() for retrieving results across multiple pages.

Understanding the difference is useful when building data lookups, processing large record sets, or creating caches for SuiteScript automation.

Running a Basic SuiteQL Query

The query.runSuiteQL() method executes a SuiteQL statement and returns a result set.

For example:

const sql = `
    SELECT
        id,
        itemid
    FROM
        item
    WHERE
        id = ? OR id = ?
`;

const myQueryResult = query.runSuiteQL({
    query: sql,
    params: [123, 456]
});

const mappedResults = myQueryResult.asMappedResults();

The question marks (?) are placeholders for the values supplied through the params array.

Using parameters is preferable to concatenating user-provided values directly into the SQL string.

The asMappedResults() method converts the query results into an array of JavaScript objects.

For example, a result might look like:

[
    {
        id: 123,
        itemid: 'ITEM-123'
    },
    {
        id: 456,
        itemid: 'ITEM-456'
    }
]

This format makes it easier to access returned values by their column aliases.

Grouping SuiteQL Results into a Lookup Cache

You can also group query results by a particular field.

For example, the following code groups results by itemid:

const myLookupCache = mappedResults.reduce(
    (cache, result) => {
        if (!cache[result.itemid]) {
            cache[result.itemid] = [];
        }

        cache[result.itemid].push(result);

        return cache;
    },
    {}
);

This creates an object where each item ID maps to an array of matching results.

Grouping results this way can be useful when a script needs to perform repeated lookups without executing another query each time.

What Is the SuiteQL Result Limit?

NetSuite's query.runSuiteQL() method returns a maximum of 5,000 results.

If your query could return more than that, you should use query.runSuiteQLPaged() to retrieve the results in pages.

The paged method also allows you to control how many rows are returned per page.

Running a Paged SuiteQL Query

The query.runSuiteQLPaged() method executes SuiteQL and provides access to the results through a paged data object.

For example:

const sql = `
    SELECT
        id,
        itemid
    FROM
        item
    ORDER BY
        id
`;

const pagedResults = query.runSuiteQLPaged({
    query: sql,
    pageSize: 1000
});

The pageSize property controls the number of results returned per page.

NetSuite supports page sizes from 5 to 1,000 results, with a default of 50.

The ORDER BY clause is important because paged queries should use a unique, unambiguous sort order to avoid duplicate or missing results during pagination.

Iterating Through SuiteQL Result Pages

Unlike an unpaged query, a paged query requires you to retrieve and process each page.

One approach is to use the pageRanges array and fetch() method.

const myLookupCache = {};

pagedResults.pageRanges.forEach((pageRange) => {
    const page = pagedResults.fetch({
        index: pageRange.index
    });

    const results = page.data.asMappedResults();

    results.forEach((result) => {
        if (!myLookupCache[result.itemid]) {
            myLookupCache[result.itemid] = [];
        }

        myLookupCache[result.itemid].push(result);
    });
});

This code:

1. Creates an empty lookup cache.
2. Iterates through the available page ranges.
3. Fetches each page.
4. Converts the page's results into mapped objects.
5. Groups the results by item ID.

The resulting cache has the same general structure as the unpaged example, but it can process results across multiple pages.

SuiteQL Paged vs. Unpaged Queries

The appropriate method depends on how many records you expect the query to return and how you intend to process them.

SuiteQL Performance Considerations

Pagination helps you retrieve larger result sets, but it doesn't eliminate performance or governance considerations.

Use Filters to Limit Results

Avoid retrieving more records than your script actually needs.

Use appropriate WHERE conditions to reduce the number of returned rows.

Select Only the Required Columns

Instead of using SELECT *, identify the fields your script needs.

This reduces unnecessary data processing.

Use a Consistent Sort Order

Paged queries should use a deterministic ORDER BY clause.

If multiple rows share the same sort value, include another field that makes the ordering unique.

Consider Script Memory Usage

If you're processing a large number of results, storing every row in a JavaScript object can consume significant memory.

For larger workloads, consider processing each page as it is retrieved rather than accumulating the entire result set.

Monitor Governance and Query Limits

Both SuiteQL execution methods consume script governance units.

Paged queries also have result-count and page-count limits, which depend on the account configuration.

For very large datasets, review the relevant NetSuite limits before designing the processing workflow.

Final Thoughts

Use query.runSuiteQL() when your query returns a manageable number of records and you don't need pagination.

When the result set may exceed 5,000 rows, query.runSuiteQLPaged() provides a way to retrieve and process the results across multiple pages.

The main difference is how you access the returned data. With unpaged queries, you can call asMappedResults() directly on the result set. With paged queries, you retrieve each page and call asMappedResults() on that page's data.

For scripts that build lookup caches or process larger datasets, pagination provides greater flexibility while helping you manage the results in smaller batches.