Optimiser une requête très longue

**sam01** · 04/04/2017, 11h05

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 ?

**Pico-----** · 04/04/2017, 14h22

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.

**SQLpro** · 05/04/2017, 12h06

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 +

**sam01** · 13/04/2017, 16h09

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

**sam01** · 14/04/2017, 13h34

Envoyé par Pico-----

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 ?

**sam01** · 14/04/2017, 13h58

Envoyé par SQLpro

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 ?

**SQLpro** · 14/04/2017, 16h53

Envoyé par sam01

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 +

**bernard59139** · 15/04/2017, 08h29

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+

**escartefigue** · 20/04/2017, 14h45

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

**escartefigue** · 20/04/2017, 14h50

Envoyé par sam01

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...)

**sam01** · 24/04/2017, 14h09

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 ?

**escartefigue** · 25/04/2017, 09h42

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

**sam01** · 25/04/2017, 14h50

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 !  
+---------------------------------+

**escartefigue** · 25/04/2017, 15h07

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

**sam01** · 26/04/2017, 16h27

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

Optimiser une requête très longue

DB2

Discussions similaires

Partager

Partager