Dédoublonnage partiel, problème de performances. [Résolu]

[vincdevcom]

Bonjour,

J'ai un problème de dédoublonnage partiel qui correspond exactement à la situation
présentée à l'adresse : http://sqlpro.developpez.com/cours/sqlaz/erreurs/#L8.2

En l'occurrence j'ai des données provenant d'un compteur électrique qui consistent
simplement en un ensemble de valeurs associées à une étiquette. Pour l'instant, ces
données arrivent en continu et seules les valeurs qui ont changé sont insérées dans la
base associées à la date. L'unique table :

Code :

1
2
3
4
5
6
7
8
9
CREATE TABLE `T_TELEINFO` (
  `TIF_ID` int(11) NOT NULL AUTO_INCREMENT,
  `TIF_DATE` datetime NOT NULL,
  `TIF_ETIQUETTE` varchar(50) COLLATE utf8_bin NOT NULL,
  `TIF_VALEUR` varchar(50) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`TIF_ID`),
  KEY `TIF_DATE` (`TIF_DATE`),
  KEY `TIF_ETIQUETTE` (`TIF_ETIQUETTE`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Mon objectif est d'extraire, pour chaque étiquette, la valeur la plus récente.

Si j'applique la méthode du lien ci-dessus :

Code :

1
2
3
SELECT * FROM `T_TELEINFO` TIF1
WHERE TIF_DATE = (SELECT MAX(TIF_DATE) FROM `T_TELEINFO` TIF2
WHERE TIF2.TIF_ETIQUETTE = TIF1.TIF_ETIQUETTE )

Ça fonctionne, mais il faut environ 1000 s !!!

En revanche, si je modifie la sous-requête de sorte à demander la liste des dates avec
un "LIMIT 1" pour ne retenir que la plus récente :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SELECT * FROM `T_TELEINFO` TIF1 
WHERE TIF_DATE = (SELECT TIF_DATE FROM `T_TELEINFO` TIF2 
WHERE TIF2.TIF_ETIQUETTE = TIF1.TIF_ETIQUETTE 
ORDER BY TIF_DATE DESC
LIMIT 1);
+--------+---------------------+---------------+------------+
| TIF_ID | TIF_DATE            | TIF_ETIQUETTE | TIF_VALEUR |
+--------+---------------------+---------------+------------+
|  60298 | 2011-01-22 14:47:41 | IINST         | 006        |
|  60337 | 2011-01-22 14:48:46 | BASE          | 016135231  |
|  60338 | 2011-01-22 14:48:46 | PAPP          | 01270      |
+--------+---------------------+---------------+------------+
3 rows IN SET (0.33 sec)

On est 3000 fois plus rapide. Mais ça me paraît toujours très lent pour une requête
aussi simple, pour une base aussi simple et qui ne contient, pour l'instant, qu'environ
60000 lignes.

Cette requête me paraît être un standard de l'extraction d'un journal. Alors comment
expliquer une telle lenteur ? Comment conviendrait-il de faire cela sous MySQL ?

Mon projet, dont ceci me sert à valider les concepts, est nettement plus ambitieux et
des délais pareils (même 0,33 s) sont inacceptables (affichages en temps réel...).
J'envisage de créer une table avec les dernières valeurs mises à jour par un trigger.
Mais ça fait perdre en généralité car on peut vouloir connaître les valeurs pour
n'importe quelle date.

Merci d'avance à ceux qui voudront bien m'éclairer :-).

vincent

[skuatamad]

Juste au cas où, qu'est ce que ça donne sans requête corrélée ?

Code :

1
2
3
4
5
6
SELECT TIF1.* 
FROM T_TELEINFO TIF1
JOIN (SELECT TIF_ETIQUETTE, MAX(TIF_DATE) AS max_date
      FROM T_TELEINFO
      GROUP BY TIF_ETIQUETTE
      ) TIF2 ON TIF1.TIF_ETIQUETTE = TIF2.TIF_ETIQUETTE AND TIF1.TIF_DATE = TIF2.max_date

Pour améliorer les perfs de cette requête (et peut être des autres) tu peux créer un index sur (TIF_ETIQUETTE, TIF_DATE)
Je veux dire un index sur les 2 colonnes et pas juste 2 index sur les 2 colonnes.

A voir par rapport aux autres requêtes, mais si ce nouvel index est pertinant pour ce besoin, il est possible que l'index sur TIF_ETIQUETTE soit supprimable (pour gagner du temps en insertion), à tester bien sûr.

Après 0.33 sec c'est déjà pas mal, et niveau affichage en temps réel je ne pense pas que l'utilisateur fasse une grande différence entre 0.1 et 0.3 sec, quel est ton objectif ?

[vincdevcom]

Envoyé par skuatamad

