Améliorer la performance d'une requête

Version imprimable

Bonjour à tous,

J'ai cette procedure 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
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:

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.

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:

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:

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

30/04/2021, 13h37
chekkal

Citation:

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

merci je vai voir avec votre proposition
01/05/2021, 05h31
chekkal

Bonjour,

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

Citation:

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".
01/05/2021, 06h28
SergioMaster

Citation:

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:

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:

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
01/05/2021, 07h18
chekkal

Citation:

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.
01/05/2021, 07h57
SergioMaster

Ça doit être coton pour créer des contraintes ! C'est :fou:
01/05/2021, 08h12
chekkal

Bonjour,

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

Essayez avec la table temporaire
une fois (par bases)
Code:

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:

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:

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:

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));

01/05/2021, 15h15
chekkal

1 pièce(s) jointe(s)

pour la création de la table temporaire il me signale erreur
Pièce jointe 597037

en plus comment doit-je créer la table temporaire, est-ce à l'intérieur de la procédure?
01/05/2021, 18h51
SergioMaster

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 !

Citation:

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