Performance et clause WHERE.. IN (SELECT ..).

Version imprimable

Bonjour à tous,

Je travaille avec de gros volumes et je tente d'optimiser mes traitements.

Je pourrais intégrer le parallélisme dans mes traitements. Je pourrais aussi effectuer certaines "opérations hors base", en implémentant des algorithmes spéciaux, mais complexes..

Sur de gros volumes de données, je constate que la construction suivante est "colossalement contre-performante" :

Code:

UPDATE ... WHERE ... IN (SELECT ...);

Par exemple :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
DROP TABLE IF EXISTS `A`;
CREATE TABLE IF NOT EXISTS `A` (
  `idA`   INT NOT NULL AUTO_INCREMENT,
  `value` INT NOT NULL,
  PRIMARY KEY (`idA`)
)
ENGINE = InnoDB;
 
DROP TABLE IF EXISTS `B`;
CREATE TABLE IF NOT EXISTS `B` (
  `idB`    INT NOT NULL AUTO_INCREMENT,
  `fk_idA` INT NOT NULL,
  `value`  INT NOT NULL,
  PRIMARY KEY (`idB`),
  INDEX `idfk_idA` (`fk_idA` ASC),
  INDEX `idvalue` (`value` ASC)
)
ENGINE = InnoDB;
 
CREATE TEMPORARY TABLE `tmpTable`
  SELECT `B`.`fk_idA` AS `idA`
  FROM   `B`
  WHERE  `B`.`value` > 3;
 
UPDATE `A`
SET    `A`.`value` = 10
WHERE  `A`.`idA` IN (SELECT `tmpTable`.`idA` AS `idA` FROM `tmpTable`);

La procédure suivante s'exécute beaucoup plus rapidement :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
DELIMITER //
DROP PROCEDURE IF EXISTS myProc //
CREATE PROCEDURE myProc()
BEGIN
 
  DECLARE n INT DEFAULT 0;
 
  #- Indicateur de "fin de curseur".
  DECLARE done INT DEFAULT 0;
 
  #- Curseur sur les valeurs distinctes de `id_cdr`.
  DECLARE updateCur CURSOR FOR SELECT `idA` AS `idA` FROM `tmpTable`;
 
  #- Curseur de détection de fin de sélection.
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
 
  #- On sélectionne les valeurs sur lesquelles on va travailler.
  DROP TABLE IF EXISTS `tmpTable`;
  CREATE TEMPORARY TABLE `tmpTable`
    SELECT `B`.`fk_idA` AS `idA`
    FROM   `B`
    WHERE  `B`.`value` > 3;
 
  #- Lancement de l'opération. 
  SET done = 0;
  OPEN updateCur;
  FETCH updateCur INTO n;
  WHILE done = 0 DO
    UPDATE `A`
    SET    `A`.`value` = 10
    WHERE  `A`.`idA`=n;
    FETCH updateCur INTO n;
  END WHILE;
  CLOSE updateCur;
 
END //
DELIMITER ;

Ce que je recherche à faire ressemble à un "UPDATE avec jointure" : UPDATE d'une table en fonction d'un critère calculé sur une autre table.

Quelqu'un connaît-il une procédure plus performante pour parvenir au même résultat?

Remarque :

Code:

Server version: 5.1.49-0.dotdeb.0 (Debian)

Merci à tous,

A+

08/09/2010, 15h33
CinePhil
Euh... tu connais les jointures ?

Peut-être que je n'ai pas compris ton besoin mais il me semble que la requête ci-dessous y répond beaucoup plus simplement :
Code:

1 2 3 4 UPDATE A INNER JOIN B ON A.idA = B.fk_idA SET A.value = 10 WHERE B.value > 3
Le problème de non performance de ton procédé vient du fait que tu crées une table temporaire mais que tu ne l'indexes pas donc avec de gros volumes de données, cela devient sensible.

Il faut bien sûr que B.value soit indexé, en plus de la clé étrangère qui elle l'est forcément.
10/09/2010, 14h41
WinNew
Salut CinePhil,

Je te remercie pour les informations que tu m'apportes.
Citation:
Envoyé par CinePhil

Code:

1 2 3 4 UPDATE A INNER JOIN B ON A.idA = B.fk_idA SET A.value = 10 WHERE B.value > 3
Je ne connaissais pas cette construction.

Citation:

Envoyé par CinePhil

Le problème de non performance de ton procédé vient du fait que tu crées une table temporaire mais que tu ne l'indexes pas donc avec de gros volumes de données, cela devient sensible.

Il faut bien sûr que B.value soit indexé, en plus de la clé étrangère qui elle l'est forcément.

Je ne comprends pas la nécessité d'indexer. De mon point de vue, la requête ci-dessous (en rouge) n'a pas de raison d'être indexée car aucun critère de sélection n'est défini.
Code:

1 2 3 UPDATE `A` SET `A`.`value` = 10 WHERE `A`.`idA` IN (SELECT `tmpTable`.`idA` AS `idA` FROM `tmpTable`);
D'autre part, le champ idA est la clé primaire de la table A. Donc, toutes les valeurs insérées dans la clause IN (...) seront trouvées très rapidement.

