Optimisation d'un script

**lotofski** · 28/04/2018, 13h18

Est ce que qlq'un peut m'aider a optimisé un scripte SQL (il fonctionne mtn normalement ? svp sql 'teradata'.sql )
merci d'avance

**soazig** · 28/04/2018, 18h55

Bonjour,
Pour répondre correctement à ta question il faudrait

avoir un descriptif de tes tables
avoir un plan d'execution réel pour savoir où ça coince
avoir la liste des index
savoir ton sgbd

Sinon à la lecture de ton script j'ai vu plusieurs fois dans un order by de fonction de fenêtrage

Code :

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

case when COD_EVT in ('74','43') then '1000'  when COD_EVT in ('21', '42') then '0' else COD_EVT end

Cela pourrait se transformer en une table de correspondance ou une vue

Code :

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

1
2
3
4
5
6
7
select '74' as Code_EVT, '1000' as Ordre
union
select '43' as Code_EVT, '1000' as Ordre
union
select '21' as Code_EVT, '0' as Ordre
union 
select '42' as Code_EVT, '0' as Ordre

Tu fais une jointure externe sur cette table de correspondance et ton order by devient

Code :

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

COALESCE(ORDRE,CODE_EVT)

Par ailleurs

Code :

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

1
2
3
4
5
6
7
8
9
10
,CAST(
										SUBSTRING (CAST ( 
												 TO_TIMESTAMP(
															GREATEST(
															COALESCE(TO_CHAR(CNT.DAT_DEB_VLD , 'yyyymmddhh24miss'),'19000101000000'),
															COALESCE(TO_CHAR(EVT.DAT_EVT_UTC, 'yyyymmddhh24miss'),'19000101000000')
															),'yyyymmddhh24miss')
										AS VARCHAR(25))  FROM 1 FOR 19)  
									AS TIMESTAMP(0) FORMAT 'YYYY/MM/DDBHH:MI:SS')
								AS DAT_EVT_UTC

me semble quelque peu compliqué, et n'utilisera jamais d'index que cherches tu à faire?
Enfin si tu ne veux pas prendre le risque d'être spammé enlève ton adresse mail de ton post.
Cordialement
Soazig

**lotofski** · 29/04/2018, 13h10

le scripte fonctionne bien mais on dois l'optimiser , on travaille sur teradata

**soazig** · 29/04/2018, 13h44

Bonjour,
Tu n'as répondu qu'à une question sur 5.
Je reformule pour le plan d’exécution il servira à savoir ce qui prend du temps, et quelle est ou quelles sont la ou les portions à remanier.

As tu essayé ma suggestion avec table de correspondance.
Nous ne sommes pas magiciens, sans information nous ne pouvons pas deviner.
A+
Soazig

**lotofski** · 29/04/2018, 14h54

Merci pour votre réponse , pour le reste des questions je peux réponde demain en rentrant au boulot , j'ai ramené que le script pour le moment , merci encore une fois .

**lotofski** · 29/04/2018, 15h06

voila le message de demande d'optimisation ''Optimisation technique de la requête sur le calcul d'EVENT.
Avec la quantité croissante d'événement, une optimisation est nécessaire pour éviter tout accident dans un proche avenir.''

**lotofski** · 30/04/2018, 09h42

Bonjour , soazik , voici les informations demandées , merci pour votre aide .creat table .sql insert.sql select .sql new ticket 572 (1).sql

**soazig** · 30/04/2018, 10h22

Bonjour,
Tu nous as fourni le script d'une table et pas des 3 tables utilisées. On n'a pas de script d'index , du coup on ne sait pas s'il n'y en a pas, ou si tu ne les as pas fourni.
Cordialement
Soazig

**escartefigue** · 30/04/2018, 10h40

Bonjour,

Avant de parler d'optimisation, il faudrait déjà que ce script fonctionne, je doute que ce soit le cas au vu des premières lignes fournies

Par exemple l'extrait ci-dessous est erroné

Code :

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

