1

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"}
4

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.

|improve this answer|||||
  • For the DataExtension.Init("LOG_DATAEXTENSION"); i still have to insert Platform.Load("Core","1"); right? Just add the line before that call? Or do i have to rewrite the Log statements because i should not use Platform.Load("Core","1"); – Johannes Schapdick Sep 6 '18 at 14:58
  • 1
    @JohannesSchapdick - try removing the empty filter part from the retrieve. That was added on to show the ability to filter if needed, and since it is not a null value or a valid filter, it might be throwing off the Script. "security descriptors' usually means something is wrong with the parameters of the function, so if that doesn't work, I would troubleshoot each of the functions inside your script. – Gortonington Sep 10 '18 at 12:11
  • 1
    I would do 2 things: 1.) Stringify and write out your response (data) and verify what you are requesting is inside of there. If they are empty in the response, I would verify the column names as it would appear they are not mapping to the DE correctly. 2.) Verify the path for Attribute1,2,3, etc are correctly defined for the vars that are holding them. – Gortonington Sep 10 '18 at 12:43
  • 1
    Updated your answer to my final result. Hope that is ok for you. Thank you again for your amazing help. Do you know if there is any document about the parameters of the function WSProxy.retrieve? – Johannes Schapdick Sep 10 '18 at 13:57
  • 1
    Here is a resent presentation I did on WSProxy (github.com/sfmcdg/awesome-salesforce-marketingcloud/blob/master/…) and here is the docs from SFMC (developer.salesforce.com/docs/…) – Gortonington Sep 10 '18 at 14:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.