sql - Gran diferencia de rendimiento cuando se usa group by vs distinct

sql count where

Question

Estoy realizando algunas pruebas en un servidor HSQLDB con una tabla que contiene 500 000 entradas. La tabla no tiene índices. Hay 5000 claves de negocios distintas. Necesito una lista de ellos. Naturalmente comencé con una consulta DISTINCT :

SELECT DISTINCT business_key FROM memory WHERE
   concept <> ''case'' or 
   attrib <> ''status'' or 
   value <> ''closed''

¡Toma alrededor de 90 segundos!

Luego intenté usar GROUP BY :

SELECT business_key FROM memory WHERE
       concept <> ''case'' or 
       attrib <> ''status'' or 
       value <> ''closed''
GROUP BY business_key

¡Y toma 1 segundo!

Tratando de descubrir la diferencia con la que ejecuté EXLAIN PLAN FOR pero parece dar la misma información para ambas consultas.

EXLAIN PLAN FOR DISTINCT ...

isAggregated=[false]
columns=[
  COLUMN: PUBLIC.MEMORY.BUSINESS_KEY
]
[range variable 1
  join type=INNER
  table=MEMORY
  alias=M
  access=FULL SCAN
  condition = [    index=SYS_IDX_SYS_PK_10057_10058
    other condition=[
    OR arg_left=[
     OR arg_left=[
      NOT_EQUAL arg_left=[
       COLUMN: PUBLIC.MEMORY.CONCEPT] arg_right=[
       VALUE = case, TYPE = CHARACTER]] arg_right=[
      NOT_EQUAL arg_left=[
       COLUMN: PUBLIC.MEMORY.ATTRIB] arg_right=[
       VALUE = status, TYPE = CHARACTER]]] arg_right=[
     NOT_EQUAL arg_left=[
      COLUMN: PUBLIC.MEMORY.VALUE] arg_right=[
      VALUE = closed, TYPE = CHARACTER]]]
  ]
]]
PARAMETERS=[]
SUBQUERIES[]
Object References
PUBLIC.MEMORY
PUBLIC.MEMORY.CONCEPT
PUBLIC.MEMORY.ATTRIB
PUBLIC.MEMORY.VALUE
PUBLIC.MEMORY.BUSINESS_KEY
Read Locks
PUBLIC.MEMORY
WriteLocks

EXLAIN PLAN FOR SELECT ... GROUP BY ...

isDistinctSelect=[false]
isGrouped=[true]
isAggregated=[false]
columns=[
  COLUMN: PUBLIC.MEMORY.BUSINESS_KEY
]
[range variable 1
  join type=INNER
  table=MEMORY
  alias=M
  access=FULL SCAN
  condition = [    index=SYS_IDX_SYS_PK_10057_10058
    other condition=[
    OR arg_left=[
     OR arg_left=[
      NOT_EQUAL arg_left=[
       COLUMN: PUBLIC.MEMORY.CONCEPT] arg_right=[
       VALUE = case, TYPE = CHARACTER]] arg_right=[
      NOT_EQUAL arg_left=[
       COLUMN: PUBLIC.MEMORY.ATTRIB] arg_right=[
       VALUE = status, TYPE = CHARACTER]]] arg_right=[
     NOT_EQUAL arg_left=[
      COLUMN: PUBLIC.MEMORY.VALUE] arg_right=[
      VALUE = closed, TYPE = CHARACTER]]]
  ]
]]
groupColumns=[
COLUMN: PUBLIC.MEMORY.BUSINESS_KEY]
PARAMETERS=[]
SUBQUERIES[]
Object References
PUBLIC.MEMORY
PUBLIC.MEMORY.CONCEPT
PUBLIC.MEMORY.ATTRIB
PUBLIC.MEMORY.VALUE
PUBLIC.MEMORY.BUSINESS_KEY
Read Locks
PUBLIC.MEMORY
WriteLocks

EDITAR : Hice pruebas adicionales. Con 500 000 registros en HSQLDB con todas las claves comerciales distintivas, el rendimiento de DISTINCT ahora es mejor: 3 segundos, frente a GROUP BY que tardó alrededor de 9 segundos.

En MySQL ambas consultas preforman lo mismo:

MySQL: 500 000 filas - 5 000 claves comerciales distintas: ambas consultas: 0,5 segundos MySQL: 500 000 filas - todas las claves empresariales distintas: SELECT DISTINCT ... - 11 segundos SELECT ... GROUP BY business_key - 13 segundos

Entonces, el problema solo está relacionado con HSQLDB .

Estaré muy agradecido si alguien puede explicar por qué hay una diferencia tan drástica.

Answer 1

Las dos consultas expresan la misma pregunta. Aparentemente, el optimizador de consultas elige dos planes de ejecución diferentes. Mi suposición sería que el enfoque distinct se ejecuta como:

• Copie todos business_key valores de business_key en una tabla temporal
• Ordenar la tabla temporal
• Escanee la tabla temporal, devolviendo cada elemento que es diferente al anterior

El group by podría ejecutarse como:

• Escanee la tabla completa, almacenando cada valor de business key en una tabla hash
• Devuelve las claves de la tabla hash

El primer método se optimiza para el uso de la memoria: aún funcionaría razonablemente bien cuando parte de la tabla temporal deba intercambiarse. El segundo método optimiza la velocidad, pero potencialmente requiere una gran cantidad de memoria si hay muchas claves diferentes.

Como tiene suficiente memoria o pocas teclas diferentes, el segundo método supera al primero. No es inusual ver diferencias de rendimiento de 10x o incluso 100x entre dos planes de ejecución.