1. #1
    Membre du Club
    Homme Profil pro
    Développeur .NET
    Inscrit en
    mai 2007
    Messages
    28
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Val d'Oise (Île de France)

    Informations professionnelles :
    Activité : Développeur .NET
    Secteur : Transports

    Informations forums :
    Inscription : mai 2007
    Messages : 28
    Points : 52
    Points
    52

    Par défaut Transformation date en période

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

  2. #2
    McM
    McM est actuellement connecté
    Expert éminent

    Homme Profil pro
    Développeur Oracle
    Inscrit en
    juillet 2003
    Messages
    4 060
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Développeur Oracle

    Informations forums :
    Inscription : juillet 2003
    Messages : 4 060
    Points : 6 426
    Points
    6 426
    Billets dans le blog
    3

    Par défaut

    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
    More Code : More Bugs. Less Code : Less Bugs
    Mon Blog PL/Sql : Fichier Zip / Image BMP / Lire sqliteDB

  3. #3
    Membre expérimenté
    Homme Profil pro
    Chef de projet MOA
    Inscrit en
    février 2012
    Messages
    511
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Morbihan (Bretagne)

    Informations professionnelles :
    Activité : Chef de projet MOA
    Secteur : Distribution

    Informations forums :
    Inscription : février 2012
    Messages : 511
    Points : 1 403
    Points
    1 403

    Par défaut

    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

  4. #4
    Membre du Club
    Homme Profil pro
    Développeur .NET
    Inscrit en
    mai 2007
    Messages
    28
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Val d'Oise (Île de France)

    Informations professionnelles :
    Activité : Développeur .NET
    Secteur : Transports

    Informations forums :
    Inscription : mai 2007
    Messages : 28
    Points : 52
    Points
    52

    Par défaut

    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

  5. #5
    Membre du Club
    Homme Profil pro
    Développeur .NET
    Inscrit en
    mai 2007
    Messages
    28
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Val d'Oise (Île de France)

    Informations professionnelles :
    Activité : Développeur .NET
    Secteur : Transports

    Informations forums :
    Inscription : mai 2007
    Messages : 28
    Points : 52
    Points
    52

    Par défaut

    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

  6. #6
    Modérateur

    Homme Profil pro
    Ingénieur d'études en décisionnel
    Inscrit en
    septembre 2008
    Messages
    7 643
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 40
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Ingénieur d'études en décisionnel
    Secteur : High Tech - Multimédia et Internet

    Informations forums :
    Inscription : septembre 2008
    Messages : 7 643
    Points : 16 397
    Points
    16 397

    Par défaut

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

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Réponses: 4
    Dernier message: 14/11/2007, 11h07
  2. [Dates] Transformation date en timestamp
    Par NerOcrO dans le forum Fonctions
    Réponses: 2
    Dernier message: 22/05/2007, 16h25
  3. API gestion de dates et période
    Par ran_hery dans le forum java.util
    Réponses: 2
    Dernier message: 28/07/2006, 10h18
  4. Sélection de dates ou période
    Par soso78 dans le forum Access
    Réponses: 1
    Dernier message: 25/10/2005, 21h08
  5. [XSL] Transformation DATE
    Par Phenolphtaleine dans le forum XSL/XSLT/XPATH
    Réponses: 5
    Dernier message: 28/06/2004, 15h57

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