TOP: ¿dentro o fuera de la vista?

Recientemente recibí un ‘mail‘ de uno de los habituales de este ‘blog‘ en el que me comentaba las diferencias que hay entre dos ‘SELECT’ aparentemente iguales:

replace view borrar_0 as select * from tablagrande;
select top 1 * from borrar_0;

replace view borrar_1 as select top 1 * from tablagrande;
select * from borrar_1;

El contexto era algún proceso ETL/ELT con Powercenter. Y el asunto era la gran diferencia de rendimiento que había entre las dos sentencias que deberían comportarse, sobre el papel, de forma similar.

Pero nada más lejos de la realidad:

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

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

   Count(1)
-----------
   60279016

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

REPLACE VIEW MY_DB.POST_TOP
AS
SELECT MY_COLUMN
  FROM MY_DB.TABLA_GRANDE;

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT TOP 10 * FROM MY_DB.POST_TOP;

 *** Query completed. 10 rows found. One column returned.
 *** Total elapsed time was 1 second.

MY_COLUMN
---------
B1I293945
S1E253546
T1F203344
S1E213349
I1B283549
I1B283749
S1E213249
S1E253346
B1I293745
S1S293847

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
REPLACE VIEW MY_DB.PRE_TOP
AS
SELECT TOP 10 MY_COLUMN
  FROM MY_DB.TABLA_GRANDE;

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

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT * FROM MY_DB.PRE_TOP;

 *** Query completed. 10 rows found. One column returned.
 *** Total elapsed time was 11 minutes and 3 seconds.

MY_COLUMN
---------
C12213044
I1B203240
I1B223240
C1C203248
I1B243840
I1B243640
M13293347
M12293347
M11293347
C1R203143

Aparte de tardar infinitamente más, la segunda opción (con el TOP construído dentro de la vista) consume un disparate de ‘spool‘. La respuesta al enigma es obvia: cuando el TOP está incluído en la definición de la vista ésta se construye entera antes de devolver el ‘resultset’ de la ‘SELECT’ (y se debe, por tanto, construir un ‘spool’ con la tabla enterita para efectuar el TOP 10). Mientras que cuando la vista no tiene TOP en su definición el ‘SELECT’ utiliza una optimización para devolver sólo diez filas (ver pasos 3 y 4 en el primer EXPLAIN):

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
EXPLAIN
SELECT TOP 10 * FROM MY_DB.POST_TOP;

 *** Help information returned. 26 rows.
 *** Total elapsed time was 1 second.

Explanation
-----------------------------------------------------------------------
  1) First, we lock a distinct MY_DB."pseudo table" for read on a
     RowHash to prevent global deadlock for
     MY_DB.TABLA_GRANDE.
  2) Next, we lock MY_DB.TABLA_GRANDE in view
     POST_TOP for read.
  3) We do an all-AMPs STAT FUNCTION step from
     MY_DB.TABLA_GRANDE in view POST_TOP by way of
     an all-rows scan with no residual conditions into Spool 6, which
     is built locally on the AMPs.  The result rows are put into Spool
     2 (group_amps), which is built locally on the AMPs.  This step is
     used to retrieve the TOP 10 rows.  Single AMP optimization is
     used. If this step retrieves less than 10 rows, then execute step
     4.  The size is estimated with low confidence to be 60,353,380
     rows (1,810,601,400 bytes).
  4) We do an all-AMPs STAT FUNCTION step from
     MY_DB.TABLA_GRANDE in view POST_TOP by way of
     an all-rows scan with no residual conditions into Spool 6 (Last
     Use), which is built locally on the AMPs.  The result rows are put
     into Spool 2 (group_amps), which is built locally on the AMPs.
     This step is used to retrieve the TOP 10 rows.  The size is
     estimated with low confidence to be 60,353,380 rows (
     1,810,601,400 bytes).
  5) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 2 are sent back to the user as the result of
     statement 1.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
EXPLAIN
SELECT * FROM MY_DB.PRE_TOP;

 *** Help information returned. 16 rows.
 *** Total elapsed time was 1 second.

Explanation
-----------------------------------------------------------------------
  1) First, we lock a distinct MY_DB."pseudo table" for read on a
     RowHash to prevent global deadlock for
     MY_DB.TABLA_GRANDE.
  2) Next, we lock MY_DB.TABLA_GRANDE in view
     PRE_TOP for read.
  3) We do an all-AMPs STAT FUNCTION step from
     MY_DB.TABLA_GRANDE in view PRE_TOP by way of
     an all-rows scan with no residual conditions into Spool 6 (Last
     Use), which is assumed to be redistributed by value to all AMPs.
     The result rows are put into Spool 2 (group_amps), which is built
     locally on the AMPs.  The size is estimated with low confidence to
     be 60,353,380 rows (1,810,601,400 bytes).
  4) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 2 are sent back to the user as the result of
     statement 1.

Saludos.

Carlos.

2 respuestas a TOP: ¿dentro o fuera de la vista?

  1. […] En capítulos anteriores vimos la diferencia entre crear una vista ‘incondicional’ y hacer un ‘SELECT TOP’ sobre ella o crear la vista con el ‘TOP’ incluído en ella y hacer el ‘SELECT’ ‘incondicional’. La respuesta era que el ‘TOP’ debería ir fuera, ya que Teradata debe construir la vista y eso implica crear un ‘spool’ completo ANTES de aplicarle el ‘TOP’. […]

  2. […] terminar con la saga que hemos venido desarrollando aquí y aquí vamos a ver que muchas veces el uso de ‘TOP’ es incorrecto o […]

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: