IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Requêtes MySQL Discussion :

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


Sujet :

Requêtes MySQL

  1. #1
    Membre régulier
    Profil pro
    Développeur informatique
    Inscrit en
    Mars 2008
    Messages
    80
    Détails du profil
    Informations personnelles :
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Industrie

    Informations forums :
    Inscription : Mars 2008
    Messages : 80
    Points : 114
    Points
    114
    Par défaut Performance et clause WHERE.. IN (SELECT ..).
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    UPDATE ... WHERE ... IN (SELECT ...);
    Par exemple :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    Server version: 5.1.49-0.dotdeb.0 (Debian)
    Merci à tous,

    A+

  2. #2
    Modérateur

    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    Août 2006
    Messages
    16 793
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : Août 2006
    Messages : 16 793
    Points : 34 024
    Points
    34 024
    Billets dans le blog
    14
    Par défaut
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  3. #3
    Membre régulier
    Profil pro
    Développeur informatique
    Inscrit en
    Mars 2008
    Messages
    80
    Détails du profil
    Informations personnelles :
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Industrie

    Informations forums :
    Inscription : Mars 2008
    Messages : 80
    Points : 114
    Points
    114
    Par défaut
    Salut CinePhil,

    Je te remercie pour les informations que tu m'apportes.

    Citation Envoyé par CinePhil Voir le message
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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 Voir le message
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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+

  4. #4
    Modérateur

    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    Août 2006
    Messages
    16 793
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : Août 2006
    Messages : 16 793
    Points : 34 024
    Points
    34 024
    Billets dans le blog
    14
    Par défaut
    Citation Envoyé par WinNew Voir le message
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  5. #5
    Membre régulier
    Profil pro
    Développeur informatique
    Inscrit en
    Mars 2008
    Messages
    80
    Détails du profil
    Informations personnelles :
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Industrie

    Informations forums :
    Inscription : Mars 2008
    Messages : 80
    Points : 114
    Points
    114
    Par défaut
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    UPDATE `A`
    SET    `A`.`value` = 10
    WHERE  `A`.`idA` IN (SELECT `tmpTable`.`idA` AS `idA` FROM `tmpTable`);
    Est équivalente à :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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+

  6. #6
    Modérateur

    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    Août 2006
    Messages
    16 793
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : Août 2006
    Messages : 16 793
    Points : 34 024
    Points
    34 024
    Billets dans le blog
    14
    Par défaut
    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.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  7. #7
    Membre régulier
    Profil pro
    Développeur informatique
    Inscrit en
    Mars 2008
    Messages
    80
    Détails du profil
    Informations personnelles :
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Industrie

    Informations forums :
    Inscription : Mars 2008
    Messages : 80
    Points : 114
    Points
    114
    Par défaut
    Citation Envoyé par CinePhil Voir le message
    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+

Discussions similaires

  1. Réponses: 1
    Dernier message: 07/03/2012, 11h34
  2. clause WHERE avec SELECT
    Par nymus7 dans le forum Débuter
    Réponses: 2
    Dernier message: 23/11/2010, 16h18
  3. Clause WHERE pour SELECT entre deux dates
    Par arogues dans le forum Langage SQL
    Réponses: 8
    Dernier message: 17/04/2008, 18h42
  4. Réponses: 8
    Dernier message: 16/08/2006, 15h39
  5. Select dans clause Where
    Par Bisûnûrs dans le forum Requêtes
    Réponses: 2
    Dernier message: 11/08/2006, 12h50

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo