Proc SQL

Quand les "Hash Objects" sauvent une jointure SQL complexe

Simon 29/08/2023 2 views

Dans le monde de la programmation SAS^©, un débat persiste : qui est le plus performant entre PROC SQL et l'étape DATA ? La réponse classique est "ça dépend". Cependant, il existe des scénarios spécifiques où les performances de SQL s'effondrent littéralement, passant de quelques minutes à plusieurs heures, voire au plantage de la session.

L'un de ces scénarios critiques survient lors de jointures impliquant des conditions complexes, notamment l'utilisation de OR combinée à des transformations de variables à la volée dans la clause ON.

Cet article explore comment transformer une requête SQL inefficace en une étape Data ultra-rapide grâce à l'utilisation des Tables de Hachage (Hash Objects).

Le problème : Les jointures SQL "gourmandes"

Imaginons le scénario suivant, fréquent en traitement de données bancaires ou administratives :

Vous devez joindre deux tables de taille moyenne (par exemple, 500 000 lignes et 300 000 lignes). Le défi réside dans le fait que les clés de jointure ne sont pas directes. Les identifiants sont stockés sous forme numérique, et la correspondance nécessite de convertir ces nombres en chaînes de caractères, puis d'extraire des sous-chaînes spécifiques. Pire encore, la condition de jointure contient un OR, signifiant qu'une correspondance peut survenir de deux manières différentes.

Voici à quoi ressemble le goulot d'étranglement en SQL :

1	PROC SQL noprint;
2	CREATE TABLE Resultat AS
3	SELECT DISTINCT
4	T1.ID_Parent_L,
5	T2.CodeCedant,
6	T2.ID_Client,
7	T1.Nom
8	FROM TABLE_PRINCIPALE T1
9	INNER JOIN TABLE_SECONDAIRE T2
10	/* La source du problème : conversions et condition OR */
11	ON (substr(put(T1.ID_Parent_L,14.),7,8) = substr(put(T2.ID_Client,14.),1,8)
12	OR substr(put(T1.ID_Parent_L,14.),1,8) = substr(put(T2.ID_Client,14.),1,8))
13	;
14	QUIT;

Pourquoi est-ce si lent ?

Transfo à la volée (PUT et SUBSTR) : SAS^© doit effectuer ces conversions de type (numérique vers caractère) et ces extractions pour chaque combinaison potentielle de lignes avant de pouvoir les comparer.
La clause OR : L'utilisation de OR dans une jointure empêche souvent l'optimiseur SQL d'utiliser efficacement les index. Cela force souvent le système à effectuer un "produit cartésien" partiel ou total, comparant chaque ligne de la table T1 avec une grande partie des lignes de T2.
Absence de clés uniques : Si les clés de jointure ne sont pas uniques dans les deux tables, la complexité augmente exponentiellement.

Dans ce genre de situation, une jointure MERGE classique en étape Data est également difficile à mettre en œuvre car elle nécessite un tri préalable sur des clés transformées. C'est ici qu'interviennent les objets de hachage.

La Solution : L'étape Data et les Hash Objects

Les objets de hachage (Hash Objects) permettent de charger une table entière (généralement la plus petite des deux) en mémoire vive (RAM). Cela crée une structure de recherche ultra-rapide où l'on peut trouver une correspondance instantanément sans avoir besoin de trier les données.

La stratégie consiste à :

Préparer la table de référence (la plus petite) en créant une clé de recherche propre.
Charger cette table dans un objet de hachage en mémoire.
Lire la grande table ligne par ligne, calculer la clé de recherche à la volée, et interroger l'objet de hachage.

Étape 1 : Préparation des données de référence

Au lieu de faire des calculs complexes dans la jointure, nous préparons la table secondaire (TABLE_SECONDAIRE) pour qu'elle contienne une clé de recherche (KEY) prête à l'emploi correspondant à la partie de l'identifiant que nous cherchons.

1	PROC SQL;
2	CREATE TABLE HashData AS
3	SELECT UNIQUE
4	CodeCedant,
5	ID_Client,
6	/* Création de la clé de jointure standardisée */
7	put(put(ID_Client,14.),$8.) AS KEY
8	FROM TABLE_SECONDAIRE;
9	QUIT;

Étape 2 : L'étape Data avec Hash Object

Voici le code qui remplace la jointure SQL complexe.

1	DATA Resultat_Hash;
2	/* On lit la table principale ligne par ligne */
3	SET TABLE_PRINCIPALE (keep=ID_Parent_L Nom);
4
5	/* Initialisation de l'objet de hachage (une seule fois au début) */
6	IF _N_=1 THEN DO;
7	/* Déclaration : on charge la table préparée en mémoire */
8	dcl hash H(dataset:'HashData');
9	/* Définition de la clé de recherche */
10	H.definekey('KEY');
11	/* Définition des données à récupérer en cas de succès */
12	H.definedata('CodeCedant','ID_Client');
13	H.definedone();
14	/* Astuce pour éviter de lire HashData ici, mais initialiser les variables */
15	IF 0 THEN SET HashData;
16	END;
17
18	/* --- Cœur du traitement --- */
19
20	/* Tentative 1 : On calcule la clé potentielle selon la première logique */
21	RC = H.find(key: put(put(ID_Parent_L,14.),$8.));
22
23	/* Si RC (Code Retour) = 0, la recherche a réussi.
24	Les variables CodeCedant et ID_Client sont remplies automatiquement. */
25	IF RC=0 THEN OUTPUT;
26	ELSE DO;
27	/* Optionnel : Si votre logique SQL initiale avait un "OR",
28	vous pouvez tenter une deuxième recherche ici si la première échoue.
29	Exemple :
30	RC = H.find(key: substr(put(ID_Parent_L,14.),7,8));
31	if RC=0 then output;
32	*/
33	END;
34
35	drop RC;
36	RUN;

Le gain de performance lors du passage d'une jointure SQL complexe avec des OR et des conversions vers une solution basée sur des Hash Objects est souvent spectaculaire. Des traitements qui prenaient des heures ou plantaient le serveur peuvent s'exécuter en quelques secondes.

Bien que la syntaxe des objets de hachage soit plus technique, c'est un outil indispensable dans l'arsenal du développeur SAS^© confronté à des volumes de données importants et des logiques de rapprochement non standard.

Important Disclaimer

The codes and examples provided on WeAreCAS.eu are for educational purposes. It is imperative not to blindly copy-paste them into your production environments. The best approach is to understand the logic before applying it. We strongly recommend testing these scripts in a test environment (Sandbox/Dev). WeAreCAS accepts no responsibility for any impact or data loss on your systems.

Back to article list

Dans : Proc SQL

Comment extraire une "sous-chaîne" d'une variable numérique ?

29/04/2021 • 2

Comment filtrer une table à partir d'une liste d'identifiants avec PROC SQL

26/02/2020 • 8

Le piège du "Many-to-Many" (MERGE vs SQL)

24/08/2019 • 1

View full category

References & Docs

étape DATA PROC SQL session PUT

SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration. WeAreCAS is an independent community site and is not affiliated with SAS Institute Inc.

Table of Contents