Améliorer la performance d'une requête

**chekkal** · 30/04/2021, 07h53

Bonjour à tous,

J'ai cette procedure stockée

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
 
CREATE PROCEDURE REDCAL (
    CAS1 Char(1),
    NUM1 Varchar(5),
    CHE Varchar(255) )
AS
DECLARE VARIABLE i integer;
DECLARE VARIABLE NUM Varchar(5); 
DECLARE VARIABLE ANA Varchar(20); 
DECLARE VARIABLE DES Varchar(255); 
BEGIN
 I= gen_id(GENRED,-gen_id(GENRED,0));
delete  from RED where (cas=2) or(cas=3);
if (:cas1<>'2') then
begin
insert into red (cas,dat,clt,fou,rec,dep,des)
with c as (
select 2 as cas,DAT,null as clt,ANA as fou,null as rec,VER as dep,(select first 1 desf from ach a where (a.ANA=p.ANA)) as des  from PAF p where (ANA<>'00000')and(MOP='ESPECE')and((VER>0)or(VER<0))
union all 
select 2 as cas,DAT,ANA as clt,null as fou,VER as rec,null as dep,(select first 1 descl from ven v where (v.ANA=p.ANA)) as des from PAC P where (ANA<>'00000')and(MOP='ESPECE')and((VER>0)or(VER<0)) 
) select * from c;
 end
 else
 begin
      insert into red (cas,dat,clt,fou,rec,dep,des)
        with c as (
            select 2 as cas,DAT,null as clt,ANA as fou,null as rec,VER as dep,(select first 1 desf from ach a where (a.ANA=p.ANA)) as des  from PAF p where (ANA<>'00000')and(MOP='ESPECE')and((VER>0)or(VER<0))
            union all 
       select 2 as cas,dat,'00000' as clt,null as fou,sum(mht) as rec,null as dep,'VENTE JOURNEE ' as des from ven group by dat)
       select * from c;
 end     
 
 
SUSPEND;
END

et voci la strcutres des tables utilisées

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
 
-- table Achat:
CREATE TABLE ACH
(
  CAS Integer NOT NULL,
  DAT Date NOT NULL,
  FAC Varchar(30) NOT NULL,
  ORD Integer NOT NULL,
  ANA Varchar(20) CHARACTER SET ISO8859_1,
  MAT Varchar(20) CHARACTER SET ISO8859_1,
  PRO Varchar(20) CHARACTER SET ISO8859_1,
  DES Varchar(255) CHARACTER SET ISO8859_1,
  QTE Numeric(14,5),
  MHT Numeric(14,2),
  COI Integer,
  PER Integer,
  DAP Date,
  CHA Varchar(255) CHARACTER SET ISO8859_1,
  IMA Varchar(35) CHARACTER SET ISO8859_1,
  MON Numeric(14,2),
  OBS Varchar(255) CHARACTER SET ISO8859_1,
  PUA Numeric(14,5),
  COIR Integer,
  DESF Varchar(200) CHARACTER SET ISO8859_1,
  MTB Numeric(14,5),
  TAU Numeric(14,2),
  TVA Numeric(14,5),
  TRE Numeric(14,2),
  REM Numeric(14,5),
  UTI Varchar(50),
  COID Integer,
  COIF Integer,
  PUDEV Numeric(14,6),
  TAUXC Numeric(14,6),
  FRATRANS Numeric(14,6),
  FRADOU Numeric(14,6),
  FRAMAN Numeric(14,6),
  FRAAUT Numeric(14,6),
  DEVISE Varchar(15),
  FRATRANSP Numeric(14,6),
  PRIMARY KEY (CAS,DAT,FAC,ORD)
);
 
-- Table VEN
CREATE TABLE VEN
(
  DAT Date NOT NULL,
  BON Varchar(20) CHARACTER SET ISO8859_1 NOT NULL,
  ORD Integer NOT NULL,
  ANA Varchar(20) CHARACTER SET ISO8859_1,
  PRO Varchar(20) CHARACTER SET ISO8859_1,
  DES Varchar(255) CHARACTER SET ISO8859_1,
  QTE Numeric(14,5),
  PUV Numeric(18,6),
  MHT Numeric(14,2),
  COI Smallint,
  CDR Numeric(14,5),
  BEN Numeric(14,2),
  FAC Varchar(20) CHARACTER SET ISO8859_1,
  HEU Timestamp,
  DEN Numeric(14,5),
  OBS Varchar(255) CHARACTER SET ISO8859_1,
  UME Varchar(30),
  ED1 Numeric(14,4),
  ED2 Numeric(14,4),
  ED3 Numeric(14,4),
  QTP Numeric(14,5),
  PUA Numeric(14,5),
  QTD Numeric(14,5),
  QTR Numeric(14,5),
  DAP Date,
  COIR Integer,
  DESCL Varchar(200) CHARACTER SET ISO8859_1,
  CHA Varchar(30),
  IMA Varchar(15),
  MTB Numeric(14,5),
  TAU Numeric(14,2),
  TVA Numeric(14,5),
  TRE Numeric(14,2),
  REM Numeric(14,5),
  UTI Varchar(50),
  COID Integer,
  COIF Integer,
  MHTR Numeric(18,6),
  CLTLIV Char(1),
  CPUV Char(1),
  PRIMARY KEY (DAT,BON,ORD)
);
 
-- Table PAF
CREATE TABLE PAF
(
  CAS Integer NOT NULL,
  DAT Date NOT NULL,
  ANA Varchar(20) NOT NULL,
  FAC Varchar(20) NOT NULL,
  ORD Integer NOT NULL,
  DEP Numeric(14,2),
  MON Numeric(14,2),
  VER Numeric(14,2),
  RES Numeric(14,2),
  DES Varchar(255) CHARACTER SET ISO8859_1,
  MOP Varchar(30) CHARACTER SET ISO8859_1,
  DAC Date,
  MTB Numeric(14,2),
  TVA Numeric(14,5),
  REM Numeric(14,5),
  TIM Numeric(14,5),
  PRIMARY KEY (CAS,DAT,ANA,FAC,ORD)
);
-- Table PAC
CREATE TABLE PAC
(
  CAS Integer NOT NULL,
  DAT Date NOT NULL,
  ANA Varchar(20) NOT NULL,
  FAC Varchar(20) NOT NULL,
  ORD Integer NOT NULL,
  DEP Numeric(14,2),
  MON Numeric(14,2),
  VER Numeric(14,2),
  RES Numeric(14,2),
  DES Varchar(255) CHARACTER SET ISO8859_1,
  MOP Varchar(30) CHARACTER SET ISO8859_1,
  DAC Date,
  MTB Numeric(14,2),
  TVA Numeric(14,5),
  REM Numeric(14,5),
  TIM Numeric(14,5),
  BEN Numeric(14,2),
  PRIMARY KEY (CAS,DAT,ANA,FAC,ORD)
);

Mon problème c'est que lorsque les tables deviennent volumineuses l’exécution de cette procédure prend beaucoup de temps presque 1 minute. Et je voudrais pouvoir réduire ce temps, peut être en modifiant la procédure.

**SergioMaster** · 30/04/2021, 10h13

Bonjour,

Déjà en enlevant le suspend qui ne sert à rien puisqu'il n'y a pas de retour de données.

Ensuite AMHA il vaut mieux faire deux inserts plutôt que d'utiliser une CTE

Code :

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

1
2
3
 
insert into red (cas,dat,clt,fou,rec,dep,des) select 2 as cas,DAT,null as clt,ANA as fou,null as rec,VER as dep,(select first 1 desf from ach a where (a.ANA=p.ANA)) as des  from PAF p where (ANA<>'00000')and(MOP='ESPECE')and((VER>0)or(VER<0));
insert into red (cas,dat,clt,fou,rec,dep,des)  select 2 as cas,DAT,ANA as clt,null as fou,VER as rec,null as dep,(select first 1 descl from ven v where (v.ANA=p.ANA)) as des from PAC P where (ANA<>'00000')and(MOP='ESPECE')and((VER>0)or(VER<0));

ensuite ces SELECT First 1 sans clause ORDER BY me laissent dubitatif.
1- il faut ajouter des indexs sur PAF et PAC

Code :

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

1
2
  CREATE INDEX IX_PAFANA ON TABLE PAF(ANA);
  CREATE INDEX IX_PACANA ON TABLE PAC(ANA);

**chekkal** · 30/04/2021, 13h37

CREATE INDEX IX_PAFANA ON PAF(ANA);
CREATE INDEX IX_PACANA ON PAC(ANA);

merci je vai voir avec votre proposition

**chekkal** · 01/05/2021, 05h31

Bonjour,

J'ai indexé les 2 tables mais toujours pareil , le temps de l’exécution de la requête reste toujours élevé.

ensuite ces SELECT First 1 sans clause ORDER BY me laissent dubitatif.

c'est la conception des tables qui veut ça, le champs commun est "ana", et j'ai besoin des "desf" qui correspond à la désignation du champs "ana".

