Slow SQL in Marketing Cloud: staying under the 30 minute limit

A sports equipment retailer prepares the audience for its promotional newsletter every morning. An automation starting at 06:00 runs a single query, written when the programme launched two years earlier: French or Belgian customers, having ordered in the last 90 days, with no recent bounce. Back then it ran in four minutes.

Since then the orders table has grown sixfold, and the same query has been copied into three other automations, also scheduled at 06:00. One Monday, the activity stops after 30 minutes. The send, scheduled separately for 08:00, goes out anyway: it reads the target data extension, which still holds Friday's population.

In the test environment, on a reduced copy of the data, the query runs in a minute. Nothing in the code is wrong. Its shape no longer holds the volume.

SELECT DISTINCT
    c.ClientId,
    c.Email,
    c.Prenom
FROM Clients AS c
INNER JOIN Commandes AS co
    ON co.ClientId = c.ClientId
WHERE (c.Pays = 'FR' OR c.Pays = 'BE')
  AND DATEDIFF(day, co.DateCommande, GETDATE()) <= 90
  AND UPPER(c.Email) NOT LIKE '%@EXAMPLE.ORG'
  AND NOT EXISTS (
      SELECT 1
      FROM _Bounce AS b
      WHERE b.SubscriberKey = c.ClientId
        AND DATEDIFF(day, b.EventDate, GETDATE()) <= 30
  )

Thirty minutes, and nothing to see it coming

A Marketing Cloud Engagement query has no readable execution plan and no index you can create yourself. It does have a maximum duration of 30 minutes, beyond which the activity is stopped. While volumes stay modest, none of this shows.

The query behind the incident breaks none of these thresholds on paper: two data extensions, one data view, one join. It fails anyway, because the volume processed and the shape of the conditions weigh more than the number of tables. The five minute target remains the best marker, since a query that holds at fifteen minutes in a normal week will pass 30 minutes the day the platform is busy.

What the SQL dialect does not support

The dialect is based on SQL Server without matching it exactly. Variables, cursors, temporary tables and common table expressions with WITH are not supported, and neither are double dash end of line comments. The splits described below therefore go through real data extensions, and comments are written between /* and */.

Write conditions the engine can resolve

You do not create indexes in Marketing Cloud. Salesforce states that the platform automatically indexes primary keys, send relationship fields and the most used fields, and flags a few indexed columns on the data views. Your lever is to write conditions the engine can resolve with those indexes.

Non indexable arguments in a WHERE clause

The Salesforce optimisation page asks you to avoid, inside a WHERE, the arguments it calls non indexable: OR, NOT, NOT EXISTS, NOT IN, NOT LIKE, along with functions applied to a column value. The same page nonetheless rewrites a DATEDIFF as a bounded interval. What costs is the function wrapping the column, not the comparison on a bare column.

The LEFT JOIN ... IS NULL is not spelled out by Salesforce. It rests on an equality join with a small table whose primary key is indexed, which makes it plausible, but measure it on your data before turning it into a rule.

The parameter table has a quiet advantage: Pays_Cibles holds two rows, FR and BE, and adding Switzerland will no longer mean reopening the query and putting it through validation again.

Split rather than optimise

Before rewriting anything, split the query and run each piece on its own, in a test activity that writes to a throwaway data extension. Compare the number of rows in Commandes over 90 days with the number of distinct customers: if the ratio is ten to one, the original query built ten rows per customer before DISTINCT removed nine. The longest piece names your priority.

Intermediate tables in Overwrite mode

Salesforce then recommends breaking multi join queries into smaller queries that write to intermediate tables, then consolidating with a final query. Each expensive subset is worked out once, in Overwrite mode, and keeps a single row per customer.

/* Tmp_Acheteurs_90j, ClientId primary key, Overwrite mode */
SELECT
    co.ClientId,
    MAX(co.DateCommande) AS DerniereCommande
FROM Commandes AS co
WHERE co.DateCommande >= DATEADD(day, -90, CAST(GETDATE() AS DATE))
GROUP BY co.ClientId

The GROUP BY removes the need for DISTINCT in the final query. Salesforce recommends keeping SELECT DISTINCT for cases where duplicates genuinely exist in the data, rather than using it to patch a join that is too wide. The recent bounces table is prepared the same way, with SubscriberKey as primary key.

The final query, joins on keys only

The final query then handles only reduced tables, linked by their keys.

/* Cible_Newsletter, ClientId primary key, Overwrite mode */
SELECT
    c.ClientId,
    c.Email,
    c.Prenom
FROM Clients AS c
INNER JOIN Pays_Cibles AS p
    ON p.Pays = c.Pays
INNER JOIN Tmp_Acheteurs_90j AS a
    ON a.ClientId = c.ClientId
LEFT JOIN Tmp_Bounces_30j AS b
    ON b.SubscriberKey = c.ClientId
WHERE c.Email IS NOT NULL
  AND b.SubscriberKey IS NULL

Three joins, all on key equalities, within the Salesforce recommendation. The filter on the address domain has gone: a leading wildcard cannot be sped up, and if it genuinely matters to you it becomes a column worked out at import. Dates are expressed in server time, like EventDate and GETDATE(); if your order dates are in Paris time, the bounds shift by a few hours.

One query per step

The three queries become three successive steps of the same automation, in dependency order, followed by the send. Salesforce recommends one query per step, and staggering automations across quieter hours: 05:40 rather than 06:00.

1. step 1, Tmp_Acheteurs_90j in Overwrite, the heaviest, measured on its own;
2. step 2, Tmp_Bounces_30j in Overwrite, fast and low volume;
3. step 3, Cible_Newsletter in Overwrite, joins on keys only;
4. step 4, a check on the volume obtained, to stop if the audience is empty or abnormal;
5. step 5, the send, inside the same automation rather than scheduled separately.

Bringing the send back into the automation

Bringing the send back into the automation removes the direct cause of the incident: it is no longer fired by a clock independent of the calculation that feeds it. Four overwrites stay under the limit of five per automation. As for the three automations that copied the query, better to have them read Cible_Newsletter than repeat the same work three times.

If you are coming from Adobe Campaign

In Adobe Campaign Classic, a query activity generates SQL you can read in the logs, and you declare your indexes in the schema. Nothing of the sort here: no execution plan, no index on demand, and the only measurement available is the run duration recorded after the fact.

The work tables a Campaign workflow creates and purges by itself between two activities do not exist either. Their equivalent is an intermediate data extension that you create, name and overwrite yourself on every run. That is more plumbing, and it is also the only way to keep control of what the engine processes at each step.

The habit worth keeping

Record the run durations before touching the SQL. If a query takes twelve minutes today, it does not need optimising: it needs splitting, because the volume that will bring it down is already on its way.

Frequently asked questions