How to improve this SSJS

Question

Recently started to learn SSJS and wrote the script (for automation studio) which is located below. What i am interested in is:

• How can i improve this Server Side JavaScript performance wise?
  • For example: The else branch is called every 2500 records. Is this good because you operate in smaller chunks or can you adapt this value to increase performance without the loss of stability?
• How to make it more fail-safe?
  • Try catch around everything or around particular calls?
  • Can i recover from some of these errors?
• Are there additional ressources Ressource1?
  • Any blog or something like that which covers other examples like the above ressource?

This script processes the the records of the Dataextension DATAEXTENSION_NAME and does a HTTP-Get call for every row and does some logging into DEBUG_DATAEXTENSION and writes the recordcount into LOG_DATAEXTENSION.

<script runat="server">
    Platform.Load("Core","1");
    // records processed equals r
    var r = 0
    var okCount;
    var timestamp;

    var debugDE=DataExtension.Init("DEBUG_DATAEXTENSION");

    var RetrieveRequest = Platform.Function.CreateObject("RetrieveRequest");

    Platform.Function.SetObjectProperty(RetrieveRequest,"ObjectType","DataExtensionObject[DATAEXTENSION_NAME]");
    Platform.Function.AddObjectArrayItem(RetrieveRequest,"Properties","Attribute1");
    Platform.Function.AddObjectArrayItem(RetrieveRequest,"Properties","Attribute2");
    Platform.Function.AddObjectArrayItem(RetrieveRequest,"Properties","Attribute3");
    Platform.Function.AddObjectArrayItem(RetrieveRequest,"Properties","Attribute4");

    var StatusAndRequestID=[0,0];

    var data = Platform.Function.InvokeRetrieve(RetrieveRequest,StatusAndRequestID);

    if(StatusAndRequestID[0]=="OK"){
        okCount=1;
    }

    do{
        if (data.length>0) {
            for (var i = 0; i < data.length; i++) {
                var Attribute1 = data[i].Properties[0].Value;
                var Attribute2 = data[i].Properties[1].Value;
                var Attribute3 = data[i].Properties[2].Value;
                var Attribute4 = data[i].Properties[3].Value;

                r++;
                // url has to be defined before this call
                var result = HTTP.GetURL(url);
                // and any other stuff
            }
        }
    if (okCount==1){
        okCount++;
    }
    else{
        timestamp = new Date();
        debugDE.Rows.Add({DebugMessage: r,Timestamp: timestamp,ImportType:"DebugScript"});

        Platform.Function.SetObjectProperty(RetrieveRequest,"ContinueRequest",StatusAndRequestID[1]);
        var data = Platform.Function.InvokeRetrieve(RetrieveRequest, StatusAndRequestID);
        if (StatusAndRequestID[0]=="OK"){
            okCount=1;
        }
    }
    } while(StatusAndRequestID[0]=="MoreDataAvailable" || okCount==1);

    var logDE = DataExtension.Init("LOG_DATAEXTENSION");
    var d = new Date();
    logDE.Rows.Add({TimeStamp: d,RecordsImported: r,ImportType:"DebugScript"});

</script>

Current stats for testing:

• DATAEXTENSION_NAME 500k records
• Average Run time (without HTTP.Get Calls) 15 minutes

Thanks for reading / helping.

---

---

---

Update

---

---

---

Current Version of the improved script with slight edits from my side (see answer below from @Gortonington) [which is not working since this point]

<script runat="server">
function Write(str) {
   Platform.Response.Write(str);
}

function Stringify(obj) {
   return Platform.Function.Stringify(obj);
}