**SergioMaster** · 01/05/2021, 06h28

Envoyé par chekkal

c'est la conception des tables qui veut ça

Ce qui ne veut pas dire que cette conception soit bonne. Quand je vois des clés primaires composées je doute, et quand il y a besoin d'un

Code :

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

select first 1 desf from ach a where (a.ANA=p.ANA)

, qui plus est sans clause ORDER BY, c'est qu'il y a des redondances.
Créez un table temporaire (ANA,DESF PK ANA) ON COMMIT DELETE ROWS pourrait être une solution, même s'il me semble que les index sur ces tables ne sont pas forcément pris en compte
Remplir la table temporaire par un

Code :

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

INSERT INTO SELECT ANA,DESF FROM  ... WHERE  ... GROUP BY ANA;

et faite une jointure pour vos reqêtes

Mais j'ai quand même un gros doute, déjà exprimé, sur ce first.
Si ANA correspond à une seule désignation, il y aurait dû y avoir un table d'une structure minimale* ANA(CODE,DESIGNATION PK ANA) les tables utilisant ce code ayant des contraintes de référence.
Cela sent une migration PARADOX où les requêtes de jointures étaient inconnues.

*minimale car une meilleure structure aurait avant tout une colonne auto incrémentée servant d'index primaire, le code étant utilisé comme index unique

**chekkal** · 01/05/2021, 07h18

Si ANA correspond à une seule désignation, il y aurait dû y avoir un table d'une structure minimale* ANA(CODE,DESIGNATION PK ANA) les tables utilisant ce code ayant des contraintes de référence.

Tout à fait , il ya bien une table analy(ana,designation) mais il se trouve sur une autre base de données.

**SergioMaster** · 01/05/2021, 07h57

Ça doit être coton pour créer des contraintes ! C'est

**chekkal** · 01/05/2021, 08h12

Bonjour,

Je l'ai récupéré comme ça

**SergioMaster** · 01/05/2021, 09h35

Essayez avec la table temporaire
une fois (par bases)

Code :

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

1
2
CREATE GLOBAL TEMPORARY TABLE analy(ana VARCHAR(20) NOT NULL PRIMARY KEY,designation VARCHAR(255))
ON COMMIT DELETE ROWS;

procédure pour tester

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
SET TERM ^ ;
 
CREATE PROCEDURE TESTANALY(BASEREF VARCHAR(30)) 
RETURNS 
 ( A VARCHAR(25),
   D VARCHAR(255))
AS 
BEGIN
 FOR EXECUTE STATEMENT 'SELECT ANA,DESIGNATION FROM ANALY' 
   ON EXTERNAL :BASEREF INTO :A,:D  
 DO INSERT INTO ANALY(ANA,DESIGNATION) VALUES (:A,:D);
 FOR SELECT ANA,DESIGNATION FROM ANALY INTO :A,:D 
   DO SUSPEND;
END^
 
SET TERM ; ^

dans votre procédure, aprés bien sûr avoir mis en debut de procedure, et déclaré A et D

Code :

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

1
2
3
 FOR EXECUTE STATEMENT 'SELECT ANA,DESIGNATION FROM ANALY' 
   ON EXTERNAL :BASEREF INTO :A,:D  
 DO INSERT INTO ANALY(ANA,DESIGNATION) VALUES (:A,:D);

l'appel du (select first ...) sera remplacé par ,(select designation from ANALY where ANA= p.ANA)exemple

Code :

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

insert into red (cas,dat,clt,fou,rec,dep,des) select 2 as cas,DAT,null as clt,ANA as fou,null as rec,VER as dep,(select designation from ANALY where ANA=p.ANA) from PAF p where (ANA<>'00000')and(MOP='ESPECE')and((VER>0)or(VER<0));

**chekkal** · 01/05/2021, 15h15

pour la création de la table temporaire il me signale erreur
Nom : Untitled.png
Affichages : 175
Taille : 98,1 Ko

en plus comment doit-je créer la table temporaire, est-ce à l'intérieur de la procédure?

**SergioMaster** · 01/05/2021, 18h51

Ah oui, j'aurais du signaler que les tables temporaires ne supportent les index qu'à partir de la version 3 ? Mais avez vous indiqué votre version de Firebird en début de discussion !

en plus comment doit-je créer la table temporaire, est-ce à l'intérieur de la procédure?

c'est affligeant, vous osez poser cette question. renseignez vous sur ce qu'est une table temporaire.
Moi j'abandonne le sujet

Améliorer la performance d'une requête

SQL Firebird

Discussions similaires

Partager

Partager