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

Langage SQL Discussion :

INSERT INTO trop long à s'exécuter -> a optimiser


Sujet :

Langage SQL

  1. #1
    Nouveau membre du Club
    INSERT INTO trop long à s'exécuter -> a optimiser
    Bonjour,

    Ce post s'adresse principalement aux développeurs SQL, mais bien sur aussi aux dbas.

    Je me suis lancé un défi : démontrer à mon client que je peux lui faire gagner un temps certain dans l'exécution de ses traitements.

    Mon client utilise cette requête :

    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
    INSERT
    INTO    toto
            (
                    id,
                    type
            )
            (
                    SELECT  t.id,
                            297609449
                    FROM    titi t
                    WHERE   EXISTS
                            (
                                    SELECT 1 FROM titi t7 WHERE t7.type IN (-1684476324)
                            )
                            AND t.type IN (-1684476324)
            );



    J'ai remplacé le nom des colonnes et des tables dans un soucis de confidentialité.

    Je pense que cette requête est largement optimisable.


    Pouvez vous m'aider à la réécrire svp?

    Je vous remercie d'avance pour votre aide.

    JOguess~~

  2. #2
    Expert éminent sénior
    Bonjour,
    Je ne comprends pas l'utilité de la sous-requête: elle utilise la même table, elle n'est pas corrélée, et le critère de la requête principal est le même que celui de la sous-requête.
    A moins que le renommage ne soit "un peu violent" ici.
    Dans ce cas tu peux remplacer la sous-requête par une simple jointure. Peut-être que la requête sera un peu plus rapide.

    Tatayo.

  3. #3
    Modérateur

    bonjour,

    je pense en effet que l'anonymisation a été un peu trop vite faite, sinon, le test EXISTS est même carrément inutile, en raison de la deuxième condition de la clause WHERE.

  4. #4
    Expert éminent sénior
    Peut être aussi que certains vieux optimiseurs traitent moins bien IN avec une seule valeur dans la liste que =.
    à vérifier avec un explain

  5. #5
    Modérateur

    Citation Envoyé par joguess Voir le message
    Je me suis lancé un défi : démontrer à mon client que je peux lui faire gagner un temps certain dans l'exécution de ses traitements.
    Du coup vous lancez plutôt un défi au forum qu'à vous-même


    Sur le fond, en phase avec tout ce qui a été dit ci-dessous, mais si vous voulez démontrez quoi que ce soit à votre client il faut d'abord collecter l'impact de cette requête : plan d'exécution, temps de parsing, temps d'exécution, CPU, IO, nombre d'exécutions.

    Et aussi dans quel contexte cette requête est générée et exécutée.

  6. #6
    Nouveau membre du Club
    merci de votre réactivité
    Bonjour,

    Modérateur vous avez raison, je vous fournis donc les informations suivantes :

    pour la requête suivante dont la structure (tout aussi pourrie) est similaire à celle précédemment citée. :

    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
    INSERT
    INTO    TOTO
            (
                    id,
                    type
            )
            (
                    SELECT  t.id,
                            297950818
                    FROM    TITI t
                    WHERE   EXISTS
                            (
                                    SELECT  1
                                    FROM    TITI t7
                                    WHERE   t7.type IN (90733904,90813843,90819624,90821970,90955930,90994311,91102236,91106126,91126766,687919715,-1809954855,-1684426764,-106298800,-106282981,-106278063,-1364941731,-257286118,-257275642,903395045,1566915833,1566922066,1566931654,-106222516,-106210824,-106131413,462247857,-106123462,462249968,-257319085,-106098176,-106096517,-106089565,-106083793,-106075607,-106058048,-106033557,91227914,91229658,91237123,231592713,1478817095,1478827205,1478822192,1478827734,1478823791,1478831006,-1364961518,91068793,-1364960659,91068653,-1364960011,-1364957281,90929521,91056886,91060378,91063284,91063662,91065257,231601464,231604451,231597724,231601610,231607574,1478854503)
                            )
                            AND t.type IN (90733904,90813843,90819624,90821970,90955930,90994311,91102236,91106126,91126766,687919715,-1809954855,-1684426764,-106298800,-106282981,-106278063,-1364941731,-257286118,-257275642,903395045,1566915833,1566922066,1566931654,-106222516,-106210824,-106131413,462247857,-106123462,462249968,-257319085,-106098176,-106096517,-106089565,-106083793,-106075607,-106058048,-106033557,91227914,91229658,91237123,231592713,1478817095,1478827205,1478822192,1478827734,1478823791,1478831006,-1364961518,91068793,-1364960659,91068653,-1364960011,-1364957281,90929521,91056886,91060378,91063284,91063662,91065257,231601464,231604451,231597724,231601610,231607574,1478854503)
            );



    nombre de ligne dans la table TITI : 12 567 135

    plan d'exécution :

    => access full de la table TITI ; coût 47K
    => passage par un index range qui pointe sur la colonne type de la table TITI ; coût : 4

    la requête ne s'exécute qu'une seule fois.

    Voici les statistiques d'exécution (voir pièce jointe) (exécrable selon moi) :



    	Total	Per Execution	Per Row
    Executions	1	1	<0.01
    Elapsed Time (sec)	758.45	758.45	<0.01
    CPU Time (sec)	26.28	26.28	<0.01
    Buffer Gets	3,985,442	3,985,442.00	11.35
    Disk Reads	300,492	300,492.00	0.86
    Direct Writes	0	0.00	0.00
    Rows	351,151	351,151.00	1
    Fetches	0	0.00	0.00

    J'ai 16 Go en SGA.
    Le buffer cache monte à 12Go pendant le traitement.

    J'ai 4 cpu et une base en 12.2.

    Le traitemant lancée n'est pas parallèlisé, comprendre une seule session oracle lance les requête SQL.

    Cette requête est issue d'un traitement encapsulée qui est lancée dans le cadre d'une migration applicative.
    Donc il faut que je fasse le forcing poliement auprès de l'éditeur pour qu'il puisse modifier ces requêtes merdiques.
    Mes collègues autour de moi n'y comprennent rien.

    De mon point de vue c'est plus un problème de conception qu'une optimisation...
    Je suis d'accord avec ce qui a été énoncé plus haut, mais je suis un peu une chèvre en SQL...

    J'aimerais être capable de teser la même requête améliorée sur un serveur proto avec la même table, meme nombre de ligne, même config etc...

    Joguess~

  7. #7
    Expert confirmé
    Si vous ne vous trompez pas dans l'anonymisation des requêtes en sur simplifiant, ça veut dire que les EXISTS sont inutiles.
    Pour tester faites :
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    create table TOTO2 (id,type) as
    SELECT t.id, 297950818
      FROM TITI t
     where t.type IN (90733904,90813843,90819624,90821970,90955930,90994311,91102236,91106126,91126766,687919715,-1809954855,-1684426764,-106298800,-106282981,-106278063,-1364941731,-257286118,-257275642,903395045,1566915833,1566922066,1566931654,-106222516,-106210824,-106131413,462247857,-106123462,462249968,-257319085,-106098176,-106096517,-106089565,-106083793,-106075607,-106058048,-106033557,91227914,91229658,91237123,231592713,1478817095,1478827205,1478822192,1478827734,1478823791,1478831006,-1364961518,91068793,-1364960659,91068653,-1364960011,-1364957281,90929521,91056886,91060378,91063284,91063662,91065257,231601464,231604451,231597724,231601610,231607574,1478854503);


    Ensuite vous pouvez tester le contenu de tot et de toto2 avec minus :
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    select * from toto
      minus
     select * from toto2;
     
     select * from toto2
      minus
     select * from toto;
     
     select count(*) from toto
      minus
     select count(*) from toto2;

  8. #8
    Nouveau membre du Club
    Merci!
    Bonjour

    Merci skuatamad

    vous m'avez été d'une grande aide !!!
    Joguess

  9. #9
    Rédacteur

    D'autre part, quand vous avez un IN avec beaucoup de valeurs scalaires, il vaut mieux passer par une table temporaire dans laquelle vous stockez ces ID et que vous indexez de manière unique, et en rectifiant la requête pour effectuer une jointure avec les bons index…..

    A +
    Cette signature n'a pas pu être affichée car elle comporte des erreurs.

###raw>template_hook.ano_emploi###