1

I am unable to put the comment in the original thread where Adam has put up the answer. I have modified the code suit to my scenario and it's able to delete 50 records, but I am unable to delete bulks records like what Adam mentioned that he could delete 300million records back then. Even I put in while..loop function, but I am not able to achieve the same result. Can anyone help on this?

Original thread: Bulk delete data extension rows

<script runat="server">

Platform.Load("core", "1.1.1");

function pruneRows () {

  var DERowKeys = DataExtension.Init("DEofRowKeys");
  var DERowKeyRows = DERowKeys.Rows.Lookup(["ProcessedFlag"], [0], 50, "RowDate");

  var returnString = "";
  var totalDelCount = 0;

  for (var i in DERowKeyRows) {

     var RowKey = DERowKeyRows[i].RowKey;
     var RowDate = DERowKeyRows[i].RowDate;

     returnString += "<br>" + RowKey;
     returnString += ", " + RowDate;

     var delCount = 0;
     var action = "";
     var begin = (new Date()).getTime();

     var sl = DataExtension.Init("DataExtensionToPrune");

     try {

       delCount += sl.Rows.Remove(["RowKey"], [RowKey]);
       totalDelCount += delCount;
       action = "delete";           
       returnString += ", " + delCount;

     } catch (e) {

       action = "error";

     }

     var end = (new Date()).getTime();
     var duration = (end-begin).toString() + 'ms ';

     // update row as processed
     if (action != "error") {

        var updateCount = DERowKeys.Rows.Update({"ProcessedFlag":1,"RowsDeleted":delCount, "Duration":duration, "Action":action}, ["RowKey"], [RowKey]);

     }

  }

  returnString += "<br>total deleted in batch: " + totalDelCount;

  return returnString;
}

</script>
3

1 Answer 1

1

Your lookup is setting a return limit of 50

var DERowKeyRows = DERowKeys.Rows.Lookup(["ProcessedFlag"], [0], 50, "RowDate");

The third parameter (50) ID setting the max return.

As a note i believe the default return is maxxed at 2500 records per lookup/return so to accomplish above this in SSJS you need to use WSPROXY or raw SOAP calls to utilize continueRequest. See here for example with WSPROXY

2
  • Yes, deletion of 50 records per batch is intended as per Adam's suggestion to avoid Cloudpage time out. But I am not sure how to continue the next page after the first page of 50 records processed. Thanks for the WSProxy recommendation. Is it possible Wsproxy can delete bulk records (say about 1 million record)?
    – kelvid
    Mar 27, 2022 at 6:16
  • @kelvid - I wouldn't run this on a Cloudpage - do it in a Script Activity for anything bulk. If you notice in the example link I shared it has the full process on how to do a continue Request in WSProxy. And yes, you can do millions of rows (in the right context) with WSProxy. It is actually (in most cases) more performant than the Platform Lookup Functions. Mar 27, 2022 at 14:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .