3

I am using the C# Fuel SDK for the deletion of rows in a data extension, am I am trying to delete large(ish) volumes of records at once.

The sample code only provides details of how to delete records one-by-one. Deleting large volumes (hundreds to thousands) takes a very long time using this approach.

I have begun migrating my code to use the REST APIs, but according to the documentation, there are no supported methods for delete data extension records. (see link here: https://code.exacttarget.com/apis-sdks/rest-api/v1/hub/data-events/postDataExtensionRowsetByKey.html)

Is there anyway to bulk delete a large volume of records at once through the APIs or SOAP clients?

3

You have to loop and capture the RequestID from the Response to churn through the rows in blocks of 2500.

Here's the important part from a PHP implementation:

var_dump($results);

while ($results->OverallStatus=="MoreDataAvailable") {

    $rr = new ExactTarget_RetrieveRequest();
    $rr->ContinueRequest = $results->RequestID;
    $rrm = new ExactTarget_RetrieveRequestMsg();
    $rrm->RetrieveRequest = $rr;
    $results = null;
    $results = $client->Retrieve($rrm);
    $tempRequestID = $results->RequestID;
    print_r($results->OverallStatus.' : '.$results->RequestID.' : '.count($results->Results));                    

}

If you'd like to keep all of your code in SFMC, I've deleted 300 million rows from a DE using this SSJS in SFMC Landing Page that posts to itself. I utilized a DE of keys and deleted all of the rows from the target DE one key at a time. It took a while but it worked.

<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>
  • Sorry @Adam, but I dont really follow the flow here, what does the PHP code block actually do? How does this tie to the javascript you also posted? I think I just need more details on your approach. Thanks so far though. – Mark Apr 3 '16 at 10:48
  • The PHP is the execute loop of an API retrieve -- like the C# one you've described. It processes all of the rows, 2500 at a time. If there are more rows to retrieve, the status will contain MoreDataAvailable. The SSJS is an alternative that I've used if you don't want to maintain the code outside of the SFMC. Apologies if that was confusing. – Adam Spriggs Apr 3 '16 at 21:21
  • Thanks for the clarification Adam, however, my situation is less of "delete all records" and more "delete records with these keys", in your JS code, it looks like it deletes one-by-one correct? I think that is essentially what I have now, but its very slow... maybe half a second per record. So, Im still not sure if what you are giving me actually solves my problem? – Mark Apr 3 '16 at 22:04
  • The SSJS is not deleting one by one. Just one key at a time... Sendlog rows by JobId – Adam Spriggs Apr 3 '16 at 22:06
  • Yeah one key at a time, but isnt that painfully slow? I suppose that its actually a different method to what Im using, so I will give that a try – Mark Apr 3 '16 at 22:17

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.