Optimisation de requete sql

[gilles98800]

Bonjour
C'est ma première demande....
J'ai un souci de performance avec cette requête stockée.

Code :

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
 
BEGIN 
DECLARE RROL CHARACTER(20);
DECLARE RART CHARACTER(12);
DECLARE SQLCODE INTEGER DEFAULT 0;
 
DECLARE REC CURSOR FOR 
    SELECT ID_FACTURE, ID_ARTICLE 
    FROM DBPROD.T_ARCHIVES
      WHERE AA_DATARCH IS NULL ;
 
OPEN REC;
 
FETCH NEXT FROM REC INTO RROL, RART;
 
label0 : WHILE ( SQLCODE=0 ) DO
 
     /*==== Archivage  */
     /* T_HISTACTU */
    INSERT INTO DBARCHIVE.T_HISTACTU
       ( SELECT * FROM DBPROD.T_HISTACTU WHERE OID_OPERA IN  (
             SELECT OID_OPERA FROM DBPROD.TJ_POURS WHERE ID_FACTURE = RROL AND ID_ARTICLE = RART ) ) ;
 
      /*==== Suppression */
     /* T_HISTACTU */
     DELETE FROM DBPROD.T_HISTACTU WHERE OID_OPERA IN  (
        SELECT OID_OPERA FROM DBPROD.TJ_POURS WHERE ID_FACTURE = RROL AND ID_ARTICLE = RART ) ;
 
 
   FETCH NEXT FROM REC INTO RROL, RART;
 
END WHILE label0 ;
 
CLOSE REC;

Il s'agit de déplacer des rows d'une table vers une table identique mais dans une base d'archives. Les rows insérées dans la table de la base archives viennent de la table production d'où elles sont retirées immédiatement après par delete.
Le tout tourne sous db2 et iserie 520 avec version OS 5.4. Cette requête dure en moyenne 8 heures. En mettant un index sur la table C, je suis descendu a 5 heures mais je trouve que cela fait encore beaucoup.
Ce n'est pas moi qui ait développé ces requêtes mais je les "récupère" suite à un départ. Je ne suis pas un cador en SQL. Merci aux bonne volontés de m'orienter vers certaines pistes (si c'est possible) pour tenter d'améliorer les temps d'exploitation. Là je sèche un peu. Les tables ne sont pas énormes TABLE A = 75400 et TABLEC=320000

[punkoff]

bonjour,

As-tu pu identifier la partie de ta procédure qui étaient la plus longue ? (DBMON)

Sinon pourrai-tu indiquer précisément les index en place sur tes tables.

Et enfin quand tu passe cette proc-stock es-tu en concurrence avec d'autre process ?

Qu'appel-tu table A et table C sachant que tu as 3 tables distincts ?


edit:
- Est-ce normal que tu n'update pas la date d'archivage dans T_ARCHIVES apres ton process ?
- Une étape d'archivage va bouger en gros combien de % de lignes de table de prod ?

[K2R400]

Tout d'abord, bienvenu sur ce forum.

Avec une volumétrie comme la tienne et vu ce que tu veux faire on va rester en dessous des 5 secondes.
Let's go (ne pas sauter d'étapes) :


1) On va s'assurer que c'est bien le moteur SQE qui traite les requêtes et non le bieux moteur CQE.

Supprime le fichier QUSRSYS/QAQQINI s'il existe.
Fait un CRTDUPOBJ de QAQQINI de QSYS vers QUSRSYS en copiant tout (+ contraintes).
sous STRSQL tu fais :

Code :

1
2
 
UPDATE QUSRSYS/QAQQINI SET QQVAL = '*YES' WHERE QQPARM = 'IGNORE_DERIVED_INDEX'

