1

We have a data extension containing two columns:

  • subscriber_key (email address)
  • url_link

We load up this data extension with new subscribers and then iterate through and update the url_link using the CloudPagesURL() function for the new subscribers.

We have code that is working, but I fear it isn't optimized:

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

    try{
        var targetDE = DataExtension.Init("<ExternalID>");
        // TODO: is there a way to retreive N records at a time? E.g. TOP 100
        var filter = {Property:"url_link",SimpleOperator:"equals",Value:"na"};
        var target = targetDE.Rows.Retrieve(filter);

        do {
            // TODO: change for loop page size; 1999 was chosen arbitrarily to prevent timeout or memory issues
            for (var p = 0; p < 1999; p++)
            {
                var subscriber_key = target[p].subscriber_key;

                var url_link = Platform.Function.TreatAsContent("%"+"%"+"=CloudPagesURL"+"(3300,'subscriber_key',\""+subscriber_key+"\")="+"%"+"%");

                var rows = Platform.Function.UpsertData("MY_DATA_EXTENSION",["subscriber_key"],[subscriber_key],["url_link"],[url_link]);
            }

            var target = targetDE.Rows.Retrieve(filter);
        }
        while (target.length > 0)
    }

    catch(e)
    {
        // log error to log data extension...
    }
</script>

The two optimizations I would like to do are:

  1. Adjust the filter to only return a page at a time. For example, just do a "TOP 100". I didn't see anything n the Rows.Retrieve documentation. Do I need to worry about this step? Perhaps the data set isn't loaded in memory?
  2. Change the for loop limit to the page size or number of rows returned. (Looks like I can probably use target.length once I'm able to set the page size in the filter)

Thank you!

1
  • 1
    If the filter is just an 'equals', why not use the Platform function of LookupOrderedRows or the AMPScript version of it? Both of these allow you to limit the number of returned rows. Using Rows.Retrieve is utilizing the Core library which can lower processing efficiency and is basically a 'shorthand baked in function' to use the SOAP API. The benefit to it is it allows you to use more simple operators (LIKE, CONTAINS, etc.) and more complex filtering capabilities - which it doesn't seem like you need. – Gortonington Oct 8 '20 at 21:17
2

1. SSJS

Rows.Retrieve() loads 2500 rows at a time, and it looks like there is no option to limit the response size
I would iterate from 0 to target.length to avoid IndexOutOfBoundsException
But, in any way, this approach is not very effective

1.1 SSJS + _CustomObjectKey hack

You can use _CustomObjectKey in the filter as an index surrogate
In SOAP it looks like this:

<Filter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="SimpleFilterPart">          
    <Property>_CustomObjectKey</Property>
    <SimpleOperator>greaterThan</SimpleOperator>
    <Value>42</Value>
</Filter>

_CustomObjectKey is an identity primary key - take into account that there might be gaps if some rows were deleted

You may even get several empty pages while there are lots of data starting from higher values of _CustomObjectKey
You need to count all retrieved rows and check with DataExtensionRowCount() to detect the end of the loop
An error will lead you to the infinite loop

2. SOAP

Soap API supports pagination
You can iterate over small pages of rows. I'm not sure that SOAP SDK's support pagination well, at least Java FuelSDK has very primitive pagination support

You can try to use SOAP over WSProxy in SSJS
It should support all the advantages of SOAP

3. REST

You can retrieve DataExtension rows with an undocumented REST endpoint

GET /data/v1/customobjectdata/key/<DE-Key>/rowset

It supports standard pagination and filtering parameters

You can update DataExtension rows asynchronously
This is a bulk update, so you just need to retrieve all rows, change data and submit upsert request for all of them

6
  • Thank you @DenisKurochkin ! This isn't my normal area of focus, so I'm not sure if I want to dive a level deeper into SOAP or REST calls. I was able to work out a solution creating batches while loading the data extension. I'll document that in another answer. – Josh Jay Oct 7 '20 at 19:23
  • @JoshJay see one more trick with _CustomObjectKey – Denis Kurochkin Oct 8 '20 at 12:36
  • interesting! How do you think each method would rank performance-wise? I would think Server-Side JavaScript would be performant since the data and processing all stay on Marketing Cloud's side? – Josh Jay Oct 8 '20 at 12:47
  • 1
    With _CustomObjectKey you don't need to inject additional field into DE. But if you have many gaps in identifiers it might take more iteration – Denis Kurochkin Oct 8 '20 at 12:53
  • 1
    In any way, I would recommend investing your time in learning REST, or even SOAP - this will broaden your "toolkit" and significantly increase your rank as an engineer – Denis Kurochkin Oct 8 '20 at 12:59
0

Perhaps Denis's answer would be the normal route for a Marketing Cloud developer, so please check that out. I was hoping there was a simple filter option, but I was able to figure out a different solution by generating batch numbers on the load from the SQL statement. I'm much more comfortable with SQL than I am with JavaScript and AMPScript, so the old adage of "use the tools you know" applies here. I've marked this as the answer, since it solved my problem, but Denis's answer might be the "right" answer.

SQL Statement that Loads the Data Extension

select
     ceiling(row_number() over(order by s.email) / 2000.0) as BatchNumber
    ,s.email as subscriber_key 
    ,'na' as pcURL
from
    Source_Data_Extension s
where
    not exists (
        select 1
        from Processed_Data_Extension e
        where e.email = s.email
    )

This required me to add "BatchNumber" to the data extension. The "2000.0" is the desired batch/page size. The ".0" forces SQL to treat it as a decimal instead of an int. An int value causes issues with the ceiling function. BatchNumber would start at 0 (int) instead of the desired 1 (decimal).

Updated Server-Side JavaScript

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

    var batchNumber = 1;
    var pageSize = 1;

    try{
        
        do {
            var targetDE = DataExtension.Init("<ExternalID>");
            var filter1 = {Property:"url_link",SimpleOperator:"equals",Value:"na"};
            var filter2 = {Property:"BatchNumber",SimpleOperator:"equals",Value: batchNumber};
            var filter = {LeftOperand: filter1, LogicalOperator: "AND", RightOperand: filter2};
            var target = targetDE.Rows.Retrieve(filter);

            pageSize = target.length;

            for (var p = 0; p < pageSize; p++)
            {    
              var subscriber_key = target[p].subscriber_key;
              
              var url_link = Platform.Function.TreatAsContent("%"+"%"+"=CloudPagesURL"+"(3300,'subscriber_key',\""+subscriber_key+"\")="+"%"+"%");
              
              var rows = Platform.Function.UpsertData("MY_DATA_EXTENSION",["subscriber_key"],[subscriber_key],["url_link"],[url_link]);
            } 
            
            batchNumber++;
        }
        while (pageSize > 0)
    }

    catch(e)
    {
        // log error to log data extension...
    }
</script>

I've added a second filter for the batch number and the do while loop continues until there are no records returned.

2
  • sounds like "don't try to do this at home" :) – Denis Kurochkin Oct 8 '20 at 12:18
  • haha, or "you're mileage may vary"! The SQL batch number calc is a great one to have int the toolbox, and this seems to be working very well so far. – Josh Jay Oct 8 '20 at 12:51

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.