DISTINCT vs. GROUP BY en Teradata

En cualquier base de datos es muy común tener que contar los elementos diferentes (columnas de filas) que hay en una tabla.

En Teradata, como en cualquier otro SGBDR, se puede hacer muy fácilmente mediante cláusulas ‘DISTINCT’ o ‘GROUP BY’. No obstante los mecanismos de implementación que Teradata utiliza para una y otra son muy diferentes. Ante eso, llega la inevitable pregunta “¿Y cuál de los dos es mejor?” Y la respuesta es, como de costumbre, ‘”Depende.”

La arquitectura de Teradata está pensada por y para sacar partido del paralelismo. Este paralelismo tiene su lado bueno (hacer una tarea en paralelo hace que el trabajo se reparta y sea más efectivo) y su negativo (el trabajo terminará cuando el último de los procesos en paralelo termine). En general, suele darse el primero de ellos, pero a veces puede ocurrir que el paralelismo (o la falta de él) juegue en nuestra contra. Es por eso que la máxima de las bases de datos “Conoce tus datos” en Teradata tiene un añadido: “Y su distribución”.

Respecto a esto, hay muchas ideas preconcebidas y bastante desconocimiento de como Teradata implementa el paralelismo, como por ejemplo, aquí.

Pero sigamos con el asunto.

Veamos cómo funciona DISTINCT:

‘DISTINCT’ hace que todos los ‘AMPs’ en los que haya datos los manden a un ’spool’ que se distribuye entre los ‘AMPs’ por el valor elegido (es por eso que no importa si la columna es ‘primary index’ o no, como también se decía aquí ). Una vez en el ’spool’ y distribuido según sus valores se hace un ‘SORT’ con eliminación de duplicados y se devuelve el resultado total al usuario.

Veamos ahora cómo funciona ‘GROUP BY’:

‘GROUP BY’ realiza un ‘SUM’ local a cada ‘AMP’ por el valor elegido (GROUP BY). Utilizando los resultados de cada ‘AMP’ se computan los totales y se envían a un ’spool’ (que también estará distribuido). De ahí se hará un ‘retrieve’ a otro ’spool’ local a cada AMP, desde donde se devuelve el resultado al usuario.

Visto esto nos damos cuenta de que cuando hay muchos valores distintos un ‘DISTINCT’ debe ir mejor, ya que nos ahorramos los ‘SUMS’ que harían los ‘AMPs’ si hiciéramos un ‘GROUP BY’ (estaríamos intentando sumar valores cuasi-únicos: mucho trabajo para poco resultado) y además el resultado en el ’spool’ estará muy distribuido. Luego sólo queda el ‘SORT’ y la devolución de los datos.

Ocurre justo lo contrario cuando hay pocos valores distintos: ahí un ‘DISTINCT’ mandaría todos los datos a unos pocos ‘AMPs’ y además habría que efectuar un ‘SORT’ con eliminación de gran cantidad de filas. ‘GROUP BY’ saca aquí partido del paralelismo: los ‘AMPs’ hacen ‘SUMS’ locales, encargándose de lo más duro del trabajo para tener un ’spool’ final ya listo para devolverse.

Vamos a ver si lo que decimos es cierto.

Nos creamos una tabla de pruebas:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:

CREATE SET TABLE MY_DB.PRUEBA11 ,
     NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      ID1 INTEGER NOT NULL,
      ID2 INTEGER NOT NULL,
      ID3 INTEGER NOT NULL )
PRIMARY INDEX ( ID1 );

CREATE SET TABLE MY_DB.PRUEBA11 ,
     NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      ID1 INTEGER NOT NULL,
      ID2 INTEGER NOT NULL,
      ID3 INTEGER NOT NULL )
PRIMARY INDEX ( ID1 );

 *** Table has been created.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:

Ahora insertamos varios millones de filas de una tabla ‘fuente’. Para nuestro ejemplo cargaremos los datos (además de en el ‘PRIMARY INDEX’) en dos columnas sin indexar, para obligar a que se efectúen ‘FULL SCANS’ y para ver que el paralelismo también funciona cuando accedemos a columnas sin ‘hashing‘ (al contrario de lo que erróneamente se decía aquí ). Necesitaremos dos distribuciones diferentes de los datos: una muy distribuída (muchos valores distintos) y otra poco distribuida (pocos valores distintos):

INSERT INTO MY_DB.PRUEBA11 (ID1,
                            ID2,
                            ID3)
                     SELECT ID,
                            ID,
                            ID MOD 10
                       FROM MY_DB.TABLA_FUENTE;

INSERT INTO MY_DB.PRUEBA11 (ID1,
                            ID2,
                            ID3)
                     SELECT ID,
                            ID,
                            ID MOD 10
                       FROM MY_DB.TABLA_FUENTE;

 *** Insert completed. 26658715 rows added.
 *** Total elapsed time was 20 seconds.

26.658.715 parecen suficientes para una prueba.

Ahora vamos con las ‘queries’ y veremos cuál tarda más.

Empezamos con la columna con muchos valores distintos:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT COUNT(1) FROM (
SELECT DISTINCT ID2 FROM MY_DB.PRUEBA11
) a
;

SELECT COUNT(1) FROM (
SELECT DISTINCT ID2 FROM MY_DB.PRUEBA11
) a
;

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 28 seconds.

   Count(1)
-----------
   26658715

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT COUNT(1) FROM (
SELECT ID2 FROM MY_DB.PRUEBA11 GROUP BY ID2
) a
;

SELECT COUNT(1) FROM (
SELECT ID2 FROM MY_DB.PRUEBA11 GROUP BY ID2
) a
;

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 2 minutes and 20 seconds.

   Count(1)
-----------
   26658715

En efecto: El ‘DISTINCT’ lo hemos hecho en 28 segundos, mientras que el ‘GROUP BY’ nos ha llevado 2 minutos y 20 segundos (casi el tiempo anterior por cinco).

Vamos ahora con la columna con pocos valores distintos:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT COUNT(1) FROM (
SELECT DISTINCT ID3 FROM MY_DB.PRUEBA11
) a
;

SELECT COUNT(1) FROM (
SELECT DISTINCT ID3 FROM MY_DB.PRUEBA11
) a
;

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 23 seconds.

   Count(1)
-----------
         10

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT COUNT(1) FROM (
SELECT ID3 FROM MY_DB.PRUEBA11 GROUP BY ID3
) a;

SELECT COUNT(1) FROM (
SELECT ID3 FROM MY_DB.PRUEBA11 GROUP BY ID3
) a;

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 5 seconds.

   Count(1)
-----------
         10

Aquí ocurre lo contrario: el ‘GROUP BY’ sólo tarda 5 segundos, mientras que el ‘DISTINCT’ se va a 23 (también casi el tiempo anterior por cinco). Estamos sacando partido del paralelismo del ‘SUM’ local de los AMPs. La diferencia sería aún mayor si en vez de 10 valores distintos hubiera sólo dos, tres o cuatro… ya que estaríamos penalizando aún más el ‘DISTINCT’.

Así pues, la conclusión es que hay que usar ‘DISTINCT’ con columnas con muchos valores distintos y ‘GROUP BY’ con columnas con un pequeño número de valores distintos.

En cualquier caso, los valores obtenidos -incluso los peores- no están mal para 26.658.715 filas. ( Y ni mucho menos se ‘cuelgan’ como también se decía aquí ):

Otra cosa que vemos es que un ‘DISTINCT’ siempre dará los resultados EN ORDEN (ya que el último paso efectuado es un ‘SORT-MERGE’), mientras un ‘GROUP BY’ los devolverá en orden aleatorio.

Finalmente, un consejo: ante la duda (sin conocer los datos) yo intentaría siempre un ‘GROUP BY’.

Saludos.

Carlos.