2) On va revoir le contenu de ta procédure
Tes "IN" successifs ne sont pas bons, il faut travailler plutôt avec des jointures.
Mais dans ton cas, personellement je n'aurais pas déclaré de curseur, un seul INSERT et un seul DELETE sous commiment control :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
     SET TRANSACTION ISOLATION LEVEL RR;
     /*==== Archivage  */
     /* T_HISTACTU */
    INSERT INTO DBARCHIVE.T_HISTACTU
       ( SELECT T1.* FROM DBPROD.T_HISTACTU T1, 
			  DBPROD.TJ_POURS   T2, 
			  DBPROD.T_ARCHIVES T3   
		    WHERE T1.OID_OPERA  = T2.OID_OPERA
		      AND (T2.ID_FACTURE, T2.ID_ARTICLE) = (T3.ID_FACTURE, T3.ID_ARTICLE);
 
 
 
      /*==== Suppression */
     /* T_HISTACTU */
     DELETE FROM DBPROD.T_HISTACTU T1
	   WHERE EXISTS  (
        SELECT *	 FROM DBPROD.TJ_POURS   T2,
			      DBPROD.T_ARCHIVES T3   
 			WHERE T1.OID_OPERA = T2.OID_OPERA
		          AND (T2.ID_FACTURE, T2.ID_ARTICLE) = (T3.ID_FACTURE, T3.ID_ARTICLE);
 
      /*==== Validation */
     COMMIT;

3) Maintenant il faut s'assurer que tu es les bons index :
- Sur les tables DBPROD.T_HISTACTU et DBPROD.TJ_POURS il te faut un index sur OID_OPERA
- Sur les tables DBPROD.TJ_POURS et DBPROD.T_ARCHIVES il te faut un index sur ID_FACTURE, ID_ARTICLE

[punkoff]

attention K2R400, il me semble que tu as oublié la condition WHERE AA_DATARCH IS NULL sur la table T_ARCHIVE du coup l'indexation devrait suivre aussi.

De plus cette solution pose un problème si le SI est encore actif, on pourrait tres bien supprimer des lignes de la table de prod sans quelles soient archivées !

[K2R400]

Envoyé par punkoff

attention K2R400, il me semble que tu as oublié la condition WHERE AA_DATARCH IS NULL sur la table T_ARCHIVE du coup l'indexation devrait suivre aussi.

Oui tu as raison, il faut rajouter cette clause.

Envoyé par punkoff

De plus cette solution pose un problème si le SI est encore actif, on pourrait tres bien supprimer des lignes de la table de prod sans quelles soient archivées !

Non c'est impossible, c'est le principe du niveau d'isolation RR (Repetable Read) qui verrouille les enregistrements lus.

[pdz74]

Je ne reviens pas sur les différents moyens d'optimisation.

Ceci dit, quand on souhaite insérer dans une table historique après suppression dans une table de gestion, le moyen le plus simple et le plus rapide, c'est de se servir d'un trigger. De cette manière, tu n'es pas obligé de travailler en 2 temps et de multiplier par 2 tes temps de réponses. En plus, tu restes cohérent dans le SI, puisque INSERT dans Histo et DELETE dans Gestion sont dans la même UR, le ROLLBACK de l'un entrainant le ROLLBACK de l'autre, le COMMIT de l'un entrainant le COMMIT de l'autre.

Prenons une table de 5 colonnes que tu souhaites historiser dans une table de même définition + une 6ème colonne contenant le timestamp d'historisation (c'est souvent une information importante). Le trigger serait :

Code :

1
2
3
4
5
6
7
8
9
CREATE  TRIGGER XXX.TABJOUR01                                       
        AFTER DELETE                                                
        ON XXX.TABJOUR                                              
        REFERENCING OLD AS OLD                                      
        FOR EACH ROW MODE DB2SQL                                    
INSERT  INTO TABHIST ( COL1 , COL2 , COL3 , COL4 , COL5 , TSINSERT )
VALUES (OLD.COL1 , OLD.COL2 , OLD.COL3 , OLD.COL4 , OLD.COL5 ,      
        CURRENT TIMESTAMP)                                          
;

A ta disposition et bonne utilisation.

[punkoff]

Envoyé par K2R400

Non c'est impossible, c'est le principe du niveau d'isolation RR (Repetable Read) qui verrouille les enregistrements lus.

Oui j'entends bien. Mais si je ne m'abuse ce niveau de lock est au niveau des lignes et non au niveau table.


Reprenons l'algo :

Table 1 : on sélectionne des enregistrements que l'on insere dans T2 selon une sélection précise sur T3.