1
2
    ,case when NUM_STA is null 
          then max(NUM_STA) OVER (PARTITION BY IDT_CNT_RCU, NUM_RTE  ORDER BY DAT_EVT_UTC ASC, case when COD_EVT in ('74','43') then '1000'

Exécutez le SELECTseul dans un premier temps pour vérifier son bon fonctionnement (et de préférence par petits bouts)

Ensuite, vu la taille conséquente de la requête, il serait préférable d'exprimer le besoin fonctionnel (que cherchez vous à obtenir) et communiquer le MLD pour voir si une autre approche plus simple ne serait pas plus efficiente

**lotofski** · 30/04/2018, 11h20

Bonjour escartefigue , merci pour votre réponse , le script marche trés bien , il faut juste commencer par le bas l’exécution .

**lotofski** · 30/04/2018, 11h22

Bonjour soazig
Voici les 4 tables TABLE 1.sql TABLE 2.sql TABLE 3.sql TABLE 4.sql

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
CREATE MULTISET TABLE DWHFRT.STAMP_EVT_CNT ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      COD_EVT CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Event Code UPU' NOT NULL COMPRESS '001',
      IDT_CNT_RCU CHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Rec. -ID or Container ID' NOT NULL,
      IDT_TYP CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Id-Type' NOT NULL COMPRESS '0',
      DAT_EVT_UTC TIMESTAMP(0) TITLE 'Event timestamp in UTC' NOT NULL,
      DAT_CRN_ODS TIMESTAMP(0) TITLE 'Timestamp when row was imported to ODS' NOT NULL,
      DAT_LOC_EVT TIMESTAMP(0) TITLE 'Event timestamp in Local' COMPRESS ,
      COD_EVT_MSG CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Event Code from the message' COMPRESS ,
      NOM_SRC CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Source' COMPRESS ,
      IDT_COD_CNT VARCHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Container Instance ID' COMPRESS ,
      STT_LVL_SCA CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Scan Level' COMPRESS ,
      PDS_BRT DECIMAL(6,1) TITLE 'Gross Weight (Kg)' COMPRESS ,
      COD_RSN CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Reason Code' COMPRESS ,
      COD_CIE_HDL VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Handling Transport Company' COMPRESS ,
      LCT_SCA VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Scan Location' COMPRESS ,
      ESC_ORI CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Origin Station' COMPRESS ,
      ESC_DST CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Dest. Station' COMPRESS ,
      NOM_PRN_ORI VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Partner at Origin' COMPRESS ,
      NOM_PRN_DST VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Partner at Dest.' COMPRESS ,
      NUM_VOL_DEP VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Dep. Flight No.' COMPRESS ,
      NUM_VOL_ARV VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Arrv. Flight No.' COMPRESS ,
      DAT_DEP TIMESTAMP(0) TITLE 'Departure Date/Time' COMPRESS ,
      DAT_VOL TIMESTAMP(0) TITLE 'Arrival Date/Time' COMPRESS ,
      DAT_CRN_EVT TIMESTAMP(0) TITLE 'Event Timestamp written to disk' COMPRESS ,
      DAT_MDF TIMESTAMP(0) TITLE 'Last modified Time Stamp' COMPRESS ,
      UTI_MDF VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Changed By' COMPRESS ,
      IDT_VOL VARCHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Flight ID' COMPRESS ,
      DAT_CRN_DWH DATE FORMAT 'YYYYMMDD' TITLE 'DWH creation date' NOT NULL)
PRIMARY INDEX PI_STAMP_EVT_CNT ( IDT_CNT_RCU ,DAT_EVT_UTC );

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
CREATE MULTISET TABLE DWHFRT.STAMP_EVT_RCU ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      IDT_CNT_RCU CHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Rec. -ID or Container ID' NOT NULL,
      DAT_LOC_EVT TIMESTAMP(0) TITLE 'Event time stamp Local Time' NOT NULL,
      COD_EVT CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Event Code' NOT NULL COMPRESS '001',
      DAT_CRN_ODS TIMESTAMP(0) TITLE 'Timestamp when row was imported to ODS' NOT NULL,
      SRC_DON_ESC CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Data sourced from Station' NOT NULL COMPRESS ('AMS','CDG','CPH','JFK'),
      SRC_ESC_TSP CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Source Station Transport Co.' NOT NULL COMPRESS '2',
      IDT_TYP CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Id-Type' COMPRESS ,
      IDT_CNT VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Container ID' COMPRESS ,
      PDS_BRT DECIMAL(6,1) TITLE 'Gross weight' COMPRESS ,
      COD_RSN CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'ReasonCode' COMPRESS ,
      ESC_ORI CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Origin Station' COMPRESS ,
      ESC_DST CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Dest. Station' COMPRESS ,
      NOM_PRN_ORI VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Partner at Origin' COMPRESS ,
      NOM_PRN_DST VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Partner at Dest.' COMPRESS ,
      NUM_VOL_DEP VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Dep. Flight No.' COMPRESS ,
      NUM_VOL_ARV VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Arrv. Flight No.' COMPRESS ,
      DAT_DEP TIMESTAMP(0) TITLE 'Departure Date/Time' COMPRESS ,
      DAT_VOL TIMESTAMP(0) TITLE 'Arrival Date/Time' COMPRESS ,
      IDT_VOL VARCHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Flight ID' COMPRESS ,
      COD_LCT VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Location' COMPRESS ,
      NOM_CND VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Driver' COMPRESS ,
      STT_SPP CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Delete Flag' COMPRESS ,
      STT_MLD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'MLD generated flag' COMPRESS ,
      STT_ANL CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Booking cancelled via MLD' COMPRESS ,
      STT_ANL_2 CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Booking cancelled via MLD 2' COMPRESS ,
      STT_LIV CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Delivery confirmed flag' COMPRESS ,
      DAT_LIV TIMESTAMP(0) TITLE 'Delivery Timestamp' COMPRESS ,
      DAT_DNI_MDF VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Delivered to Office of Exchange' COMPRESS ,
      DAT_MDF TIMESTAMP(0) TITLE 'Last modified Time Stamp' COMPRESS ,
      UTI_MDF VARCHAR(14) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Changed By' COMPRESS ,
      STT_MDF_PC CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Changed on Workstation' COMPRESS ,
      NUM_VOL_DEP_CLC VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Dep. Flight No.' COMPRESS ,
      NUM_VOL_ARV_CLC VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Arrv. Flight No.' COMPRESS ,
      CIE_VOL_DEP CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Dep. Flight No.' COMPRESS ,
      CIE_VOL_ARV CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Arrv. Flight No.' COMPRESS ,
      DAT_CRN_DWH DATE FORMAT 'YYYYMMDD' TITLE 'DWH creation date' NOT NULL)
PRIMARY INDEX PI_STAMP_EVT_RCU ( IDT_CNT_RCU ,DAT_LOC_EVT );
 
 
SELECT	IDT_CNT_RCU, DAT_LOC_EVT, COD_EVT, DAT_CRN_ODS, SRC_DON_ESC,
		SRC_ESC_TSP,
		IDT_TYP,
		IDT_CNT,
		PDS_BRT,
		COD_RSN,
		ESC_ORI,
		ESC_DST,
		NOM_PRN_ORI,
		NOM_PRN_DST,
		NUM_VOL_DEP,
		NUM_VOL_ARV,
		DAT_DEP,
		DAT_VOL,
		IDT_VOL,
		COD_LCT,
		NOM_CND,
		STT_SPP,
		STT_MLD,
		STT_ANL,
		STT_ANL_2,
		STT_LIV,
		DAT_LIV,
		DAT_DNI_MDF,
		DAT_MDF,
		UTI_MDF,
		STT_MDF_PC,
		NUM_VOL_DEP_CLC,
		NUM_VOL_ARV_CLC,
		CIE_VOL_DEP,
		CIE_VOL_ARV,
		DAT_CRN_DWH
