Proc SQL

Cuando los "Hash Objects" salvan una unión SQL compleja

Simon 23 vues
Niveau de difficulté
Débutant
Publié le :
Dans : Proc SQL
¿Cómo extraer una "subcadena" de una variable numérica?
29/04/2021 • 24
Cómo filtrar una tabla a partir de una lista de identificadores con PROC SQL
26/02/2020 • 28
La trampa del "Many-to-Many" (MERGE vs SQL)
24/08/2019 • 20
Voir toute la catégorie

En el mundo de la programación SAS©, persiste un debate: ¿qué es más eficiente, PROC SQL o el paso DATA? La respuesta clásica es "depende". Sin embargo, existen escenarios específicos donde el rendimiento de SQL se desploma literalmente, pasando de unos pocos minutos a varias horas, o incluso provocando el bloqueo de la sesión.

Uno de estos escenarios críticos ocurre durante las uniones que involucran condiciones complejas, especialmente el uso de OR combinado con transformaciones de variables sobre la marcha en la cláusula ON.

Este artículo explora cómo transformar una consulta SQL ineficiente en un paso de datos ultrarrápido utilizando Tablas de Hash (Hash Objects).

Cuando los "Hash Objects" salvan una unión SQL compleja -
Sommaire

El problema: Las uniones SQL de alto consumo

Imaginemos el siguiente escenario, frecuente en el procesamiento de datos bancarios o administrativos:

Debe unir dos tablas de tamaño mediano (por ejemplo, 500,000 filas y 300,000 filas). El desafío radica en que las claves de unión no son directas. Los identificadores se almacenan en formato numérico, y la correspondencia requiere convertir estos números en cadenas de caracteres y luego extraer subcadenas específicas. Peor aún, la condición de unión contiene un OR, lo que significa que una coincidencia puede ocurrir de dos maneras diferentes.

Así es como se ve el cuello de botella en SQL:

1PROC 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 ;
14QUIT;

¿Por qué es tan lento?

  1. Transformación sobre la marcha (PUT y SUBSTR): SAS© debe realizar estas conversiones de tipo (de numérico a carácter) y estas extracciones para cada combinación potencial de filas antes de poder compararlas.

  2. La cláusula OR: El uso de OR en una unión a menudo impide que el optimizador de SQL utilice los índices de manera eficiente. Esto a menudo obliga al sistema a realizar un "producto cartesiano" parcial o total, comparando cada fila de la tabla T1 con una gran parte de las filas de T2.

  3. Ausencia de claves únicas: Si las claves de unión no son únicas en ambas tablas, la complejidad aumenta exponencialmente.

En este tipo de situación, una unión MERGE clásica en un paso de datos también es difícil de implementar porque requiere una ordenación previa sobre claves transformadas. Aquí es donde entran en juego los objetos hash.

La solución: El paso de datos y los Hash Objects

Los objetos hash (Hash Objects) permiten cargar una tabla entera (generalmente la más pequeña de las dos) en la memoria principal (RAM). Esto crea una estructura de búsqueda ultrarrápida donde se puede encontrar una coincidencia instantáneamente sin necesidad de ordenar los datos.

La estrategia consiste en:

  1. Preparar la tabla de referencia (la más pequeña) creando una clave de búsqueda limpia.

  2. Cargar esta tabla en un objeto hash en memoria.

  3. Leer la tabla grande línea por línea, calcular la clave de búsqueda sobre la marcha e interrogar al objeto hash.

Paso 1: Preparación de los datos de referencia

En lugar de realizar cálculos complejos en la unión, preparamos la tabla secundaria (TABLE_SECONDAIRE) para que contenga una clave de búsqueda (KEY) lista para usar que corresponda a la parte del identificador que estamos buscando.

1PROC 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;
9QUIT;

Paso 2: El paso de datos con Hash Object

Este es el código que reemplaza la compleja unión SQL.

1DATA 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;
36RUN;

La ganancia de rendimiento al pasar de una unión SQL compleja con OR y conversiones a una solución basada en Hash Objects suele ser espectacular. Procesos que tardaban horas o bloqueaban el servidor pueden ejecutarse en unos pocos segundos.

Aunque la sintaxis de los objetos hash es más técnica, es una herramienta indispensable en el arsenal del desarrollador SAS© que se enfrenta a grandes volúmenes de datos y lógicas de coincidencia no estándar.

Retour à la liste des articles
Copyright © WeAreCAS 2014-2026
SAS et tous les autres noms de produits ou de services de SAS Institute Inc. sont des marques déposées ou des marques de commerce de SAS Institute Inc. aux États-Unis et dans d'autres pays. ® indique un enregistrement aux États-Unis. WeAreCAS est un site communautaire indépendant et n'est pas affilié à SAS Institute Inc.