Juste au cas où, qu'est ce que ça donne sans requête corrélée ?

Code :

J'ai mis un peu de temps à comprendre la requête :-). J'avais constaté que couper en
deux permettait d'aller vite mais je croyais qu'il fallait faire le lien par le code externe
et tu m'apprends donc qu'on peut en quelque sorte créer une table à la volée. Ça
permet encore un gain significatif :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT TIF1.*
    -> FROM T_TELEINFO TIF1
    -> JOIN (SELECT TIF_ETIQUETTE, MAX(TIF_DATE) AS max_date
    ->       FROM T_TELEINFO
    ->       GROUP BY TIF_ETIQUETTE
    ->       ) TIF2 ON TIF1.TIF_ETIQUETTE = TIF2.TIF_ETIQUETTE AND TIF1.TIF_DATE = TIF2.max_date
    -> ;
+--------+---------------------+---------------+------------+
| TIF_ID | TIF_DATE            | TIF_ETIQUETTE | TIF_VALEUR |
+--------+---------------------+---------------+------------+
|  63508 | 2011-01-22 16:16:57 | BASE          | 016137109  |
|  63119 | 2011-01-22 16:08:42 | IINST         | 007        |
|  63507 | 2011-01-22 16:16:55 | PAPP          | 01550      |
+--------+---------------------+---------------+------------+
3 rows IN SET (0.08 sec)

Pour améliorer les perfs de cette requête (et peut être des autres) tu peux créer un index sur (TIF_ETIQUETTE, TIF_DATE)
Je veux dire un index sur les 2 colonnes et pas juste 2 index sur les 2 colonnes.
A voir par rapport aux autres requêtes, mais si ce nouvel index est pertinant pour ce besoin, il est possible que l'index sur TIF_ETIQUETTE soit supprimable (pour gagner du temps en insertion), à tester bien sûr.

Rajouter cet index permet de passer de typiquement 0,0725 s à typiquement 0,0700 s.
Enlever l'index sur TIF_ETIQUETTE fait perdre cet avantage. En revanche enlever
l'index sur TIF_DATE n'a pas d'effet significatif.

Après 0.33 sec c'est déjà pas mal, et niveau affichage en temps réel je ne pense pas que l'utilisateur fasse une grande différence entre 0.1 et 0.3 sec, quel est ton objectif ?

Ce temps est acceptable en l'état. Ce qui me fait peur c'est qu'une fois qu'on ajoutera
d'autres données, qu'il faudra trier selon leurs pertinences et gérer des droits d'accès...
Ça devienne vraiment très lent.

Par rapport aux 1000 s ça fait un gain d'un facteur 15000 ! Merci beaucoup.

vincent

[skuatamad]

et tu m'apprends donc qu'on peut en quelque sorte créer une table à la volée

Ca s'appelle une sous requete ce qui est assez classique mais que tu connais peut être mieux via l'utilisation de IN.

Rajouter cet index permet de passer de typiquement 0,0725 s à typiquement 0,0700 s.

je ne pensais pas que la différence serait si faible donc en l'état autant conserver les 2 index que tu avais prédédemment créés.
Par contre il est possible que tu soit "victime" d'un phénomeène de cache qu'en est il après redémarrage de la base ? De plus cette différence est peut être très faible sur 60000 lignes mais pourrait être beaucoup plus conséquente avec 5M de lignes, as tu la possibilité de tester en générant une plus grosse table (comme 5M de lignes) ?

Par rapport aux 1000 s ça fait un gain d'un facteur 15000 ! Merci beaucoup.

Content d'avoir pu t'aider

[vincdevcom]

Envoyé par skuatamad

Ca s'appelle une sous requete ce qui est assez classique mais que tu connais peut être mieux via l'utilisation de IN.

En fait je pensais qu'une sous-requête s'utilisait pour extraire une valeur et non une colonne, encore moins une table.

Envoyé par skuatamad

je ne pensais pas que la différence serait si faible donc en l'état autant conserver les 2 index que tu avais prédédemment créés.
Par contre il est possible que tu soit "victime" d'un phénomeène de cache qu'en est il après redémarrage de la base ? De plus cette différence est peut être très faible sur 60000 lignes mais pourrait être beaucoup plus conséquente avec 5M de lignes, as tu la possibilité de tester en générant une plus grosse table (comme 5M de lignes) ?

En fait la base est alimentée par de nouvelles données en permanence. Sil n'y a pas
eu de modification entre deux requêtes, le temps est de 0,00002 s. Sinon il est celui
que j'ai indiqué. On verra à l'usage, on a déjà bien progressé :-).

Mais je me demande bien pourquoi la requête de départ prend tellement de temps.

[CinePhil]

La mauvaise réputation de MySQL en matière, entre autres, de performance avec des requêtes corrélées n'est plus à faire !