FROM	DWHFRT.STAMP_EVT_RCU

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
CREATE MULTISET TABLE DWHFRT.STAMP_EVT_RCU ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      IDT_CNT_RCU CHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Rec. -ID or Container ID' NOT NULL,
      DAT_LOC_EVT TIMESTAMP(0) TITLE 'Event time stamp Local Time' NOT NULL,
      COD_EVT CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Event Code' NOT NULL COMPRESS '001',
      DAT_CRN_ODS TIMESTAMP(0) TITLE 'Timestamp when row was imported to ODS' NOT NULL,
      SRC_DON_ESC CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Data sourced from Station' NOT NULL COMPRESS ('AMS','CDG','CPH','JFK'),
      SRC_ESC_TSP CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Source Station Transport Co.' NOT NULL COMPRESS '2',
      IDT_TYP CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Id-Type' COMPRESS ,
      IDT_CNT VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Container ID' COMPRESS ,
      PDS_BRT DECIMAL(6,1) TITLE 'Gross weight' COMPRESS ,
      COD_RSN CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'ReasonCode' COMPRESS ,
      ESC_ORI CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Origin Station' COMPRESS ,
      ESC_DST CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Dest. Station' COMPRESS ,
      NOM_PRN_ORI VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Partner at Origin' COMPRESS ,
      NOM_PRN_DST VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Partner at Dest.' COMPRESS ,
      NUM_VOL_DEP VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Dep. Flight No.' COMPRESS ,
      NUM_VOL_ARV VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Arrv. Flight No.' COMPRESS ,
      DAT_DEP TIMESTAMP(0) TITLE 'Departure Date/Time' COMPRESS ,
      DAT_VOL TIMESTAMP(0) TITLE 'Arrival Date/Time' COMPRESS ,
      IDT_VOL VARCHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Flight ID' COMPRESS ,
      COD_LCT VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Location' COMPRESS ,
      NOM_CND VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Driver' COMPRESS ,
      STT_SPP CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Delete Flag' COMPRESS ,
      STT_MLD CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'MLD generated flag' COMPRESS ,
      STT_ANL CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Booking cancelled via MLD' COMPRESS ,
      STT_ANL_2 CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Booking cancelled via MLD 2' COMPRESS ,
      STT_LIV CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Delivery confirmed flag' COMPRESS ,
      DAT_LIV TIMESTAMP(0) TITLE 'Delivery Timestamp' COMPRESS ,
      DAT_DNI_MDF VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Delivered to Office of Exchange' COMPRESS ,
      DAT_MDF TIMESTAMP(0) TITLE 'Last modified Time Stamp' COMPRESS ,
      UTI_MDF VARCHAR(14) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Changed By' COMPRESS ,
      STT_MDF_PC CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Changed on Workstation' COMPRESS ,
      NUM_VOL_DEP_CLC VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Dep. Flight No.' COMPRESS ,
      NUM_VOL_ARV_CLC VARCHAR(17) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Arrv. Flight No.' COMPRESS ,
      CIE_VOL_DEP CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Dep. Flight No.' COMPRESS ,
      CIE_VOL_ARV CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Arrv. Flight No.' COMPRESS ,
      DAT_CRN_DWH DATE FORMAT 'YYYYMMDD' TITLE 'DWH creation date' NOT NULL)
PRIMARY INDEX PI_STAMP_EVT_RCU ( IDT_CNT_RCU ,DAT_LOC_EVT );
 
 
SELECT	IDT_CNT_RCU, DAT_LOC_EVT, COD_EVT, DAT_CRN_ODS, SRC_DON_ESC,
		SRC_ESC_TSP,
		IDT_TYP,
		IDT_CNT,
		PDS_BRT,
		COD_RSN,
		ESC_ORI,
		ESC_DST,
		NOM_PRN_ORI,
		NOM_PRN_DST,
		NUM_VOL_DEP,
		NUM_VOL_ARV,
		DAT_DEP,
		DAT_VOL,
		IDT_VOL,
		COD_LCT,
		NOM_CND,
		STT_SPP,
		STT_MLD,
		STT_ANL,
		STT_ANL_2,
		STT_LIV,
		DAT_LIV,
		DAT_DNI_MDF,
		DAT_MDF,
		UTI_MDF,
		STT_MDF_PC,
		NUM_VOL_DEP_CLC,
		NUM_VOL_ARV_CLC,
		CIE_VOL_DEP,
		CIE_VOL_ARV,
		DAT_CRN_DWH
FROM	DWHFRT.STAMP_EVT_RCU

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
CREATE MULTISET TABLE DWHFRT.STAMP_EVT_VOL_GRP ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      IDT_EVT CHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'ID evt' NOT NULL,
      IDT_VOL CHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Flight ID' NOT NULL,
      DAT_CRN_ODS TIMESTAMP(0) TITLE 'Timestamp when row was imported to ODS' NOT NULL,
      DAT_MDF TIMESTAMP(0) TITLE 'Last modified Time Stamp' COMPRESS ,
      DAT_CRN_DWH DATE FORMAT 'YYYYMMDD' TITLE 'DWH creation date' NOT NULL)
PRIMARY INDEX PI_STAMP_EVT_VOL_GRP ( IDT_EVT ,IDT_VOL );
 
 
SELECT	IDT_EVT, IDT_VOL, DAT_CRN_ODS, DAT_MDF, DAT_CRN_DWH
FROM	DWHFRT.STAMP_EVT_VOL_GRP

**soazig** · 30/04/2018, 12h19

Il y a t-il des index sur ces tables?

**escartefigue** · 30/04/2018, 12h25

Envoyé par lotofski

Bonjour escartefigue , merci pour votre réponse , le script marche trés bien , il faut juste commencer par le bas l’exécution .

OK, il faut avouer que avec cette requête à rallonge, c'est très difficile à lire, d'autant que la mise en forme laisse à désirer

Sinon vous n'avez communiqué que les PRIMARY INDEX, s'il n'y a rien d'autre alors vos critères de jointure (IDT_COD_CNT) et de filtrage (IDT_TYP) ne sont pas indexés

**Waldar** · 30/04/2018, 12h37

Il faut voir où le temps passe, si c'est du CPU, de la redistribution de données ou les deux.
Regardez dans DBQLogs ces informations si vous y avez accès, sinon demandez à un DBA de regarder pour vous.

Le script étant architecturé en blocs, c'est assez simple de créer plusieurs tables volatiles et de les remplir une à une au moins pour voir où le temps passe.

Les différentes fonctions de fenêtrage imposent des redistributions (coûteuses).

Le problème global que je vois avec ce script c'est que vous faites un traitement complet, il n'y a aucun filtre de date.
Potentiellement vous refaites le même boulot à chaque fois que vous l'exécutez sur un gros volume de données.

Sur quelle version de TD êtes-vous ?

Code :

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

select version from dbc.dbcinfo;

Avez-vous accès à l'option de partitionnement ?

Vous n'avez pas mis les bonnes tables dans vos descriptions, il manque les tables STAMP_OPS_EVT_TMP & DWHFRT.STAMP_CNT_RCU.
Vous avez donné deux fois la définition de la table DWHFRT.STAMP_EVT_RCU et une fois celle de DWHFRT.STAMP_EVT_VOL_GRP, mais elles ne font pas partie du script.

@soazig, non pas d'index hormis les PI, sinon ils seraient dans les scripts create.

**lotofski** · 30/04/2018, 13h03

Bonjour #waldar pour la qst (votre qst : il manque les tables STAMP_OPS_EVT_TMP & DWHFRT.STAMP_CNT_RCU.) TMP c'est temporaire pas de table pour ça voir piéce , Nom : les tables.PNG
Affichages : 279
Taille : 48,2 Ko

Nom : les tables.PNG
Affichages : 279
Taille : 48,2 Ko

( je viens d'arriver sur le projet il m'ont demandés de l'optimiser sachant que le script marche .)
voila le descriptif du ticket
"Technical optimisation of query about EVENT calculation.With the increasing amount of event, an optimisation is needed to avoid any crash in a near future.

(STAMP_OPS_EVT_TMP.bteq) file."

**lotofski** · 30/04/2018, 13h54

Nom : EXPLAIN 1.PNG
Affichages : 229
Taille : 18,9 Ko

Nom : EXPLAIN 2.PNG
Affichages : 242
Taille : 21,6 Ko

Nom : EXPLAIN 3.PNG
Affichages : 266
Taille : 44,1 Ko

Nom : EXPLAIN 4.PNG
Affichages : 325
Taille : 44,9 Ko

Nom : EXPLAIN 5.PNG
Affichages : 277
Taille : 38,0 Ko

on tout il me ramené 16039879 réel

**lotofski** · 30/04/2018, 14h12

Nom : EXEMPLE DE RE.PNG
Affichages : 272
Taille : 90,2 Ko

un extrait du résultat

**Waldar** · 30/04/2018, 15h02

Qu'avez-vous suivi comme formation Teradata d'une part et SQL d'autre part ?

Parce que là, vous m'avez l'air assez débutant : ce n'est pas une tare on l'a tous été, mais les sujets d'optimisations demandent une connaissance (approfondie) du SQL, des mécanismes internes de la base de données, et avoir une petite couche de fonctionnel s'avère nécessaire dans ce cas présent.

**lotofski** · 30/04/2018, 15h08

bonjour ,
Oui je connais pas grand chose en teradata , et j'ai fait un peu sql et voici la table demandé STAMP_OPS_EVT_TMP .sql

**soazig** · 01/05/2018, 09h33

Bonjour,
Tu nous as fourni la description de beaucoup de table qui ne figurent pas dans ton script mais pas STAMP_CNT_RCU ou je l'ai raté.
Je propose que tu essaies progressivement de créer les index suivants

table DWHFRT.STAMP_CNT_RCU index sur IDT_COD_CNT
table DWHFRT.STAMP_EVT_CNT index sur IDT_COD_CNT
table DWHFRT.STAMP_EVT_CNT index sur IDT_TYP
table DWHFRT.STAMP_CNT_RCU index sur IDT_TYP

Tu crée un index tu testes, tu en crées un autre tu testes, etc..
Par ailleurs, j'ai modifié ton script en utilisant une correspondance, à tester après les index. J'ai aussi enlevé les colonnes NON_CORR_DAT_EVT_UTC, NON_CORR_DAT_EVT_CNT_UTC qui ne servaient pas dans le résultat final. Vu la taille de la requete il peut y avoir des coquilles.

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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
;With Correspondance
AS
(
select '74' as Code_EVT, '1000' as Ordre
union
select '43' as Code_EVT, '1000' as Ordre
union
select '21' as Code_EVT, '0' as Ordre
union 
select '42' as Code_EVT, '0' as Ordre
)
INSERT INTO STAMP_OPS_EVT_TMP
Select 
	--ETAPE TMP7: Pour chaque station qui n est pas nouvelle (voir TMP5), on reporte le numéro précédent
	NUM_RTE
	--On reporte les numéro de station qui ne changent pas.
    ,case when NUM_STA is null then max(NUM_STA) OVER (PARTITION BY IDT_CNT_RCU, NUM_RTE  ORDER BY DAT_EVT_UTC ASC, Ordre desc, DAT_DEP asc ROWS UNBOUNDED PRECEDING) else NUM_STA 
    end as NUM_STA
	,COD_EVT
    ,IDT_CNT_RCU
    ,DAT_EVT_UTC
    ,DAT_LOC_EVT
    ,DAT_DEP
    ,LCT_SCA
	,ESC_ORI
    ,ESC_DST
    ,NOM_PRN_ORI
    ,NOM_PRN_DST
    ,NUM_VOL_DEP
    ,NUM_VOL_ARV
    ,IDT_VOL
 
 FROM 
	(
	--ETAPE TMP6: Pour chaque route, pour chaque nouvelle station, On affecte un numéro croissant à chaque nouvelle station (voir TMP5)
	Select 
	NUM_RTE
 
		--S il s agit d une nouvelle station (voir TMP5), on effectue un compte les indicateurs sur les lignes pécédentes pour attribuer le bon numéro de station
		,case 
		when NUM_STA=1 then coalesce(Count(NUM_STA) OVER (PARTITION BY IDT_CNT_RCU, NUM_RTE  ORDER BY DAT_EVT_UTC ASC, Odre desc, DAT_DEP asc ROWS UNBOUNDED PRECEDING),0) 
		else NUM_STA 
		end as 
		 NUM_STA
		,COD_EVT
		,IDT_CNT_RCU
		,DAT_EVT_UTC
		,DAT_LOC_EVT
		,DAT_DEP
		,LCT_SCA
		,ESC_ORI
		,ESC_DST
		,NOM_PRN_ORI
		,NOM_PRN_DST
		,NUM_VOL_DEP
		,NUM_VOL_ARV
		,IDT_VOL
		,Ordre
	 FROM 
		(
		-- ETAPE TMP5 Pour chaque route, on identifie les nouvelles stations avec un indicateur '1'
		Select 
			NUM_RTE
			--indicateur de changement de station pour chaque route
			,case 
				when coalesce(min(LCT_SCA) over ( PARTITION BY IDT_CNT_RCU , NUM_RTE order by DAT_EVT_UTC ASC, Ordre desc, DAT_DEP asc  rows between 1 preceding and 1 preceding), 0) <> LCT_SCA  then  1
				else null
				end  as NUM_STA
			,COD_EVT
			,IDT_CNT_RCU
			,DAT_EVT_UTC
			,DAT_LOC_EVT
			,DAT_DEP
			,LCT_SCA
			,ESC_ORI
			,ESC_DST
			,NOM_PRN_ORI
			,NOM_PRN_DST
			,NUM_VOL_DEP
			,NUM_VOL_ARV
			,IDT_VOL
			,Ordre
		FROM 
			(
			--ETAPE TMP4: Pour les lignes qui ne sont pas un début de route, on reporte le numéro précédent
				Select 
				case when NUM_RTE is null then 
											-- Si la ligne n'a pas de prédécésseur alors on mets 1 sinon on mets la valeur du prédécesseur
											Case when max(NUM_RTE) OVER (PARTITION BY IDT_CNT_RCU ORDER BY DAT_EVT_UTC ASC, Ordre desc, DAT_DEP asc ROWS UNBOUNDED PRECEDING) is null
											then 1
											Else max(NUM_RTE) OVER (PARTITION BY IDT_CNT_RCU ORDER BY DAT_EVT_UTC ASC, Ordre desc, DAT_DEP asc ROWS UNBOUNDED PRECEDING)
											end
										else NUM_RTE end as NUM_RTE
				,COD_EVT
				,IDT_CNT_RCU
				,DAT_EVT_UTC
				,DAT_LOC_EVT
				,DAT_DEP
				,LCT_SCA
				,ESC_ORI
				,ESC_DST
				,NOM_PRN_ORI
				,NOM_PRN_DST
				,NUM_VOL_DEP
				,NUM_VOL_ARV
				,IDT_VOL
				,Ordre
			FROM 
				(
				--ETAPE TMP3 : On affecte un numéro croissant à chaque début de route
				SELECT distinct
					 --s il s agit d un début de route, on effectue un compte les indicateurs sur les lignes pécédentes pour attribuer le bon numéro de début de route
					case 
					when RTE=1 then coalesce(Count(RTE) OVER (PARTITION BY IDT_CNT_RCU ORDER BY DAT_EVT_UTC asc , case when COD_EVT in ('74','43') then '1000'  when COD_EVT in ('21', '42') then '0' else COD_EVT end desc, DAT_DEP asc ROWS UNBOUNDED PRECEDING),0) 
					else RTE 
					end as NUM_RTE			   
					,TMP2.COD_EVT
					,TMP2.IDT_CNT_RCU
					,TMP2.DAT_EVT_UTC
					,TMP2.DAT_LOC_EVT
					,TMP2.DAT_DEP
					,TMP2.LCT_SCA
					,TMP2.ESC_ORI
					,TMP2.ESC_DST
					,TMP2.NOM_PRN_ORI
					,TMP2.NOM_PRN_DST
					,TMP2.NUM_VOL_DEP
					,TMP2.NUM_VOL_ARV
					,TMP2.IDT_VOL
					,Ordre
				FROM
				   ( --ETAPE TMP2: On identifie les début de route avec des indicateur '1' 
					SELECT
						--On affecte un indicateur  pour chaque début de route du Receptacle
						--s il s agit d un premier evènement (sauf iSC) sans début identifié
						case 
						when ROW_NUMBER() over ( PARTITION BY IDT_CNT_RCU 
												order by DAT_EVT_UTC ASC, Ordre desc, DAT_DEP asc  ) = 1 
								AND COD_EVT <>'iSC' 
								AND COD_EVT <>'6'
						then 1
						--s il s agit bien d un début identifié
						when COD_EVT in ('74','43') 
						then 1
						-- s il n y a pas de début de route mais que la ligne précédente est une fin de route, alors on crée une nouvelle route quand même
						when coalesce(min(COD_EVT) over ( PARTITION BY IDT_CNT_RCU order by DAT_EVT_UTC ASC, Ordre desc, DAT_DEP asc  rows between 1 preceding and 1 preceding), '#') in ('21', '42')  
						then 1
						else null end as RTE
					   ,TMP1.*
					   FROM
 
 
						   (
							-- ETAPE TMP1: On reccupère les information de base
							SELECT 
								'RECEPTACLE' as LVL
								,COD_EVT
								,IDT_CNT_RCU
								--,DAT_EVT_UTC as NON_CORR_DAT_EVT_UTC
								--,cast (NULL as Timestamp(0)) as NON_CORR_DAT_EVT_CNT_UTC
								,DAT_EVT_UTC
								,DAT_LOC_EVT
								,DAT_DEP
								,LCT_SCA
								,ESC_ORI
								,ESC_DST
								,NOM_PRN_ORI
								,NOM_PRN_DST
								,NUM_VOL_DEP
								,NUM_VOL_ARV
								,IDT_VOL
								,COALESCE(C.Ordre, EVT2.COD_EVT) as Ordre
							FROM
								DWHFRT.STAMP_EVT_CNT EVT2
							left outer join Correspondance C on
							EVT2.COD_EVT=C.COD_EVT
							--Receptacle level
							WHERE IDT_TYP = 'R'
 
							UNION 
 
							SELECT 
								 'CONTAINER' as LVL
								 ,EVT.COD_EVT
								,CNT.IDT_CNT_RCU
								--DR_REC0 Event at the COntainer level must have the data of the max(STAMP_CNT_RCU.DAT_DEB_VLD)
								--,EVT.DAT_EVT_UTC as NON_CORR_DAT_EVT_UTC
								--,CNT.DAT_DEB_VLD as NON_CORR_DAT_EVT_CNT_UTC
								--,Las plus grande des deux dates :(CNT.DAT_DEB_VLD  , EVT.DAT_EVT_UTC     ) as DAT_EVT_UTC
								,CAST(
										SUBSTRING (CAST ( 
												 TO_TIMESTAMP(
															GREATEST(
															COALESCE(TO_CHAR(CNT.DAT_DEB_VLD , 'yyyymmddhh24miss'),'19000101000000'),
															COALESCE(TO_CHAR(EVT.DAT_EVT_UTC, 'yyyymmddhh24miss'),'19000101000000')
															),'yyyymmddhh24miss')
										AS VARCHAR(25))  FROM 1 FOR 19)  
									AS TIMESTAMP(0) FORMAT 'YYYY/MM/DDBHH:MI:SS')
								AS DAT_EVT_UTC
								,EVT.DAT_LOC_EVT
								,EVT.DAT_DEP
								,EVT.LCT_SCA
								,EVT.ESC_ORI
								,EVT.ESC_DST
								,EVT.NOM_PRN_ORI
								,EVT.NOM_PRN_DST
								,EVT.NUM_VOL_DEP
								,EVT.NUM_VOL_ARV
								,EVT.IDT_VOL
								,COALESCE(C.Ordre, EVT.COD_EVT) as Ordre
							FROM
								DWHFRT.STAMP_EVT_CNT EVT
 
							INNER JOIN DWHFRT.STAMP_CNT_RCU CNT
									ON EVT.IDT_COD_CNT = CNT.IDT_COD_CNT
							left outer join Correspondance C on
							EVT.COD_EVT=C.COD_EVT
							--Container level
							WHERE EVT.IDT_TYP = 'C'
							  AND CNT.IDT_TYP = 'R'		
							) as TMP1
							--where IDT_CNT_RCU='AEDXBASESTOABTT70050001000054'
					) as TMP2
				) as TMP3
 
			) as TMP4
		) as TMP5
	) as TMP6
 --order by 4,5
;

Bon premier mai
Soazig

Optimisation d'un script

Langage SQL

Discussions similaires

Partager

Partager