Table 1 : on delete des enregistrements selon une selection precise sur T3.

Rien n'indique qu'entre les 2 étapes il n'y ai pas de nouveau enregistrement sur notre table t3, qui n'ont aucune raison d'être locké par le mode RR.
Ces enregistrements peuvent prendre part à la sélection de la 2eme étape => données non archivées supprimées.

non ?

[K2R400]

@pdz74,

c'est une excellent idée : les triggers.

@punkoff,

Le niveau d'isolation RR empêchera toute insertion, suppression et modification d'enregistrements lus, non encore "commités".
Ainsi pour reprendre ton exemple, on ne pourra jamais toucher au contenu de T3 entre la première requête et le commit final (excepté si c'est dans la même transaction).

[gilles98800]

Bonjour
D'abord merci pour toutes ces réponses. J'ai plusieurs piste à explorer semble-t-il
Pour répondre au diverses questions
@punkoff

As-tu pu identifier la partie de ta procédure qui étaient la plus longue ? (DBMON)

Sur les 8 heures les deletes durent 4h50 et les inserts 3h30 (index advisor). Pour DBMON faut que je regarde comment m'en servir

Sinon pourrai-tu indiquer précisément les index en place sur tes tables?

Il n'y avait aucun index au départ, j'en ai créé un sur la table C pour arriver à 5h.Il a été créé sur ID_FACTURE, ID_ARTICLE et OID_OPERA

Et enfin quand tu passe cette proc-stock es-tu en concurrence avec d'autre process ?

Pas de concurrence Le traitement passe la nuit avec le TP fermé et bien après les batchs quotidiens. C'est une petite base de 1,8G

Qu'appel-tu table A et table C sachant que tu as 3 tables distincts ?

Table A = T_HISTACTU, Table C = TJ_POURS T_ARCHIVES serait la table B. Je me suis mal exprimé sur ce coup là

- Est-ce normal que tu n'update pas la date d'archivage dans T_ARCHIVES apres ton process ?

Si il y a bien un update de cette date après le traitement. En fait je n'ai présenté qu'un petit bout de la procédure car elle concerne beaucoup plus de tables mais toujours sur le même principe. J'ai donc fait tourner un extrait pour voir ce que ça donnait.

- Une étape d'archivage va bouger en gros combien de % de lignes de table de prod ?

Ca concerne environ 10% de la table

@KR2400
Merci pour ton aide, je vais examiner cela de plus près. Je un peu de temps vu que le traitement passe 1 fois par an.Et que je suis seul sur le coup.

@pdz74
Je précise que les tables ne sont pas journalisées. Est-ce la solution avec trigger reste valable.Je vais étudier cela aussi.


A tous, c'est bon de savoir qu'on est pas tout seul . merci. Je vais faire mes tests et reviendrai vers vous dans quelques temps

[pdz74]

Bonjour,

La définition du trigger que je t'ai donné est simple :

Lors de toute suppression dans la table TABJOUR, il y aura une insertion automatique dans la table TABHIST. Peu importe que tes suppressions soient journalières, hebdomadaires, mensuelles, ... Peu importe que ce soit un programme TP, Batch, ou un spufi qui fasse les DELETE. Ce sera pris en compte, c'est automatique, ça assure l'intégrité des données et ça divise par 2 tes temps de réponse. Bref que des avantages !!!

@+

[gilles98800]

Bonjour
Finalement, j'ai pu faire assez rapidement mes tests.

1) Le fichier QAQQINI n'existait pas dans QUSRSYS, je l'ai donc créé comme demandé. La valeur contenue à l'origine dans QQPARM était '*DEFAULT'. J'ai fait tourner avec cette seule modif et on a gagné encore 1h30 ---> 3h30

2) Mais la vraie valeur ajoutée fut de recoder la requête comme préconisé par K2R400. Moins d'une minute avec des résultats conformes à mes attentes Assez sidérant comme différence au passage.

3)Le trigger est une excellente idée que je testerai aussi. Il faut que je voie par rapport au specs de l'appli si l'archivage doit être systématique lors d'un delete

4) Merci encore pour votre aide, ça m'a bien fait progresser.