Énorme différence de performances lors de l'utilisation de group by vs distinctes

Je suis d'effectuer quelques tests sur un HSQLDB serveur avec une table contenant plus de 500 000 entrées. La table n'a pas d'indices. Il y a 5000 distinctes d'affaires clés. J'ai besoin d'une liste d'entre eux. Naturellement, j'ai commencé avec un DISTINCT requête:

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

Il faut environ 90 secondes!!!

Puis 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 il prend 1 seconde!!!

À essayer de comprendre la différence, j'ai couru EXLAIN PLAN FOR mais il 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

MODIFIER:
J'ai fait des tests supplémentaires. Avec 500 000 enregistrements dans HSQLDB avec tous distincts les clés d'entreprise, la performance de DISTINCT est maintenant mieux 3 secondes, vs GROUP BY qui a pris environ 9 secondes.

Dans MySQL les deux requêtes de préformation le même:

MySQL: 500 000 lignes - 5 000 affaires distinctes clés:
Les deux requêtes: 0,5 seconde
MySQL: 500 000 lignes - tous distincts les clés d'entreprise:
SELECT DISTINCT ... - 11 secondes
SELECT ... GROUP BY business_key - 13 secondes

De sorte que le problème est uniquement lié à HSQLDB.

Je serai très reconnaissant si quelqu'un peut expliquer pourquoi il existe une différence radicale.

s'il vous plaît montrer le résultat de EXPLAIN PLAN ET essayez de lancer le DISTINCTrequête APRÈS l'exécution de la GROUP BY pour voir si peut-être certains de la mise en cache est de biaiser le moment...
Étant donné que vous obtenez le même plan pour chaque requête, il semble que ce soit les données de la table ou le résultat a été mis en cache.
J'ai couru tant de fois qui croient que la mise en cache n'est pas un problème. Je suis annonce la EXLAIN PLAN FOR de sortie.
J'ai une idée, mais je suis vraiment pas sûr - s'il vous plaît essayer SELECT DISTINCT business_key FROM (SELECT business_key FROM memory WHERE concept <> 'case' or attrib <> 'status' or value <> 'closed') - ce qui devrait montrer le même niveau de performance que vous voyez avec le GROUP BY SI mon idée est la bonne.
toujours très lent - 94 secondes. Je vais courir les mêmes requêtes MySQL pour voir ce que va montrer
Ce n'est pas ce pinal dave dit. blog.sqlauthority.com/2007/03/29/...

InformationsquelleAutor Martin Dimitrov | 2011-10-30

66

Les deux requêtes exprimer la même question. Apparemment, l'optimiseur de requête choisit deux différents plans d'exécution. J'imagine que le distinct approche est exécuté comme:
- Copie de tous les business_key valeurs dans une table temporaire
- Trier la table temporaire
- Scan de la table temporaire, de retour de chaque élément qui est différent de celui d'avant il
La group by pourrait être exécuté comme:
- Numériser l'intégralité de la table, le stockage de chaque valeur de business key dans une table de hachage
- De la remise des clés de la table de hachage
La première méthode optimise pour l'utilisation de la mémoire: il serait encore effectuer raisonnablement bien quand une partie de la table temporaire doit être échangé. La deuxième méthode optimise pour la vitesse, mais potentiellement nécessite une grande quantité de mémoire si il y a beaucoup de touches.

Depuis que vous avez assez de mémoire ou de quelques touches différentes, la seconde méthode est plus performante que la première. Il n'est pas rare de voir les différences de rendement de 10 ou même 100 fois entre deux plans d'exécution.
- Merci pour la réponse. Sont vos suppositions évident à partir de la EXPLAIN de sortie? À la fois regarder la même chose pour moi.
- Aussi loin que je peux voir, le plan ne précise pas comment il va exécuter le rejoindre. Je ne suis même pas sûr de savoir pourquoi il exécute une jointure. Il prend sans doute un HSQLDB spécialiste de lire les expliquer de sortie.
- Comme la réponse indique, la deuxième méthode utilise plus de mémoire et peut frapper garbage collection (GC) trop souvent. Si vous augmentez la JVM de l'allocation de mémoire, il ne devrait pas y avoir une énorme différence entre les deux temps de requête.
- J'ai fait le test supplémentaire en saisissant toutes les clés distinctes dans le tableau (voir ci-dessus). Penses-tu que le résultat prouve votre point de vue? Merci beaucoup.
- Il y a trop de variables dans la base de données de l'optimisation pour vraiment prouver quoi que ce soit. Cependant, il semble cohérent dans le sens où la première approche serait relativement plus rapide lorsque toutes les touches sont différentes.
- Juste assez. Merci pour l'aide.
- à 200 000 enregistrement group_by passe 500ms et distinctes passe 60ms.
- Peut une PME - expert veuillez expliquer cela plus en détails avec des exemples... j'ai eu ce problème plusieurs fois, mais ne semblent pas s'en passer... je sais que le fixer, mais je veux savoir comment et POURQUOI
InformationsquelleAutor Andomar

Vous devez vous connecter pour publier un commentaire.