Team, I am running a query via SuiteQL within a SuiteScript and I get 5000 records. Is there a way to go around this limit?

Yes. You can manage the paging with the native run paged function or I use this to run all my queries and it returns and array of objects. It will only work in 2.1 syntax

/**
 *
 * @param {string} sql
 * @param {integer} [limit=9999999999]
 * @param {integer} [pageSize=5000]
 * @returns {*[]}
 */
const runQuery = ({ sql, limit, pageSize }) => {
    if ( !sql ) return [];

    const sqlPageSize = pageSize || 5000;
    let paginatedRowBegin = 1;
    const paginatedRowEnd = limit || 9999999999;
    let records = [];
    let isMoreRecords = true;
    do {
        const paginatedSQL = `SELECT * FROM (SELECT ROWNUM AS ROWNUMBER, * FROM (  ${sql} ) )  WHERE ( ROWNUMBER BETWEEN ${paginatedRowBegin} AND ${paginatedRowEnd} )`;
        const queryResults = query.runSuiteQL({ query: paginatedSQL, params: [] }).asMappedResults();
        records.push( ...queryResults );
        if ( queryResults.length < sqlPageSize ) {
            isMoreRecords = false;
        }
        paginatedRowBegin += sqlPageSize;
    } while ( isMoreRecords );

    return records;
};

that looks beautiful, thank you, I will try it

What's the advantage to using this over the native runPaged?

Less Usage and I found the performance was also better. I tested with the two below paged functions. With the manual pagination, you can page by the 5000 but with the native function, you can only page by max 1000. It’s 10 usage for every page so depending on the results, it can start to add up.

const queryFetch = ({ sql }) => {
    const queryResult = query.runSuiteQLPaged({
        query: sql,
        pageSize: 1000,
    });
    let results = [];
    queryResult.pageRanges.forEach(( page, index ) => {
        queryResult.fetch({ index }).data.results.forEach(( rowObj ) => {
            results.push( rowObj.asMap());
        });
    });
    return results;
};
const queryIterator = ({ sql }) => {
    const queryResult = query.runSuiteQLPaged({
        query: sql,
        pageSize: 1000,
    });
    let results = [];
    queryResult.iterator().each(( page ) => {
        page.value.data.iterator().each(( row ) => {
            results.push( row.value.asMap());
            return true;
        });
        return true;
    });
    return results;
};

out of curiosity, is there a specific reason you went with the more verbose function syntax rather than just

function queryFetch( { sql }) {}

?

are you referring to the arrow function notation ? const name= () => {}

yes

just my preference of syntax. No difference in this use case to use arrow notation or function

10-4. There are differences but indeed perhaps not relevant to how/where this code is defined in your codebase.

correct. feel free to use either one in this use case

Also curious that your experiment against

runPaged()

was faster doing manual paging? Do you have a feel for how much faster it was?

the manual paging was significantly faster. It depended on how many results. for small result sets (<5000), there wasn’t a huge difference but I tested with upwards of 25000 results and it was easily cut in half if not more. this one will log out the elapsed time. This is how I tested the time. I also use manual paging method in the suiteql editor I built

const queryFetch = ({ sql }) => {
    const startTime = new Date().getTime();
    const queryResult = query.runSuiteQLPaged({
        query: sql,
        pageSize: 1000,
    });
    let results = [];
    queryResult.pageRanges.forEach(( page, index ) => {
        queryResult.fetch({ index }).data.results.forEach(( rowObj ) => {
            results.push( rowObj.asMap());
        });
    });
    log.debug( "queryFetch total time", ( new Date().getTime() - startTime ) / 1000 );
    return results;
};

wow, how sad for NS that "native" methods could be 50% slower

yeah. that’s just what I experienced in a TSTDRV account, you might experience something different. If you test, let us know if you saw the same

i usually say that runSuiteQLPaged is faster, but turns out ive been doing too much filtering

I’m kinda confused by what allResults is. At then end, you’re also saying that manual pagination is faster than queryPaged?

allResults is an array, the array's length is sufficient for filtering the starting row

ahhh and you don’t have to put a max since it will only return 5000. makes sense!

I personally would have thought adding a filter on which row to stop at would have made the query faster, not slower

was it significantly slower? how many results were you testing with?

first version i shared runs in 4.7 seconds second one 1.8 seconds third 1.4 seconds

wow. pretty crazy

quite a difference