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
    Inscrit en
    juillet 2008
    Messages
    35
    Détails du profil
    Informations forums :
    Inscription : juillet 2008
    Messages : 35
    Points : 35
    Points
    35
    Par défaut 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
    Homme Profil pro
    Responsable Datas
    Inscrit en
    janvier 2009
    Messages
    4 050
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Responsable Datas

    Informations forums :
    Inscription : janvier 2009
    Messages : 4 050
    Points : 9 465
    Points
    9 465
    Par défaut
    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

    Profil pro
    dba
    Inscrit en
    janvier 2010
    Messages
    5 416
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : janvier 2010
    Messages : 5 416
    Points : 12 490
    Points
    12 490
    Par défaut
    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

    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    5 316
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 5 316
    Points : 15 769
    Points
    15 769
    Billets dans le blog
    1
    Par défaut
    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

    Homme Profil pro
    Consultant Teradata
    Inscrit en
    septembre 2008
    Messages
    7 909
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Consultant Teradata

    Informations forums :
    Inscription : septembre 2008
    Messages : 7 909
    Points : 15 805
    Points
    15 805
    Par défaut
    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
    Inscrit en
    juillet 2008
    Messages
    35
    Détails du profil
    Informations forums :
    Inscription : juillet 2008
    Messages : 35
    Points : 35
    Points
    35
    Par défaut 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) :

    Nom : Capture.JPG
Affichages : 38
Taille : 41,2 Ko

    	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é
    Profil pro
    Inscrit en
    août 2008
    Messages
    2 834
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : août 2008
    Messages : 2 834
    Points : 5 575
    Points
    5 575
    Par défaut
    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
    Inscrit en
    juillet 2008
    Messages
    35
    Détails du profil
    Informations forums :
    Inscription : juillet 2008
    Messages : 35
    Points : 35
    Points
    35
    Par défaut Merci!
    Bonjour

    Merci skuatamad

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

  9. #9
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    19 405
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 19 405
    Points : 46 111
    Points
    46 111
    Par défaut
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

Discussions similaires

  1. [11gR2] INSERT beaucoup trop long
    Par tchorlz dans le forum SQL
    Réponses: 3
    Dernier message: 03/07/2014, 16h27
  2. Stopper exécution si trop long
    Par wwave dans le forum Général Java
    Réponses: 1
    Dernier message: 04/10/2007, 22h23
  3. Oracle 8 : INSERT SELECT avec NOT IN trop long
    Par davy.g dans le forum Oracle
    Réponses: 6
    Dernier message: 03/07/2007, 12h33
  4. Insert long à s'exécuter
    Par bencot dans le forum Oracle
    Réponses: 3
    Dernier message: 21/11/2006, 13h05
  5. [VB6]Message d'erreur si le temps d'exécution est trop long
    Par Asdorve dans le forum VB 6 et antérieur
    Réponses: 16
    Dernier message: 14/09/2006, 17h43

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