try{

    var proxy = new Script.Util.WSProxy();
    var cols = ["Attribute1","Attribute2","Attribute3","Attribute4"];
    var filter = {}; //Insert Filter here if needed

    var moreData = true;
    var reqID = null;
    var data;
    var r=0;
    var props = {QueryAllAccounts: false};

    do {
        moreData = false; //in case we don't make it in to the data conditional
        if (reqID) {
            props['ContinueRequest'] = reqID;
        } //set ContinueRequest ID if we have requestID from a previous run

        data = proxy.retrieve("DataExtensionObject[DATAEXTENSION_NAME]", cols, filter);

        if(data != null) {
            moreData = data.HasMoreRows;
            reqID = data.RequestID;
            if(data && data.Results) {
                for (var i = 0; i < data['Results'].length; i++){
                    var Attribute1 = data['Results'][i]['Attribute1'];
                    var Attribute2 = data['Results'][i]['Attribute2'];
                    var Attribute3 = data['Results'][i]['Attribute3'];
                    var Attribute4 = data['Results'][i]['Attribute4'];

                    // did not display where to gather URL, so duplicating what you had
                    // var result = HTTP.GetURL(url);

                    r++;
                    Write("RecordNumber: "+Stringify(r)+"<br/>");
                    Write("Attribute1: "+Stringify(Attribute1)+" , Attribute2: "+Stringify(Attribute2)+" , Attribute3: "+Stringify(Attribute3)+" , Attribute4: "+Stringify(Attribute4));
                    Write("<br/>");
                }//for
            }//if
        }//if
    } while(moreData);

    var d = new Date();
    var rows = Platform.Function.InsertData("LOG_DATAEXTENSION",["TimeStamp","RecordsImported","ImportType"],[d,i,"DebugScript"]);
    Write("Complete state. Processed "+Stringify(r)+" rows.");

} catch (err) {
    Write(Stringify(err) + "</br>");
}

</script>

This script returns the following:

{"message":"Unable to retrieve security descriptor for this frame.","description":"System.InvalidOperationException: Unable to retrieve security descriptor for this frame. - from mscorlib\r\n\r\n"}

Answer 1

That is a ton of records to retrieve at once, especially with utilizing an HTTPGET on each one. I am not sure that it is possible to do it all in a single script.

Now that being said, I can help you optimize your Retrieve Request at least by using WSProxy instead of the SSJS API functions. This should greatly reduce that turn time, as well as reducing the code needed.

<script runat="server">
function Write(str) {
    Platform.Response.Write(str);
}
function Stringify(obj) {
    return Platform.Function.Stringify(obj);
}
try{
    var proxy = new Script.Util.WSProxy();
    var cols = ["Attribute1","Attribute2","Attribute3","Attribute4"];
    // var filter = {}; //Insert Filter here if needed
    var r = 0;
    var moreData = true;
    var reqID = null;
    var data;

do {
    moreData = false; //in case we don't make it in to the data conditional
    if (reqID) {props['ContinueRequest'] = reqID;} //set ContinueRequest ID if we have requestID from a previous run
    data = proxy.retrieve("DataExtensionObject[DATAEXTENSION_NAME]", cols);

    if(data != null) {
        moreData = data.HasMoreRows;
        reqID = data.RequestID;
        if(data && data.Results) {
            for (var i = 0; i < data['Results'].length; i++){
                var Attribute1 = data['Results'][i]['Properties'][0]['Value'];
                var Attribute2 = data['Results'][i]['Properties'][1]['Value'];
                var Attribute3 = data['Results'][i]['Properties'][2]['Value'];
                var Attribute4 = data['Results'][i]['Properties'][3]['Value'];

                // did not display where to gather URL, so duplicating what you had
                // var result = HTTP.GetURL(url);
                r++;
            }//for
        }//if
    }//if
} while(moreData);

    var d = new Date();
    var rows = Platform.Function.InsertData("LOG_DATAEXTENSION",["TimeStamp","RecordsImported","ImportType"],[d,i,"DebugScript"]);
} catch (err) {
    Platform.Function.InsertData("DEBUGGING",["DebugMessage","Timestamp","ImportType"],[err,d,"DebugScript"]);
}
</script>

I would look at if there is another solution outside requiring over 500k GETS to accomplish what you need. Your resource draw on SFMC may be too much and will slow down your entire instance. Plus you are unlikely to get this done within a single Script as it will Time out usually around the 30 minute mark - requiring multiple instances of this, increasing risk and complexity.