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).
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:
PROC SQL noprint;
CREATE TABLE Resultat AS
SELECT DISTINCT
T1.ID_Parent_L,
T2.CodeCedant,
T2.ID_Client,
T1.Nom
FROM TABLE_PRINCIPALE T1
INNER JOIN TABLE_SECONDAIRE T2
/* La source du problème : conversions et condition OR */
ON (substr(put(T1.ID_Parent_L,14.),7,8) = substr(put(T2.ID_Client,14.),1,8)
OR substr(put(T1.ID_Parent_L,14.),1,8) = substr(put(T2.ID_Client,14.),1,8))
;
QUIT;
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))
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.
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:
Preparar la tabla de referencia (la más pequeña) creando una clave de búsqueda limpia.
Cargar esta tabla en un objeto hash en memoria.
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.
PROC SQL;
CREATE TABLE HashData AS
SELECT UNIQUE
CodeCedant,
ID_Client,
/* Création de la clé de jointure standardisée */
put(put(ID_Client,14.),$8.) AS KEY
FROM TABLE_SECONDAIRE;
QUIT;
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;
Paso 2: El paso de datos con Hash Object
Este es el código que reemplaza la compleja unión SQL.
DATA Resultat_Hash;
/* On lit la table principale ligne par ligne */
SET TABLE_PRINCIPALE (keep=ID_Parent_L Nom);
/* Initialisation de l'objet de hachage (une seule fois au début) */
if _N_=1 then do;
/* Déclaration : on charge la table préparée en mémoire */
dcl hash H(dataset:'HashData');
/* Définition de la clé de recherche */
H.definekey('KEY');
/* Définition des données à récupérer en cas de succès */
H.definedata('CodeCedant','ID_Client');
H.definedone();
/* Astuce pour éviter de lire HashData ici, mais initialiser les variables */
if 0 then set HashData;
end;
/* --- Cœur du traitement --- */
/* Tentative 1 : On calcule la clé potentielle selon la première logique */
RC = H.find(key: put(put(ID_Parent_L,14.),$8.));
/* Si RC (Code Retour) = 0, la recherche a réussi.
Les variables CodeCedant et ID_Client sont remplies automatiquement. */
if RC=0 then output;
else do;
/* Optionnel : Si votre logique SQL initiale avait un "OR",
vous pouvez tenter une deuxième recherche ici si la première échoue.
Exemple :
RC = H.find(key: substr(put(ID_Parent_L,14.),7,8));
if RC=0 then output;
*/
end;
drop RC;
run;
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_=1THENDO;
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 THENSET 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 THENOUTPUT;
26
ELSEDO;
27
/* Optionnel : Si votre logique SQL initiale avait un "OR",
28
vous pouvez tenter une deuxième recherche ici si la première échoue.
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.
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.
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.
This site uses technical and analytical cookies to improve your experience.
Read more.
