1. #1
    Membre averti
    Inscrit en
    mars 2004
    Messages
    1 545
    Détails du profil
    Informations forums :
    Inscription : mars 2004
    Messages : 1 545
    Points : 354
    Points
    354

    Par défaut Optimiser une requête très longue

    Bonjour,

    j'ai la requête suivante qui dure très longtemps (plus de 24h00) :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
     
    SELECT SUBSTR(DIGITS(A.NUNA),2,8),';',                     
               SUBSTR(DIGITS(A.SECEP),2,8),';',                    
               CHAR(A.ETMAJ),';',                                  
               SUBSTR(B.UPACC_INDIC,4,9) AS ID_TECH,';'            
        FROM SEEL.UPPRF A, SEEL.UPACC B                            
        WHERE A.SECEP = B.UPACC_NUNA                               
        AND B.UPACC_INDIC LIKE 'VRC%'                              
        AND B.UPACC_TYPE = '1'                                     
        AND A.ETMAJ > '20131201'                                   
        FOR FETCH ONLY WITH VR;
    La table UPPRF fait 887741 lignes et a pour index : JPPRFC


    la table UPACC fait 191108067 de lignes et a pour index : JPACCA et JPACCB


    est-il possible d'optimiser cette requête afin qu'elle dure moins longtemps ?

  2. #2
    Membre actif
    Profil pro
    Inscrit en
    juin 2008
    Messages
    138
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : juin 2008
    Messages : 138
    Points : 266
    Points
    266

    Par défaut

    La requête est "simple" donc je ne pense pas qu'on puisse l'optimiser elle même.

    Par contre, des indexations pourraient faciliter grandement le traitement.
    Déjà sur les clefs de jointures : UPPRF.SECEP & UPACC.UPACC_NUNA
    Ensuite sur les critères de recherche : UPPRF.ETMAJ & UPACC.UPACC_INDIC, UPACC.UPACC_TYPE

    Je ne me souviens si il faut mettre les critères de jointures avant où après ceux de sélection, de mémoire, je mettrais la sélection avant, mais il me semble qu'un "like" change la donne... (et j'avoue que je n'ai pas le temps de faire de test là dans l'immédiat ;) )
    De plus, je ne connais pas le "VR", ça a peut-être une importance...

    Donc déjà j'essaierais avec ces 2 index :
    - UPPRF : ETMAJ, SECEP
    - UPACC : UPACC_TYPE, UPACC_INDIC, UPACC_NUNA

    Après si tu peux facilement, essayes aussi avec les clefs de jointures en premier dans les index

    Pour finir, un explain permettrait de voir comment DB2 gère cette requête.

  3. #3
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    17 119
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 17 119
    Points : 39 735
    Points
    39 735
    Billets dans le blog
    1

    Par défaut

    Créez ces deux inex :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    CREATE INDEX X_UPACC_TYP_NDC_NMA ON SEEL.UPACC (UPACC_TYPE, UPACC_INDIC, UPACC_NUNA);
    CREATE INDEX X_UPPRF_MAJ_SCP_NMA ON SEEL.UPPRF (ETMAJ, SECEP, NUMA)
    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 * * * * *

  4. #4
    Membre averti
    Inscrit en
    mars 2004
    Messages
    1 545
    Détails du profil
    Informations forums :
    Inscription : mars 2004
    Messages : 1 545
    Points : 354
    Points
    354

    Par défaut

    Bonjour et merci pour votre aide.
    Effectivement, on est parti sur la création d'index.

  5. #5
    Membre averti
    Inscrit en
    mars 2004
    Messages
    1 545
    Détails du profil
    Informations forums :
    Inscription : mars 2004
    Messages : 1 545
    Points : 354
    Points
    354

    Par défaut

    Citation Envoyé par Pico----- Voir le message


    Pour finir, un explain permettrait de voir comment DB2 gère cette requête.
    Salut Pico, je ne sais pas si c'est c'est judicieux de faire un explain sur une requête aussi longue et gourmande ? Est-ce que ça consomme un tel explain ?

  6. #6
    Membre averti
    Inscrit en
    mars 2004
    Messages
    1 545
    Détails du profil
    Informations forums :
    Inscription : mars 2004
    Messages : 1 545
    Points : 354
    Points
    354

    Par défaut

    Citation Envoyé par SQLpro Voir le message
    Créez ces deux inex :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    CREATE INDEX X_UPACC_TYP_NDC_NMA ON SEEL.UPACC (UPACC_TYPE, UPACC_INDIC, UPACC_NUNA);
    CREATE INDEX X_UPPRF_MAJ_SCP_NMA ON SEEL.UPPRF (ETMAJ, SECEP, NUMA)
    A +
    Bonjour SQLpro,

    est-ce la création de ces index peut influer sur d'autres traitements qui justement n'utilisent pas ces index ?

    En gros on va peut-être améliorer cette requête, mais en dégrader d'autres sur ces tables ?

  7. #7
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    17 119
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 17 119
    Points : 39 735
    Points
    39 735
    Billets dans le blog
    1

    Par défaut

    Citation Envoyé par sam01 Voir le message
    est-ce la création de ces index peut influer sur d'autres traitements qui justement n'utilisent pas ces index ?

    En gros on va peut-être améliorer cette requête, mais en dégrader d'autres sur ces tables ?
    D'un point de vue logique, en aucun cas.

    Marginalement oui, il est possible de dégrader les performances de certaines requêtes, notamment de mise à jour. Mais le faible impact de cette dégradation doit être mis en regarde du coût fantastique que vous procure l'index. C'est le même problème que les médicaments. Les prendre inutilement peut entrainer des dégâts. Mais un médicament appliqué à une maladie c'est quand même un rapport bénéfice à perte généralement très positif.... Vous pouvez néanmoins vous passer de médicaments comme d'index !

    Réfléchissez à la problématique suivante : si je perds 5% de temps pour une mise à jour et que j'économise 99,99% du temps dans les SELECT... Qui est gagnant au final ? Parce que un ordinateur doit globalement fournir du temps à tous les demandeurs...

    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 * * * * *

  8. #8
    Membre chevronné Avatar de bernard59139
    Profil pro
    Administrateur de base de données
    Inscrit en
    octobre 2006
    Messages
    866
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations professionnelles :
    Activité : Administrateur de base de données

    Informations forums :
    Inscription : octobre 2006
    Messages : 866
    Points : 1 793
    Points
    1 793

    Par défaut

    la 1ere chose à faire, c'est un explain de la requête et analyser le resultat.

    sans cela, les conseils peuvent donner un résultat aléatoire.

    a+

  9. #9
    Expert éminent

    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    2 920
    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 : 2 920
    Points : 6 437
    Points
    6 437
    Billets dans le blog
    1

    Par défaut

    Bonjour,

    Désolé pour cette réponse tardive, mais, avant de créer des index supplémentaires, il faut s'assurer que
    - A.SECEP et B.UPACC_NUNA sont bien des colonnes de même type et de même longueur,
    - le nombre de valeurs distinctes de ces colonnes dans leurs tables respectives est significatif
    Sans quoi les nouveaux index seront inutiles et pénaliseront à la marge les autres requetes en MàJ

  10. #10
    Expert éminent

    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    2 920
    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 : 2 920
    Points : 6 437
    Points
    6 437
    Billets dans le blog
    1

    Par défaut

    Citation Envoyé par sam01 Voir le message
    Salut Pico, je ne sais pas si c'est c'est judicieux de faire un explain sur une requête aussi longue et gourmande ? Est-ce que ça consomme un tel explain ?
    Un explain est toujours une bonne chose, il ne consomme rien, car la requete n'est pas exécutée lors de l'explain, seule la stratégie d'accès est calculée
    Attention toutefois, un explain n'est pas une science tout à fait exacte, il arrive parfois que la stratégie effectivement appliquée lors de l'exécution diffère de celle de l'explain
    Attention aussi a bien exécuter l'explain sur la plate-forme où le problème est décelé : la stratégie dépend des stats et des index, qui peuvent être différents d'une plate forme à une autre (test, recette, pré-prod...)

  11. #11
    Membre averti
    Inscrit en
    mars 2004
    Messages
    1 545
    Détails du profil
    Informations forums :
    Inscription : mars 2004
    Messages : 1 545
    Points : 354
    Points
    354

    Par défaut

    Bonjour escatefigue,

    voici ce que j'ai dans la colonne détail pour la colonne B.UPACC_NUNA :

    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
     
    CMD: ________                                                                
     COLUMN: UPACC_NUNA                                                          
      TABLE: UPACC                                                               
           :                                                                     
      COLNO: 10     OF 11                  TYPE: TABLE                           
      OWNER: REEL                     CREATEDBY: REEL                            
     
    COLTYPE: INTEGER            NULLS  : N                                       
    LENGTH : 4                  UPDATES: N                 PERIOD  : N/A         
    SCALE  : 0                  DEFAULT: N                 COLCARD : 20,447,232  
    KEYSEQ : 0                  FLDPROC: N                 SUBTYPE :             
     
     
    et la suite 
     
    SCHEMA : SYSIBM            TYPENAME:                   SRC TYPE: BUILT-IN     
    LENGTH2: 0                COLSTATUS: COMPLETE                                 
    HIGH2KEY :       99972679                                                     
    LOW2KEY  :              2                                                     
    STATSTIME: 2017-04-21-20.46.04.995889   CREATEDTS: 0001-01-01-00.00.00.000000 
    ALTEREDTS: 0001-01-01-00.00.00.000000                                         
    HIDDEN : N               STATS FORM: N/A             RELCREATED:              
    CCSID  : 0              PARTKEY SEQ: 3              PARTKEY ORD: A            
     
    SECTIONS REQUIRING FULL DETAIL HAVE BEEN BYPASSED
    et pour la colonne A.SECEP

    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
     
     
           :                                                                      
      COLNO: 2      OF 6                   TYPE: TABLE                            
      OWNER: REEL                     CREATEDBY: REEL                             
     
    COLTYPE: INTEGER            NULLS  : N                                        
    LENGTH : 4                  UPDATES: Y                 PERIOD  : N/A          
    SCALE  : 0                  DEFAULT: Y                 COLCARD : 606,208      
    KEYSEQ : 0                  FLDPROC: N                 SUBTYPE :              
     
     
    et la suite 
     
    SCHEMA : SYSIBM            TYPENAME: INTEGER           SRC TYPE: BUILT-IN     
    LENGTH2: 0                COLSTATUS: COMPLETE                                 
    HIGH2KEY :       57718973                                                     
    LOW2KEY  :           3786                                                     
    STATSTIME: 2015-08-07-23.36.50.047640   CREATEDTS: 2014-09-08-11.04.49.766716 
    ALTEREDTS: 2014-09-08-11.04.49.766716                                         
    HIDDEN : N               STATS FORM: N/A             RELCREATED: DB2v10       
    CCSID  : 0              PARTKEY SEQ: N/A            PARTKEY ORD: N/A          
     
    SECTIONS REQUIRING FULL DETAIL HAVE BEEN BYPASSED
    Peut-on considérer qu'elles sont de même types ?

  12. #12
    Expert éminent

    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    2 920
    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 : 2 920
    Points : 6 437
    Points
    6 437
    Billets dans le blog
    1

    Par défaut

    Bonjour,

    Oui, elles sont toutes deux de type integer(4), et le nombre de valeurs distinctes est suffisant pour qu'un index soit pertinent

    une jointure est donc sargable en cas d'index sur l'une et l'autre colonne

    Créer des index est donc une bonne solution

    Pensez à mettre à jour les stats, puis à rebinder vos package avant de relancer votre traitement

  13. #13
    Membre averti
    Inscrit en
    mars 2004
    Messages
    1 545
    Détails du profil
    Informations forums :
    Inscription : mars 2004
    Messages : 1 545
    Points : 354
    Points
    354

    Par défaut

    Merci escartefigue.
    Je viens de faire un explain de la requête couteuse .
    Pour la PLAN_TABLE j'ai :

    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
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
     
    --------------------------------------------------------------------------------
             !    QUERYNO     ! QBLOCKNO  !  PLANNO   !     !      !     !          
    --------------------------------------------------------------------------------
    DSNTEP2  !          88888 !         1 !         1 ! 000 !      ! 000 ! UPACC    
    DSNTEP2  !          88888 !         1 !         2 ! 000 !      ! 001 ! UPPRF    
    --------------------------------------------------------------------------------
     
    --------------------  
    ! ACCESSTYPE !     !  
    --------------------  
    ! I          ! 001 !  
    ! R          ! 000 !  
    --------------------  
     
     
    et 
     
    -------------------------------------------------------------------------
                       ! INDEXONLY ! SORTN_UNIQ ! SORTN_JOIN ! SORTN_ORDERBY 
    -------------------------------------------------------------------------
    JPACCA             ! Y         ! N          ! N          ! N             
                       ! N         ! N          ! N          ! N             
    -------------------------------------------------------------------------
     
    -------------------------------------------   
    ! SORTN_GROUPBY ! SORTC_UNIQ ! SORTC_JOIN !   
    -------------------------------------------   
    ! N             ! N          ! N          !   
    ! N             ! N          ! N          !   
    -------------------------------------------   
     
    et
     
    ! SORTC_ORDERBY ! SORTC_GROUPBY ! TSLOCKMODE ! PREFETCH !     !
    ---------------------------------------------------------------
    ! N             ! N             !   N        !          ! 002 !
    ! N             ! N             !   N        ! S        ! 001 !
    ---------------------------------------------------------------
     
    ! COLUMN_FN_EVAL !     !     !     !     !     !     !  
    ------------------------------------------------------  
    !                ! ?   ! ?   ! ?   ! ?   ! ?   ! ?   !  
    !                ! ?   ! ?   ! ?   ! ?   ! ?   ! ?   !  
    ------------------------------------------------------  
     
    et
     
    --------------------------------------------------------------------
    ! PARALLELISM_MODE !     ! PAGE_RANGE ! JOIN_TYPE ! WHEN_OPTIMIZE ! 
    --------------------------------------------------------------------
    ! ?                ! ?   !            !           !               ! 
    ! ?                ! ?   !            !           !               ! 
    --------------------------------------------------------------------
     
    -------------------------------------------------------+ 
    ! QBLOCK_TYPE !    TIMESTAMP     ! PARENT_QBLOCKNO !   ! 
    -------------------------------------------------------+ 
    ! SELECT      ! 2017042514354107 !         0       ! T ! 
    ! SELECT      ! 2017042514354107 !         0       ! T ! 
    -------------------------------------------------------+
    Pour la DSN_STATEMNT_TABLE


    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
     
     
    +---------------------------------+  
    !     PROCMS     !     PROCSU     !  
    +---------------------------------+  
    !            836 !          69624 !  
    +---------------------------------+

  14. #14
    Expert éminent

    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    2 920
    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 : 2 920
    Points : 6 437
    Points
    6 437
    Billets dans le blog
    1

    Par défaut

    Je suppose que vous avez fait cet explain avant d'ajouter les index, car l'access-type R sur UPPRF signifie un table scan, soit une lecture séquentielle de toute la table
    Créez les index comme préconisé et relancez l'explain

  15. #15
    Membre averti
    Inscrit en
    mars 2004
    Messages
    1 545
    Détails du profil
    Informations forums :
    Inscription : mars 2004
    Messages : 1 545
    Points : 354
    Points
    354

    Par défaut

    Oui effectivement, l'ajout d'index n' pas encore été fait.
    Dès que ça sera fait, je ferais un autre explain. Heureusement que le scan table se fait sur la plus petite table....

Discussions similaires

  1. Requête sql avec test d'une variable très longue
    Par missmiss89 dans le forum PHP & MySQL
    Réponses: 5
    Dernier message: 24/06/2011, 14h25
  2. Requête très longue sur une table très simple
    Par kragenskul dans le forum Requêtes
    Réponses: 6
    Dernier message: 16/06/2009, 14h28
  3. Une requête très longue à l'exécution
    Par mouaa dans le forum Langage SQL
    Réponses: 8
    Dernier message: 30/05/2008, 07h57
  4. Optimisation d'une requête très lourde
    Par Benji76 dans le forum Requêtes
    Réponses: 9
    Dernier message: 24/02/2007, 11h35
  5. Optimiser une requête SQL d'un moteur de recherche
    Par kibodio dans le forum Langage SQL
    Réponses: 2
    Dernier message: 06/03/2005, 20h55

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