https://netsuiteprofessionals.com logo
Join Slack
Powered by
Is anyone using return type of “suiteql” in MapRed...
# suitescript
e
Is anyone using return type of “suiteql” in MapReduce getInputData() stage? I have what seems like a fairly small dataset being returned and yet the script just seems to get stuck in the getInputData() stage forever. I’m assuming it will time out eventually. I have this same result in both Sandbox and Prod.
Copy code
return {
            type: 'suiteql',
            query: activeLineSearchQuery
        };
Here is documentation where NetSuite says this can be done: https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_158039627694.html
s
I have done this many times, in many M/R scripts I have running in production, without problems. What is the query being return with the variable activeLineSearchQuery ?
e
Thanks, @scottvonduhn. Just due to the complexity of the query I had avoided posting but there’s nothing sensitive about it.
Copy code
SELECT
                tranhistory.version,
                tranhistory.internalid,
                line.id as lineid
            FROM TransactionLine as line
            INNER JOIN (
                SELECT
                    hist.internalid,
                    MAX(hist.version) as version
                FROM TransactionHistory as hist
                WHERE hist.action='CHANGE'
                GROUP BY hist.internalid
            ) as tranhistory ON line.transaction=tranhistory.internalid
            LEFT JOIN customrecord_rs_trans_active_lines as activelines
                ON tranhistory.internalid=activelines.custrecord_rs_transaction_id
                AND tranhistory.version=activelines.custrecord_rs_transaction_version
                AND line.id = activelines.custrecord_rs_line_number
            WHERE line.id != 0
            AND activelines.recordid IS NULL
I can run this particular suiteQL query in any other context and it runs within ~30 seconds or so
Here is the entire getInputData method:
Copy code
function getTransactionLines(context) {

        log.audit({
            title: "JOB_STARTING",
            details: "Job has started."
        });

        const activeLineSearchQuery = `
            SELECT
                tranhistory.version,
                tranhistory.internalid,
                line.id as lineid
            FROM TransactionLine as line
            INNER JOIN (
                SELECT
                    hist.internalid,
                    MAX(hist.version) as version
                FROM TransactionHistory as hist
                WHERE hist.action='CHANGE'
                GROUP BY hist.internalid
            ) as tranhistory ON line.transaction=tranhistory.internalid
            LEFT JOIN customrecord_rs_trans_active_lines as activelines
                ON tranhistory.internalid=activelines.custrecord_rs_transaction_id
                AND tranhistory.version=activelines.custrecord_rs_transaction_version
                AND line.id = activelines.custrecord_rs_line_number
            WHERE line.id != 0
            AND activelines.recordid IS NULL
        `;

        return {
            type: 'suiteql',
            query: activeLineSearchQuery
        };

    }
s
how many results does the query return? in many environments, running a search on transactionLines without a date or type filter would not be considered a “small” dataset, though it could be. does it change anything if you add a filter, like 
line.transaction = <some transaction id>
?
e
Yes in this case it’s about 16 results
I’ll give that a shot next and see if that changes anything
m
I've generally had much better performance retrieving search/query results myself
Copy code
/**
   * Helper function to get all results from a SuiteQL query
   *
   * @param {string} sql The SuiteQL query to run
   * Code based on <https://timdietrich.me/blog/netsuite-suiteql-mapreduce-script/>
   *
   * @returns {*[]} The results of the query
   */
  exports.getSuiteQLResults = (sql) => {
    const PAGE_SIZE = 5000;

    let rows = [];
    let paginatedRowBegin = 1;
    let moreRows = true;

    do {
      const paginatedSQL = `SELECT * FROM ( SELECT ROWNUM AS ROWNUMBER, * FROM (${sql} ) ) WHERE ( ROWNUMBER BETWEEN ${paginatedRowBegin} AND ${PAGE_SIZE})`;

      const queryResults = query.runSuiteQL({ query: paginatedSQL }).asMappedResults();

      rows = rows.concat(queryResults);

      if (queryResults.length < PAGE_SIZE) {
        moreRows = false;
      }

      paginatedRowBegin = paginatedRowBegin + 5000;
    } while (moreRows);

    return rows;
  }
👍 1
9 Views
Open in Slack
PreviousNext
Powered by