Ai-je loupé quelque chose?

A+
10/09/2010, 15h40
CinePhil
Citation:
Envoyé par WinNew

Je ne comprends pas la nécessité d'indexer. De mon point de vue, la requête ci-dessous (en rouge) n'a pas de raison d'être indexée car aucun critère de sélection n'est défini.

Code:

1 2 3 UPDATE `A` SET `A`.`value` = 10 WHERE `A`.`idA` IN (SELECT `tmpTable`.`idA` AS `idA` FROM `tmpTable`);

D'autre part, le champ idA est la clé primaire de la table A. Donc, toutes les valeurs insérées dans la clause IN (...) seront trouvées très rapidement.
Il s'agit de la clé primaire de tmpTable ou de la table qui a servi à construire la tmpTable ?

Sauf erreur de ma part, quand tu crées une table temporaire à partir d'une requête, les index et clés des tables d'origine ne sont pas reportés sur la table temporaire !

Au contraire, quand tu fais une jointure, même avec des vues, les index des tables utilisées dans la jointure sont recherchés par le SGBD pour juger de l'optimisation de leur utilisation.

Salut CinePhil,

Je pense que l'on ne parle pas de la même chose.

Voici un jeu de test pour les tables "A" et "B".

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
DROP TABLE IF EXISTS `A`;
CREATE TABLE IF NOT EXISTS `A` (
  `idA`   INT NOT NULL AUTO_INCREMENT,
  `value` INT NOT NULL,
  PRIMARY KEY (`idA`)
)
ENGINE = InnoDB;
 
DROP TABLE IF EXISTS `B`;
CREATE TABLE IF NOT EXISTS `B` (
  `idB`    INT NOT NULL AUTO_INCREMENT,
  `fk_idA` INT NOT NULL,
  `value`  INT NOT NULL,
  PRIMARY KEY (`idB`),
  INDEX `idfk_idA` (`fk_idA` ASC),
  INDEX `idvalue` (`value` ASC)
)
ENGINE = InnoDB;
 
INSERT INTO A SET `value`=1;
SELECT LAST_INSERT_ID() INTO @n;
INSERT INTO B SET `fk_idA`=@n, `value`=10;
INSERT INTO A SET `value`=2;
SELECT LAST_INSERT_ID() INTO @n;
INSERT INTO B SET `fk_idA`=@n, `value`=20;
INSERT INTO A SET `value`=3;
SELECT LAST_INSERT_ID() INTO @n;
INSERT INTO B SET `fk_idA`=@n, `value`=30;
INSERT INTO A SET `value`=4;
SELECT LAST_INSERT_ID() INTO @n;
INSERT INTO B SET `fk_idA`=@n, `value`=40;
INSERT INTO A SET `value`=5;
SELECT LAST_INSERT_ID() INTO @n;
INSERT INTO B SET `fk_idA`=@n, `value`=50;
 
DROP TABLE IF EXISTS `tmpTable`;
CREATE TEMPORARY TABLE `tmpTable`
  SELECT `B`.`fk_idA` AS `idA`
  FROM   `B`
  WHERE  `B`.`value` > 3;

On a :

Code:

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT `tmpTable`.`idA` AS `idA` FROM `tmpTable`;
+-----+
| idA |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
+-----+
5 rows in set (0.00 sec)

Je suppose que la requête ci-dessous :

Code:

1
2
3
UPDATE `A`
SET    `A`.`value` = 10
WHERE  `A`.`idA` IN (SELECT `tmpTable`.`idA` AS `idA` FROM `tmpTable`);

Est équivalente à :

Code:

1
2
3
UPDATE `A`
SET    `A`.`value` = 10
WHERE  `A`.`idA` IN (1, 2, 3, 4, 5);

Si cette supposition est correcte, alors je ne vois pas pourquoi il serait nécessaire d'indexer la table temporaire (tmpTable). Il suffit que A.idA soit indexé (ce qu est le cas).

Si ma supposition est fausse, alors j'aimerais que l'on m'explique car c'est un mystère...

Remarque : Par contre, dans le cas de la jointure (ci-dessous), je vois effectivement l'intérêt d'indexer la table temporaire.

Code:

1
2
3
4
UPDATE A
INNER JOIN B ON A.idA = B.fk_idA
SET A.value = 10
WHERE B.value > 3

Merci,

A+

13/09/2010, 08h32
CinePhil

OK tu as raison, j'avais considéré le idA de la sous-requête.

Ceci dit, la jointure me semble meilleure que la création d'une table temporaire.
13/09/2010, 20h50
WinNew

Citation:

Envoyé par CinePhil

OK tu as raison, j'avais considéré le idA de la sous-requête.

Ceci dit, la jointure me semble meilleure que la création d'une table temporaire.

Hello!

Je pense en effet que la solution de la jointure est préférable, du point de vue des performances et du point de vue conceptuel.

D'une façon générale, quel que soit le langage considéré (ici, le SQL) : S'il existe une construction syntaxique pour exprimer directement une idée, alors il est presque toujours préférable de l'utiliser, plutôt que d'utiliser une autre construction, nécessairement "indirecte".

A+