Grande différence de performance lors de l’utilisation de groupe par vs distinct

J’effectue des tests sur un serveur HSQLDB avec une table contenant 500 000 entrées. La table n’a pas d’indices. Il existe 5000 clés de gestion distinctes. J’ai besoin d’une liste d’entre eux. Naturellement, j’ai commencé avec une requête DISTINCT :

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

Cela prend environ 90 secondes !!!

Ensuite, j’ai essayé d’utiliser GROUP BY :

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

Et ça prend 1 seconde !!!

En essayant de comprendre la différence, j’ai exécuté EXLAIN PLAN FOR mais cela semble donner les mêmes informations pour les deux requêtes.

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 

EDIT : J’ai fait des tests supplémentaires. Avec 500 000 enregistrements dans HSQLDB avec toutes les clés métier distinctes, les performances de DISTINCT sont désormais meilleures – 3 secondes vs GROUP BY ce qui prend environ 9 secondes.

Dans MySQL deux requêtes sont identiques:

MySQL: 500 000 lignes – 5 000 clés métier distinctes: les deux requêtes: 0,5 seconde MySQL: 500 000 lignes – toutes les clés métier distinctes: SELECT DISTINCT ... – 11 secondes SELECT ... GROUP BY business_key clé SELECT ... GROUP BY business_key – 13 secondes

Donc, le problème est uniquement lié à HSQLDB .

Je serai très reconnaissant si quelqu’un peut expliquer pourquoi il y a une différence si radicale.

Les deux requêtes expriment la même question. Apparemment, l’optimiseur de requêtes choisit deux plans d’exécution différents. Je suppose que l’approche distinct est exécutée comme suit:

  • Copier toutes business_key valeurs business_key dans une table temporaire
  • Trier la table temporaire
  • Scannez la table temporaire en renvoyant chaque élément différent de celui qui le précède

Le group by pourrait être exécuté comme:

  • Analyser la table complète en stockant chaque valeur de business key dans une table de hachage
  • Renvoyer les clés de la hashtable

La première méthode optimise l’utilisation de la mémoire: elle fonctionnera quand même raisonnablement bien lorsqu’une partie de la table temporaire doit être remplacée. La seconde méthode optimise la vitesse, mais nécessite potentiellement une grande quantité de mémoire s’il y a beaucoup de clés différentes.

Étant donné que vous avez soit assez de mémoire ou peu de clés différentes, la seconde méthode surpasse la première. Il n’est pas rare de voir des différences de performance de 10 fois, voire de 100 fois entre deux plans d’exécution.