Discussion :

[Matworms]

Bonjour,

On m'a demandé de voir comment optimiser une requête SQL (pour le boulot), mais je ne suis pas vraiment expert et j'ai l'impression que je vais y passer mes journées... Pour le contexte, c'est une requête qui attaque une base de production (ou plus exactement une copie de la base de production) et je ne suis pas DBA donc aucun droit particulier. Ce qui signifie donc qu'il s'agit d'optimiser la requête et uniquement la requête.

Voilà la requête en question :

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
 
select distinct H.DANEG,
                H.COMAR,
                H.COINF,
                H.CNACT,
                H.LNACT,
                H.NUCON,
                decode(H.CSENS, 'A', 'B', 'S'),
                H.CSOPT,
                H.QTCCP,
                H.CMECH,
                H.CAECH,
                H.DAECA,
                H.CODEV,
                H.MTSNA,
                H.MTNEG,
                decode(H.COMAR, 'EUREX', decode(H.COINF, 'HSBC EUR', 'HL1235', 'HSBCEURC', 'CL1234'), 'AEX', 'HL1236', 'ADEX', 'HL1238', 'HL1234')
from ERESHX H
where H.DANEG = (select
                 distinct DATRA
                 from DATRAI
                 where COMAR = 'EUREX');

Note :
SELECT DISTINCT DATRA FROM DATRAI WHERE COMAR = 'EUREX' renvoie une seule ligne (une date).

Elle n'est pas de moi cette requête, pour les observateurs, elle donne une indication sur l'entreprise pour laquelle je travaille (je suis en stage) et le type de données traitées...

Au niveau des temps de traitement, la requête que j'ai copiée plus haut a un temps d'exécution beaucoup trop long (près d'une heure), alors que si je remplace le second SELECT DISTINCT par une date "en dur" (to_date('16/06/2008', 'DD/MM/YYYY') par exemple), la requête s'exécute en 5 minutes environ.

En ce qui concerne la volumétrie des tables, la voici :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
SQL> select count(*) from ERESHX;
 
  COUNT(*)
----------
   9315190
 
SQL> select count(*) from DATRAI;
 
  COUNT(*)
----------
        31

Et si ça a une importance, la base est sous Oracle 9i.

Merci pour votre aide

[philcero]

As-tu un de tes indexs sur la table ERESHX qui commence par la champs DANEG ?

Autre axe, Envoyer le DISTINCT un cran plus haut.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
SELECT DISTINCT
    DANEG,COMAR,COINF,CNACT,LNACT,NUCON,CSENS,CSOPT,QTCCP,CMECH,CAECH,DAECA,CODEV,MTSNA,MTNEG,COMAR
FROM
(
SELECT    DANEG,COMAR,COINF,CNACT,LNACT,NUCON,
    decode(CSENS, 'A', 'B', 'S'),
    CSOPT,QTCCP,CMECH,CAECH,DAECA,CODEV,MTSNA,MTNEG,
    decode(COMAR, 'EUREX', decode(COINF, 'HSBC EUR', 'HL1235', 'HSBCEURC', 'CL1234'), 'AEX', 'HL1236', 'ADEX', 'HL1238', 'HL1234')
    FROM ERESHX H
    WHERE DANEG = (SELECT DISTINCT DATRA FROM DATRAI WHERE COMAR = 'EUREX')
);

[Matworms]

Merci pour ta réponse philcero.

Je viens tout juste d'apprendre que ERESHX n'est pas une table, mais une vue résultant d'une requête de 250 lignes environ... (je l'ai sous les yeux)

As-tu un de tes indexs sur la table ERESHX qui commence par la champs DANEG ?

Compte tenu de ma découverte récente, je dirais que c'est un peu plus compliqué que ça, et que faire la liste des indexes m'a l'air d'être une tâche peu aisée (si j'ai bien vu la vue fait intervenir des jointures entre 12 tables).

Autre axe, Envoyer le DISTINCT un cran plus haut.

Je suis en train de tester le code que tu m'as donné, mais j'ai dû faire quelques adaptations (mettre les decode() dans la partie haute) :

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
 
SELECT DISTINCT
  DANEG,
  COMAR,
  COINF,
  CNACT,
  LNACT,
  NUCON,
  decode(CSENS, 'A', 'B', 'S'),
  CSOPT,
  QTCCP,
  CMECH,
  CAECH,
  DAECA,
  CODEV,
  MTSNA,
  MTNEG,
  decode(COMAR, 'EUREX', decode(COINF, 'HSBC EUR', 'HL1235', 'HSBCEURC', 'CL1234'), 'AEX', 'HL1236', 'ADEX', 'HL1238', 'HL1234')
FROM
(
  SELECT DANEG,
         COMAR,
         COINF,
         CNACT,
         LNACT,
         NUCON,
         CSENS,
         CSOPT,
         QTCCP,
         CMECH,
         CAECH,
         DAECA,
         CODEV,
         MTSNA,
         MTNEG
  FROM ERESHX
  WHERE DANEG = (SELECT DISTINCT DATRA FROM DATRAI WHERE COMAR = 'EUREX')
);

La requête tourne en ce moment... depuis 5 minutes environ, wait and see. Je reviendrai pour donner les résultats obtenus.

[vlah79]

Bonjour,

As-tu essayé le code suivant :

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
 
SELECT DISTINCT H.DANEG,
                H.COMAR,
                H.COINF,
                H.CNACT,
                H.LNACT,
                H.NUCON,
                decode(H.CSENS, 'A', 'B', 'S'),
                H.CSOPT,
                H.QTCCP,
                H.CMECH,
                H.CAECH,
                H.DAECA,
                H.CODEV,
                H.MTSNA,
                H.MTNEG,
                decode(H.COMAR, 'EUREX', decode(H.COINF, 'HSBC EUR', 'HL1235', 'HSBCEURC', 'CL1234'), 'AEX', 'HL1236', 'ADEX', 'HL1238', 'HL1234')
FROM ERESHX H
,    DATRAI D
WHERE H.DANEG = D.DATRA
AND     D.COMAR = 'EUREX'

Tu éviteras de faire appel x fois à ta sous requête.
Et le plan d'exécution devrait en être amélioré.


pistes alternatives :
- Vérifier la volumétrie de ces tables, forcément trés grosse, pour justifier l'appel à un expert.
- Utilisation d'une commande bulk, pour traiter la requête en plusieurs lots -> diminution de la mémoire utilisée pour l'opération - ce qui évite des effets de saturation) -> demander conseil à une personne expérimentée.

[Matworms]

Merci vlah79.

J'avais envisagé quelque chose dans le genre, je suis en train de tester ta requête, j'ai stoppé l'autre qui tournait depuis plus de 40 minutes. Cela dit, il faut que je vérifie que le nombre de lignes renvoyées est correct, je fais un retour dès que c'est terminé.

Je vais étudier d'autres pistes aussi, mais j'ai plutôt l'impression qu'on m'a donné ce travail plus pour m'occuper l'esprit (et les doigts) que parce que c'est d'une nécessité absolue. Mais comme je suis quelqu'un de sérieux, je cherche une solution acceptable...

[vlah79]

Si tu disposes de TOAD ou de SQL DEVELOPER, essaye de récupérer le plan d'execution de ta requête.

Cela aide toujours pour voir ou la requete perd du temps.