Requête SQL trop lente dans Marketing Cloud : rester sous les 30 minutes

Un site de vente d’articles de sport prépare chaque matin la cible de sa newsletter promotionnelle. Une automation lancée à 6 h 00 exécute une seule requête, écrite au lancement du programme deux ans plus tôt : clients français ou belges, ayant commandé dans les 90 derniers jours, sans bounce récent. À l’époque, elle tournait en quatre minutes.

Depuis, la table des commandes a été multipliée par six, et la même requête a été recopiée dans trois autres automations planifiées elles aussi à 6 h 00. Un lundi, l’activité s’arrête au bout de 30 minutes. L’envoi, planifié séparément à 8 h 00, part quand même : il lit la data extension cible, qui contient encore la population du vendredi.

En recette, sur une copie réduite des données, la requête passe en une minute. Rien dans le code n’est faux. C’est sa forme qui ne tient plus le 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
  )

Trente minutes, et rien pour voir venir

Une requête de Marketing Cloud Engagement n’a pas de plan d’exécution consultable et pas d’index que vous pouvez créer vous-même. Elle a en revanche une durée maximale de 30 minutes, au-delà de laquelle l’activité est interrompue. Tant que les volumes restent modestes, rien de tout cela ne se voit.

La requête de l’incident ne dépasse aucun de ces seuils sur le papier : deux data extensions, une data view, une jointure. Elle échoue quand même, parce que le volume traité et la forme des conditions pèsent davantage que le nombre de tables. La cible de cinq minutes reste le meilleur repère, puisqu’une requête qui tient quinze minutes en semaine normale dépassera 30 minutes le jour où la plateforme est chargée.

Ce que le dialecte SQL ne gere pas

Le dialecte est basé sur SQL Server sans lui correspondre exactement. Les variables, les curseurs, les tables temporaires et les expressions de table communes avec WITH ne sont pas pris en charge, pas plus que les commentaires de fin de ligne à deux tirets. Les découpages décrits plus bas passent donc par de vraies data extensions, et les commentaires s’écrivent entre /* et */.

Écrire des conditions que le moteur sait résoudre

Vous ne créez pas d’index dans Marketing Cloud. Salesforce indique que la plateforme indexe automatiquement les clés primaires, les champs de relation d’envoi et les champs les plus utilisés, et signale quelques colonnes indexées sur les data views. Votre levier consiste à écrire des conditions que le moteur peut résoudre avec ces index.

Les arguments non indexables d'un WHERE

La page d’optimisation de Salesforce demande d’éviter dans un WHERE les arguments qualifiés de non indexables : OR, NOT, NOT EXISTS, NOT IN, NOT LIKE, ainsi que les fonctions appliquées à la valeur d’une colonne. La même page réécrit pourtant un DATEDIFF en intervalle borné. Ce qui coûte, c’est la fonction qui enveloppe la colonne, pas la comparaison sur une colonne nue.

Le LEFT JOIN ... IS NULL n’est pas cité en toutes lettres par Salesforce. Il s’appuie sur une jointure d’égalité avec une petite table dont la clé primaire est indexée, ce qui le rend plausible, mais mesurez-le sur vos données avant d’en faire une règle.

La table de paramétrage a un avantage discret : Pays_Cibles contient deux lignes, FR et BE, et ajouter la Suisse ne demandera plus de rouvrir la requête ni de la revalider.

Découper plutôt qu’optimiser

Avant de réécrire quoi que ce soit, découpez la requête et exécutez chaque morceau seul, dans une activité de test qui écrit dans une data extension jetable. Comparez le nombre de lignes de Commandes sur 90 jours au nombre de clients distincts : si le rapport est de dix pour un, la requête d’origine fabriquait dix lignes par client avant que le DISTINCT n’en supprime neuf. Le morceau le plus long désigne la priorité.

Des tables intermediaires en mode Overwrite

Salesforce recommande ensuite de casser les requêtes à jointures multiples en requêtes plus petites qui écrivent dans des tables intermédiaires, puis de consolider avec une dernière requête. Chaque sous-ensemble coûteux se calcule une fois, en mode Overwrite, et ne garde qu’une ligne par client.

/* Tmp_Acheteurs_90j, clé primaire ClientId, mode Overwrite */
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

Le GROUP BY supprime le besoin de DISTINCT dans la requête finale. Salesforce recommande de réserver SELECT DISTINCT aux cas où les doublons existent réellement dans les données, plutôt que de s’en servir pour rattraper une jointure trop large. La table des bounces récents se prépare de la même façon, avec SubscriberKey pour clé primaire.

La requete finale, jointures sur cles

La requête finale ne manipule plus que des tables réduites, reliées par leurs clés.

/* Cible_Newsletter, clé primaire ClientId, mode Overwrite */
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

Trois jointures, toutes sur des égalités de clés, dans la recommandation de Salesforce. Le filtre sur le domaine de l’adresse a disparu : un joker en tête de motif ne s’accélère pas, et s’il vous est vraiment utile, il devient une colonne calculée à l’import. Les dates sont exprimées dans l’heure du serveur, comme EventDate et GETDATE() ; si vos dates de commande sont en heure de Paris, les bornes décalent de quelques heures.

Une requête par étape

Les trois requêtes deviennent trois étapes successives de la même automation, dans l’ordre des dépendances, puis vient l’envoi. Salesforce recommande de ne mettre qu’une requête par étape et de décaler les automations entre elles, sur des heures moins demandées : 5 h 40 plutôt que 6 h 00.

1. étape 1, Tmp_Acheteurs_90j en Overwrite, la plus lourde, mesurée à part ;
2. étape 2, Tmp_Bounces_30j en Overwrite, rapide et de faible volume ;
3. étape 3, Cible_Newsletter en Overwrite, jointures sur clés uniquement ;
4. étape 4, contrôle du volume obtenu, pour arrêter si la cible est vide ou anormale ;
5. étape 5, l’envoi, dans la même automation plutôt que planifié à part.

Ramener l'envoi dans la meme automation

Ramener l’envoi dans l’automation supprime la cause directe de l’incident : il n’est plus déclenché par une horloge indépendante du calcul qui l’alimente. Quatre écrasements restent sous le seuil de cinq par automation. Quant aux trois automations qui recopiaient la requête, le mieux est de les faire lire Cible_Newsletter au lieu de refaire le même calcul trois fois.

Si vous venez d’Adobe Campaign

Dans Adobe Campaign Classic, une activité de requête génère un SQL que vous pouvez lire dans les journaux, et vous déclarez vos index dans le schéma. Rien de tel ici : pas de plan d’exécution, pas d’index à la demande, et la seule mesure disponible est la durée d’exécution relevée après coup.

Les tables de travail qu’un workflow Campaign crée et purge tout seul entre deux activités n’existent pas non plus. Leur équivalent est une data extension intermédiaire que vous créez, nommez et écrasez vous-même à chaque exécution. C’est plus de plomberie, mais c’est aussi la seule façon de garder la main sur ce que le moteur traite à chaque étape.

Le réflexe à garder

Relevez les durées d’exécution avant de toucher au SQL. Si une requête tourne en douze minutes aujourd’hui, elle n’a pas besoin d’être optimisée : elle a besoin d’être découpée, parce que le volume qui la fera tomber est déjà en train d’arriver.

Questions fréquentes