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.

Anuncios

14 respuestas a DISTINCT vs. GROUP BY en Teradata

  1. PAul dice:

    Muy interesante Carlos !

  2. BI Fácil dice:

    Lamento no haber conocido tu blog antes, cuando estuve trabajando con Teradata.

  3. Monis Iqbal dice:

    This is interesting Carlos. What do you think about other database e.g. Oracle, do they have show performance for both distinct, group by because they aren’t parallel?

    • carlosal dice:

      Sure.
      Each RDBMS implements the SQL differently. SQL is a 4th generation language (you care about what is done, not about how it is done). So the processes behind ‘DISTINCT’ or ‘GROUP BY’ or whatever are specific for each RDBMS.

      Cheers.

      Carlos.

      • BI FACIL dice:

        De hecho, en las versiones actuales funciona como dices, aunque nadie nos asegura que no pueda cambiar en el futuro… Como dices, ¡sólo deberíamos precuparnos en qué queremos hacer, y no en cómo hacerlo!…

        Es decir, en el mejor de los mundos posibles (pongamos Oracle 24g, Teradata v18), debería dar igual hacer un GROUP BY o un DISTINCT… El motor de la base de datos conoce (o debería conocer) la distribución de los datos posibles, y debería escoger el mejor plan de ejecución posible en cada caso…

        Desde este punto de vista, la distinción que comentas entre DISTINCT y GROUP BY no es una “feature”… Es un “bug”…

        Lo mismo ocurre entre poner JOIN o poner claúsulas WHERE… ¿Qué es mejor? Debería dar igual (y creo que da igual)… Yo siempre pongo JOINS, y no estudio otra posibilidad a menos que tenga problemas reales… ¿Qué sabes tu de esto? ¿Existe alguna diferencia en Oracle/Teradata entre poner las relaciones con la sintaxis modernas (INNER JOIN…) y la antigua (WHERE…)?

        • carlosal dice:

          Bueno, no sé si estoy muy de acuerdo en lo que dices. No hay que perder de vista el hecho de que DISTINCT está para devolver valores distintos, mientras que GROUP BY está para devolver valores agrupados. Aunque se pueda utilizar cualquiera de las dos cosas para resolver determinados problemas, cada una tiene su cometido.

          En cuanto a las JOINS, el uso de la sintaxis ANSI (INNER JOIN) o implícita (WHERE) es indistinto… a no ser que se trate de OUTER JOINS, como dijimos aquí.

          Saludos.

          Carlos.

  4. Así implementado, el select distinct es un arma de destrucción masiva cuyo uso debería estar restringido.

    Por ejemplo, tengo una tabla enorme en la que hay datos de los últimos doce meses. Quiero ver qué meses están cargados y hago:

    select distinct mes from tabla_enorme;

    ¡Se le caen las tuercas a Teradata!

    Obviamente, tengo que hacer un

    select mes from tabla_enorme group by 1;

    Pero, ¿cómo se lo hago saber a todos mis usuarios?

    • carlosal dice:

      Te lo pondré de otra forma:

      “Tengo una flota de furgonetas de reparto y de vez en cuando deben llevar material entre Madrid y Valencia. Como los conductores están acostumbrados a moverse por callejuelas estrechas, intentan ir de Madrid a Valencia sin pasar de 2ª. ¡Y se le caen las tuercas a las furgonetas!”

      Los usuarios de las bases de datos, aunque no sean expertos en ellas, al menos deben tener nociones básicas, pero sólidas, de cómo funcionan.

      Otra opción, si es una consulta muy común y se quiere limitar los estropicios que puedan causar los usuarios ‘montaraces’, es preparar una vista (o una macro) que los usuarios invoquen sin más.

      Esta es una alternativa que, en determinadas circunstancias (usuarios poco experimentados o sencillamente legos) muchos DBA’s aprecian -incluido yo-. El DBA tiene así más control y se limitan las ‘queries’ libres poco afinadas o directamente ‘asesinas’ 😉

      Por otra parte, parece que las últimas versiones del optimizador -y en las condiciones adecuadas- son capaces de ejecutar esos tipos de DISTINCT como GROUP BY’s. (Documentación de Teradata 13: “Group By and Distinct Performance Equivalence”)

      Saludos,

      Carlos.

      • Un primer comentario a tu respuesta: “¡Ven y sé nuestro DBA!”

        El segundo es que estoy comenzando a apreciar el valor de poder ejecutar “distincts” y “groups by”: en nuestro proyecto he descubierto algunas consultas que funcionan mejor (por la distribución de los datos) con un distinct que con un group by y sudo chorros de tinta para parar ese carro sin frenos que es la “leyenda urbana” de que hay que purgar todos los distincts de todos los lugares.

        En tercer lugar, estoy teóricamente de acuerdo con que cabe esperar de los usuarios que tengan nociones sólidas acerca del conocimiento del bicho que tienen entre manos. ¡Hasta los conductores de las furgonetas de tu ejemplo han tenido que sacarse, como poco, el carnet B1! Pero no sé cuántos de nuestros miles de usuarios potenciales pasarían un examen teórico o práctico de la cosa.

        Gracias por el blog. Sólo por consultar dudas en él estoy adquiriendo una inmerecida reputación de gurú de Teradata. Incluso, alguno, cuando lo remito a alguna de tus entradas, piensa que son mías (es que somos tocayos). ¡Te tengo que deber ya algo así como 382 cañas!

  5. […] de DISTINCT y GROUP BY en Teradata. Se ha discutido mucho sobre ello en diferentes sitios, incluso en este blog. El caso es que para la versión 13 Teradata ha cambiado las rutinas internas de comportamiento […]

  6. someone please translate it in English?

  7. Hola carlos,
    muy bueno tu blog, pero cuando utilizas subqueries y necesitas un group by, es lo mismo remplazarlo por un selec distinct

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: