SSJS script is timing out at 30 minutes. How to I build series of scripts using rownum to parse the DE sequentially?

Question

We have an SSJS script that is calling an API to fetch price of items and writing it into a DE. The API call is taking time and in 30minutes the script is only able to retrieve the price for ~6000 records.

As a work around we have added a column to our DE (source and destination are the same. We are just fetching price and updating the same DE) called RowNum. Now our plan is to write a series of script activities to parse the data.

For example - First script should fetch the price for rownum 1-5000. Second script should fetch the price for rownum 5001-10000 and so on.

Now SSJS already has a limitation of fetching only 2500 per batch and we are using WS Proxy to run multiple batches. So our current code is given below. How should we optimize this code to make sure it is fetching data on basis of row number as defined above:

< script runat = "server" >
Platform.Load("core", "1.1.1");

    var prox = new Script.Util.WSProxy();
    var moreData = true;
      
    var srcDE = "DataExtensionObject[ABC]";
    var cols = ['SubscriberKey','timestamp','A','B','C','D','E'];
    var postDE = DataExtension.Init("XYZ");

    while(moreData) {
        var data = reqID == null ? prox.retrieve(srcDE, cols) : prox.getNextBatch(srcDE, reqID);

        if(data != null) {
            moreData = data.HasMoreRows;
            reqID = data.RequestID;
            var array = [];
            if(data && data.Results) {
                for(var i=0; i< data.Results.length; i++) {
                var row = data.Results[i].Properties;

After that we are calling the API and writing it into the DE.

Answer 1

Suggestion is to use batching / chunks.

Use two alternating activities that "call each other" after a limit per activity has been reached. Each activity is one loop iteration. Each iteration processes one batch. Limit could be processed rows, or time, or both.

fundamentals:

automations that call each other

schema here: Account Activity or Object Activity in Journey Builder has limits to update like Data Loader?

which also references this how-to.

https://sprignaturemoves.com/retrieving-and-starting-an-automation-with-ssjs/

Downside: each automation run costs you a run - but if you optimize this sufficiently, this can become negligible and it is a stable setup.

retrieving more than 2500 records in a while loop

https://ampscript.xyz/how-tos/how-to-retrieve-more-than-2500-records-from-a-data-extension-with-server-side-javascript/

give an id to each loop iteration

This is largely a logging exercise. You are constructing a loop of activities. The element with the the 30 minute timeout is the activity. So you will need to monitor by activity -> start by giving each run an id.

//for each loop iteration, assign a runId

var runId = Platform.Function.GUID()

Now each script execution has an ID that you can log to and thus, monitor.

logging processed records

Way easier than "numbering rows" in the source DE is this: set up a data extension that holds "records which have been processed". Simply copy your source DE and name that copy "logfile". In your procedure, as a last step, store each processed record there.

Now it will be pretty easy to find "nonprocessed records", even using SQL. ("select records in source that that are not (yet) in log DE") and you do not have to come up with a way of artificially numbering them.

Plus, you automatically have a logfile of finished rows now. Ideally, you also have the runId stored in log, so you know: run X has processed N records. Worst case, You will see in your log exactly what worked and after how many records it stopped. So in case of timeout that also makes it easy to "restart where the loop broke".

General workflow:

• Retrieve batches of 2500 records that are in source DE and not in log DE.
• Potentially decrease or increase the number 2500 (with a while loop) to [yourMaxNumber].
• Process as desired.
• Finally, each processed record is added to log so it is not attempted twice.

==

Nobody wants timeouts though - [yourMaxNumber] is a "guess", and you could either "underestimate" and needlessly stop a loop iteration (i.e. you could do way more records in 30 minutes), or you "overestimate" what can be done in 30 minutes, and anyway run into a timeout, as time is not being controlled for. Of course, you could already have some experience like you alluded to. You say 6k take 30 minutes, so you can of course go with that.

Alternatively, you could also monitor for time - That way you can optimize towards the 30min timeout, or combine checks for both rows and time.

logging execution time

If you want to do a timebased decision, I would use a timer that starts when the code begins. Note that this happens at the start of each loop and is thus logged to the runId.

Set up another log de, with the fields runId (PK), key (PK), value. Here, I just make them all text, but you can make them dates if you want to work with the date later somehow. I just use this as a log for humans to monitor what happens.

//startOfLoop Logging
// Start the timer
var startTime = new Date();

Platform.Function.UpsertData(someLogDE
 ,["runId","key"]
 ,[runId,"startTime"]
 ,["value"]
 ,[Stringify(startTime)]
)

-> logs the start time of your loop iteration (denoted by a runId) into a DE.

// endOfLoop logging.
// End the timer
var endTime = new Date();

// Calculate the time difference in seconds
var executionTime = (endTime - startTime) / 1000;
var executionTimeByRecord = executionTime / i
//Write("executionTime In seconds: " + Stringify(executionTime)+ "\r\n"+ "\r\n");
//Write("executionTimeByRecord in seconds: " + Stringify(executionTimeByRecord) + "\r\n"+ "\r\n");


Platform.Function.UpsertData(someLogDE
    ,["runId","key"]
    ,[runId,"executionTimeInSeconds"]
    ,["value"]
    ,[Stringify(executionTime)]
)


Platform.Function.UpsertData(bulkProcessingLogDE
    ,["runId","key"]
    ,[runId,"executionTimeByRecordInSeconds"]
    ,["value"]
    ,[Stringify(executionTimeByRecord)]
)

--> counts seconds since code started, logs the processing time of one runId (total, as well as by record) into the DE.

Now you have a table that tells you how long e.g. one set of 2500 records takes, and how long one row takes in this procedure.

That way you can monitor and optimize how much you cram into one iteration. Your log will tell you e.g. 2500 records take 1 minute, and one individual record thus takes 1/2500 minute.

So you no longer need to guess [maxNumberOfRecords].

stopping an iteration automatically once close to 30 mins

If you really want to technically ensure that each loop is prevented from timing out: after processing each record, check if (startTime + executionTimeInSeconds) is lower than, say, 28 minutes (28*60 seconds). once it reaches that, you're close to timeout. Stop looping, call the other automation. If not, you can safely keep going with another record.