Proc SQL

Wenn "Hash-Objekte" einen komplexen SQL-Join retten

Simon 29/08/2023 2 views

In der Welt der SAS^©-Programmierung gibt es eine andauernde Debatte: Was ist leistungsfähiger, PROC SQL oder der DATA-Schritt? Die klassische Antwort lautet: „Es kommt darauf an“. Es gibt jedoch spezifische Szenarien, in denen die Leistung von SQL buchstäblich einbricht und von wenigen Minuten auf mehrere Stunden ansteigt oder die Sitzung sogar zum Absturz bringt.

Eines dieser kritischen Szenarien tritt bei Joins auf, die komplexe Bedingungen beinhalten, insbesondere die Verwendung von OR in Kombination mit on-the-fly-Variablentransformationen in der ON-Klausel.

Dieser Artikel untersucht, wie man eine ineffiziente SQL-Abfrage mithilfe von Hash-Tabellen (Hash Objects) in einen ultraschnellen Data-Schritt umwandelt.

Das Problem: „Gierige“ SQL-Joins

Stellen wir uns folgendes Szenario vor, das bei der Verarbeitung von Bank- oder Verwaltungsdaten häufig vorkommt:

Sie müssen zwei mittelgroße Tabellen (z. B. 500.000 Zeilen und 300.000 Zeilen) zusammenführen. Die Herausforderung besteht darin, dass die Join-Schlüssel nicht direkt sind. Die Kennungen sind numerisch gespeichert, und der Abgleich erfordert die Umwandlung dieser Zahlen in Zeichenketten und die anschließende Extraktion spezifischer Teilzeichenketten. Schlimmer noch, die Join-Bedingung enthält ein OR, was bedeutet, dass eine Übereinstimmung auf zwei verschiedene Weisen erfolgen kann.

So sieht der Engpass in SQL aus:

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;

Warum ist das so langsam?

On-the-fly-Transformation (PUT und SUBSTR): SAS^© muss diese Typkonvertierungen (numerisch zu Zeichen) und Extraktionen für jede potenzielle Zeilenkombination durchführen, bevor sie verglichen werden können.
Die OR-Klausel: Die Verwendung von OR in einem Join hindert den SQL-Optimierer oft daran, Indizes effizient zu nutzen. Dies zwingt das System oft, ein teilweises oder vollständiges „kartesisches Produkt“ durchzuführen, bei dem jede Zeile der Tabelle T1 mit einem großen Teil der Zeilen von T2 verglichen wird.
Fehlen von eindeutigen Schlüsseln: Wenn die Join-Schlüssel in beiden Tabellen nicht eindeutig sind, steigt die Komplexität exponentiell an.

In einer solchen Situation ist auch ein klassischer MERGE-Join in einem Data-Schritt schwer umzusetzen, da er eine vorherige Sortierung nach transformierten Schlüsseln erfordert. Hier kommen die Hash-Objekte ins Spiel.

Die Lösung: Der DATA-Schritt und Hash-Objekte

Hash-Objekte ermöglichen es, eine ganze Tabelle (normalerweise die kleinere von beiden) in den Arbeitsspeicher (RAM) zu laden. Dadurch entsteht eine ultraschnelle Suchstruktur, in der man eine Übereinstimmung sofort finden kann, ohne die Daten sortieren zu müssen.

Die Strategie besteht darin:

Die Referenztabelle (die kleinere) vorzubereiten, indem ein sauberer Suchschlüssel erstellt wird.
Diese Tabelle in ein Hash-Objekt im Speicher zu laden.
Die große Tabelle Zeile für Zeile zu lesen, den Suchschlüssel on-the-fly zu berechnen und das Hash-Objekt abzufragen.

Schritt 1: Vorbereitung der Referenzdaten

Anstatt komplexe Berechnungen im Join durchzuführen, bereiten wir die sekundäre Tabelle (TABLE_SECONDAIRE) so vor, dass sie einen gebrauchsfertigen Suchschlüssel (KEY) enthält, der dem Teil der Kennung entspricht, den wir suchen.

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;

Schritt 2: Der DATA-Schritt mit Hash-Objekt

Hier ist der Code, der den komplexen SQL-Join ersetzt.

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;

Der Leistungsgewinn beim Übergang von einem komplexen SQL-Join mit OR-Bedingungen und Konvertierungen zu einer auf Hash-Objekten basierenden Lösung ist oft spektakulär. Verarbeitungen, die Stunden dauerten oder den Server zum Absturz brachten, können in wenigen Sekunden ausgeführt werden.

Obwohl die Syntax von Hash-Objekten technischer ist, sind sie ein unverzichtbares Werkzeug im Arsenal des SAS^©-Entwicklers, der mit großen Datenmengen und nicht standardmäßigen Abgleichslogiken konfrontiert ist.

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

Wie extrahiert man einen "Substring" aus einer numerischen Variable?

29/04/2021 • 3

Wie man eine Tabelle anhand einer Liste von IDs mit PROC SQL filtert

26/02/2020 • 7

View full category

References & Docs

PROC SQL 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