Discussion :

[rvs75]

Bonjour,

Je viens de récupérer un série de "moulinettes" à optimiser car les données vont être multiplié selon les tables de 2 à 26 000 avec des problèmes de performances. J'ai optimisé tout sauf 4, procédure qui ont la même structure. Le but de ces dernières "moulinettes" est de transformer des lignes contenant un état et date d'effet en ligne contenant un état et date de début et une date de fin. Les états sont A(ctif) et I(nactif).

Pour être honnête, je ne sais pas pour quel bout prendre pour optimiser cela, en sachant que je ne connais peu Oracle (beaucoup plus SQLServer, MySQL et SQLite)

Je met le code (je l'ai modifié pour qu'il soit généraliste, je ne l'ai pas testé) :

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
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
 
create or replace 
PROCEDURE "TRAITEMENT_XXXXXX" AS
BEGIN
	DECLARE
		v_cpt NUMBER := 0; -- permettra de compter les lignes déjà insérées pour un Code donné
		r_recent TABLE_DESTINATION%ROWTYPE ;
 
BEGIN
	-- on purge la table d'export
	DELETE FROM TABLE_DESTINATION;
 
	-- on boucle sur toutes les lignes triées par code et par date_effet
	FOR item IN (
		SELECT *
		FROM TABLE_SOURCE
		ORDER BY Code,date_effet
	)
	LOOP
				-- 	gestion du A
 
    	IF item.STATUT = 'A' THEN
			BEGIN
				-- requête récupérant la ligne dont le début est le plus récent
				SELECT * INTO r_recent
				FROM TABLE_DESTINATION
				WHERE Code = item.Code
					AND DATEDEBUT = (
						SELECT max(TD.DATEDEBUT)
						FROM TABLE_DESTINATION TD
						WHERE TD.Code = item.Code
							);
				-- si l'élément considéré est postérieur à l'élément en export le plus récent
				IF item.DATE_EFFET > r_recent.DATEDEBUT THEN
					-- Si l'élément le plus récent n'est pas inactivé, on l'inactive...
					IF r_recent.DATEFIN IS NULL THEN
						-- UPDATE la date de fin et les descriptions
						UPDATE TABLE_DESTINATION
						SET
 
							DATEFIN = item.DATE_EFFET
						WHERE Code = item.Code
							AND DATEDEBUT = r_recent.DATEDEBUT;
					END IF;
					-- ... puis on insère une nouvelle ligne avec datedebut = date_effet
					INSERT INTO TABLE_DESTINATION (CODE, DESTRA, DATEDEBUT, DATEFIN)
					VALUES ( item.Code, item.DESC_LONGUE, item.DATE_EFFET, '');
 
				ELSE
					NULL;
				END IF;
 
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					-- Ce dode n'existe pas encore dans la base, il faut donc l'inserer
					INSERT INTO TABLE_DESTINATION (CCE, DESTRA, SHOTRA, ENAFLG, IPT, DIENBR, OTHDIE0, DEFCCE0, FCY, DATEDEBUT, DATEFIN)
					VALUES ( item.Code, item.DESC_LONGUE, item.DATE_EFFET, '');
			END;
 
		-- gestion du I
 
		ELSIF item.STATUT = 'I' THEN
			BEGIN
				-- lire la dernière ligne FARO la plus récente
				SELECT * INTO r_recent
				FROM TABLE_DESTINATION
				WHERE Code = item.Code
					AND DATEDEBUT = (
						SELECT max(TB.DATEDEBUT)
						FROM TABLE_DESTINATION TB
						WHERE TB.Code = item.Code
							);
				-- ce select retourne une exception NO_DATA_FOUND si il ne retourne aucune ligne
 
				IF r_recent.DATEFIN IS NULL THEN -- la dernière ligne FARO la plus récente est encore active...
					UPDATE TABLE_DESTINATION
					SET	DATEFIN = item.DATE_EFFET
					WHERE Code = item.Code
						AND DATEDEBUT = r_recent.DATEDEBUT;
				ELSE
					-- la dernière ligne la plus récente est déjà inactivée, on l'ignore
					NULL;
				END IF;
 
			EXCEPTION
				WHEN NO_DATA_FOUND THEN
					NULL;
			END;
			-- fin du I
 
		ELSE -- erreur sur le statut (ni I , ni A)
			-- Trace
		END IF;
 
	END LOOP;
 
END;
END;

Je suis preneur de toute idée ...

[McM]

Normalement ça doit être faisable en un seul select
Voir la methode tabibitosan https://www.developpez.net/forums/d1...e-tabibitosan/

Sinon, si tu peux donner un exemple de données (genre, plusieurs lignes à la même date, etc..) et le retour attendu, on devrait pouvoir te faire le SELECT

[Scriuiw]

Tout comme McM, à voir si la boucle plus les interrogations SQL ne peuvent être modifiées en une seule instruction MERGE
A voir aussi si le DELETE peut être changé en TRUNCATE TABLE (Il ne faut dans ce cas pas de lien "filles" à la table) car on peut espérer un gain de temps si la volumétrie de la table est importante

[rvs75]

Apparemment, un simple Lead me donne le résultat escompté !

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
with TableCop as
(select to_date('01/01/2017','DD/MM/YY') date_effet, 'code_bu_1' code_bu, 'Code_axe_1' code_axe,'A' actif, 'code_bu_1 Code_axe_1 A 2017' descr from dual union all
select to_date('31/12/2017','DD/MM/YY') date_effet, 'code_bu_1' code_bu, 'Code_axe_1' code_axe,'I' actif, 'code_bu_1 Code_axe_1 I 2017' descr from dual union all
select to_date('01/01/2018','DD/MM/YY') date_effet, 'code_bu_1' code_bu, 'Code_axe_1' code_axe,'A' actif, 'code_bu_1 Code_axe_1 A 2018' descr from dual union all
select to_date('01/01/2017','DD/MM/YY') date_effet, 'code_bu_1' code_bu, 'Code_axe_2' code_axe,'A' actif, 'code_bu_1 Code_axe_2 A 2017' descr from dual union all
select to_date('31/12/2017','DD/MM/YY') date_effet, 'code_bu_1' code_bu, 'Code_axe_2' code_axe,'I' actif, 'code_bu_1 Code_axe_2 I 2017' descr from dual union all
select to_date('01/01/2017','DD/MM/YY') date_effet, 'code_bu_2' code_bu, 'Code_axe_1' code_axe,'A' actif, 'code_bu_2 Code_axe_1 A 2017' descr from dual union all
select to_date('31/12/2017','DD/MM/YY') date_effet, 'code_bu_2' code_bu, 'Code_axe_1' code_axe,'I' actif, 'code_bu_2 Code_axe_1 I 2017' descr from dual union all
select to_date('01/01/2017','DD/MM/YY') date_effet, 'code_bu_2' code_bu, 'Code_axe_2' code_axe,'A' actif, 'code_bu_2 Code_axe_2 A 2017' descr from dual union all
select to_date('31/12/2017','DD/MM/YY') date_effet, 'code_bu_2' code_bu, 'Code_axe_2' code_axe,'I' actif, 'code_bu_2 Code_axe_2 I 2017' descr from dual)
select code_bu,code_axe,actif,descr,date_effet as date_debut,lead(date_effet) over (partition by code_bu,code_axe order by date_effet asc) as date_fin from  TableCop;

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
CODE_BU   CODE_AXE   ACTIF DESCR                       DATE_DEBUT DATE_FIN
--------- ---------- ----- --------------------------- ---------- --------
code_bu_1 Code_axe_1 A     code_bu_1 Code_axe_1 A 2017 01/01/17   31/12/17 
code_bu_1 Code_axe_1 I     code_bu_1 Code_axe_1 I 2017 31/12/17   01/01/18 
code_bu_1 Code_axe_1 A     code_bu_1 Code_axe_1 A 2018 01/01/18            
code_bu_1 Code_axe_2 A     code_bu_1 Code_axe_2 A 2017 01/01/17   31/12/17 
code_bu_1 Code_axe_2 I     code_bu_1 Code_axe_2 I 2017 31/12/17            
code_bu_2 Code_axe_1 A     code_bu_2 Code_axe_1 A 2017 01/01/17   31/12/17 
code_bu_2 Code_axe_1 I     code_bu_2 Code_axe_1 I 2017 31/12/17            
code_bu_2 Code_axe_2 A     code_bu_2 Code_axe_2 A 2017 01/01/17   31/12/17 
code_bu_2 Code_axe_2 I     code_bu_2 Code_axe_2 I 2017 31/12/17            
 
 9 lignes sélectionnées

Je n'ai pas encore testé sur les données réels. mais je pense que je suis en bonne voie.

Merci encore car je ne connaissais pas les instruction Lag et Lead

[rvs75]

Algorithme original : 4 h 55
Algorithme original modifié : 2 h 25
Algorithme de ce thread : 2 min 11

Il y surement moyen de gagner encore quelque secondes car je passe par une table intermédiaire que je vide pour la remplir avec les données d'origines avant de supprimer les données inutiles. Peut-être en passant par une vue ?

Données d'origine : 1406102 lignes
Données d'origine purgé des données inutiles : 1005981 lignes

Edit : 17 sec 34 avec une vue

[Waldar]

Félicitations, ça fait toujours plaisir de réussir ce genre de gains !