Explication d'un message IBEXPERT

[mika]

Voila j'ai une requête que voici

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT DISTINCT agent_nom,agent_prenom,affe_societe,affe_matricule,
AFFA_CHAN_CODE_DEB, AFFE_CHAN_DATE_DEB,
affe_dep_imposable, AFFE_DEP_NON_IMPOSABLE,
AFFE_DEp_Complementaire,Affe_chantier,AFFE_CHANT_SUIVANT,
AFFE_CHAN_DATE_SUIVANT, AFFE_CONNECTE, Affe_lieu,
AFFE_MODE_DEPLACEMENT,AFFE_COMMENTAIRE,Affe_presence, chan_lieu
FROM agents, affectation, chantiers
WHERE agent_matricule = affe_matricule
AND affe_chantier   = chan_numero
AND affe_societe      = "001"
AND chantiers.chan_societe = '001'
AND agents.agent_societe = '001'
ORDER BY agent_nom,agent_prenom, AFFE_CHAN_DATE_DEB,AFFA_CHAN_CODE_DEB

Cette requête prend prés de 20 '' ce qui est long pour le client.

Lorsque je fais avec ibexpert j'ai un message comme ceci que je ne comprend pas

Code :

1
2
3
 
PLAN SORT (SORT (JOIN (AGENTS NATURAL,AFFECTATION INDEX 
(AFFE_MATRICULE),CHANTIERS INDEX (CHAN_NUMERO))))

Que signifie t il ?

Merci

[makowski]

cela veut dire que pour la table agents, il n'y a pas d'index efficace disponible et que donc le moteur scan toute la table dans l'ordre naturel, c'est à dire l'ordre dans lequel les enregistrements sont stockés sur disque
un index sur agents.agent_societe devrait pouvoir améliorer les choses

[mika]

Merci de ce message, je vais de suite le tester

[mika]

J'ai essayé cela prend maintenant 6 secondes

Voici mes tables

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
133
134
135
136
137
138
139
140
141
142
143
144
145
146
/******************************************************************************/
/***          Generated by IBExpert 2006.03.07 16/08/2006 22:03:16          ***/
/******************************************************************************/
 
CREATE DATABASE 'C:\Mika\Tables\g_d_tps\SITRAL2000.GDB'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 4096;
 
 
 
/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/
 
 
 
CREATE TABLE AFFECTATION (
    AFFE_SOCIETE             CHAR(15) CHARACTER SET NONE NOT NULL,
    AFFE_MATRICULE           DOUBLE PRECISION NOT NULL,
    AFFE_CHANTIER            CHAR(4) CHARACTER SET NONE NOT NULL,
    AFFE_LIEU                CHAR(30) CHARACTER SET NONE,
    AFFE_DEMARRAGE           DATE,
    AFFE_FIN_CHANTIER        DATE,
    AFFE_DATE_AFFECTATION    DATE,
    AFFA_CHAN_CODE_DEB       CHAR(4),
    AFFE_CHAN_DATE_DEB       DATE NOT NULL,
    AFFE_CHANT_SUIVANT       CHAR(4) CHARACTER SET NONE,
    AFFE_CHAN_DATE_SUIVANT   DATE,
    AFFE_DEP_IMPOSABLE       DOUBLE PRECISION,
    AFFE_DEP_NON_IMPOSABLE   DOUBLE PRECISION,
    AFFE_DEP_COMPLEMENTAIRE  DOUBLE PRECISION,
    AFFE_MODE_DEPLACEMENT    CHAR(1) CHARACTER SET NONE,
    AFFE_COMMENTAIRE         BLOB SUB_TYPE 0 SEGMENT SIZE 80,
    AFFE_DEVISE              CHAR(3) CHARACTER SET NONE,
    AFFE_CONNECTE            CHAR(1) CHARACTER SET NONE,
    AFFE_PRESENCE            CHAR(1) CHARACTER SET NONE
);
 
 
CREATE TABLE AGENTS (
    AGENT_SOCIETE             CHAR(15),
    AGENT_MATRICULE           DOUBLE PRECISION,
    AGENT_ETABLISSEMENT       CHAR(5) CHARACTER SET NONE,
    AGENT_TITRE               CHAR(12) CHARACTER SET NONE,
    AGENT_NOM                 CHAR(30) CHARACTER SET NONE,
    AGENT_PRENOM              CHAR(30) CHARACTER SET NONE,
    AGENT_JFILLE              CHAR(30) CHARACTER SET NONE,
    AGENT_NUM_SECU            CHAR(30) CHARACTER SET NONE,
    AGENT_ADRESSE             CHAR(30) CHARACTER SET NONE,
    AGENT_ADRESSE1            CHAR(30) CHARACTER SET NONE,
    AGENT_CP                  CHAR(6) CHARACTER SET NONE,
    AGENT_BP                  CHAR(10) CHARACTER SET NONE,
    AGENT_VILLE               CHAR(30) CHARACTER SET NONE,
    AGENT_PAYS                CHAR(20) CHARACTER SET NONE,
    AGENT_FAX                 CHAR(20) CHARACTER SET NONE,
    AGENT_TELEPHONE           CHAR(20) CHARACTER SET NONE,
    AGENT_PORTABLE            CHAR(20) CHARACTER SET NONE,
    AGENT_EMAIL               CHAR(20) CHARACTER SET NONE,
    AGENT_NATIONALITE         CHAR(20) CHARACTER SET NONE,
    AGENT_TYPE_SALAIRE        CHAR(20) CHARACTER SET NONE,
    AGENT_EMPLOI              CHAR(30) CHARACTER SET NONE,
    AGENT_QUALIFICATION       CHAR(30) CHARACTER SET NONE,
    AGENT_HORAIRE_BASE        DOUBLE PRECISION,
    AGENT_ANCIENNETE          DOUBLE PRECISION,
    AGENT_REPOS_COMPENSATEUR  DOUBLE PRECISION,
    AGENT_SALAIRE             DOUBLE PRECISION,
    AGENT_CP_ACQUIS_NB_P      DOUBLE PRECISION,
    AGENT_CP_ACQUIS_NB_E      DOUBLE PRECISION,
    AGENT_CP_ACQUIS_NB_A      DOUBLE PRECISION,
    AGENT_CP_PRIS_NB_P        DOUBLE PRECISION,
    AGENT_CP_PRIS_NB_E        DOUBLE PRECISION,
    AGENT_CP_PRIS_NB_A        DOUBLE PRECISION,
    AGENT_CP_RESTE_NB_P       DOUBLE PRECISION,
    AGENT_CP_RESTE_NB_E       DOUBLE PRECISION,
    AGENT_CP_RESTE_NB_A       DOUBLE PRECISION,
    AGENT_DATE_ENTREE         CHAR(10) CHARACTER SET NONE,
    AGENT_DATE_SORTIE         CHAR(10) CHARACTER SET NONE,
    AGENT_NUM_CONTRAT         CHAR(15) CHARACTER SET NONE,
    AGENT_TYPE_CONTRAT        CHAR(30) CHARACTER SET NONE,
    AGENT_TAUX_HOR_CONTRAT    DOUBLE PRECISION,
    AGENT_CATEGORIE           CHAR(1) CHARACTER SET NONE,
    AGENT_CHARGE_AFFAIRE      CHAR(1) CHARACTER SET NONE,
    AGENT_TAUX_HORAIRE        DOUBLE PRECISION,
    AGENT_NIVEAU              DOUBLE PRECISION,
    AGENT_COEFFICIENT         DOUBLE PRECISION,
    AGENT_INDICE              DOUBLE PRECISION,
    AGENT_RTT                 DOUBLE PRECISION,
    AGENT_MODULATION          DOUBLE PRECISION
);
 
 
CREATE TABLE CHANTIERS (
    CHAN_SOCIETE                    CHAR(15) CHARACTER SET NONE,
    CHAN_NUMERO                     CHAR(4) CHARACTER SET NONE NOT NULL,
    CHAN_CLIENT                     CHAR(13) CHARACTER SET NONE NOT NULL,
    CHAN_NOMCLIENT                  CHAR(30) CHARACTER SET NONE NOT NULL,
    CHAN_ADRESSE                    CHAR(30) CHARACTER SET NONE,
    CHAN_ADRESSE1                   CHAR(30) CHARACTER SET NONE,
    CHAN_ADRESSE2                   CHAR(30) CHARACTER SET NONE,
    CHAN_ADRESSE3                   CHAR(30) CHARACTER SET NONE,
    CHAN_ADRESSE4                   CHAR(30) CHARACTER SET NONE,
    CHAN_ADR_COURRIER               CHAR(30) CHARACTER SET NONE,
    CHAN_ADR1_COURRIER              CHAR(30) CHARACTER SET NONE,
    CHAN_ADR2_COURRIER              CHAR(30) CHARACTER SET NONE,
    CHAN_ADR3_COURRIER              CHAR(30) CHARACTER SET NONE,
    CHAN_ADR4_COURRIER              CHAR(30) CHARACTER SET NONE,
    CHAN_DEMARRAGE                  DATE,
    CHAN_FIN_CHANTIER               DATE,
    CHAN_AFFAIRE                    CHAR(6) CHARACTER SET NONE NOT NULL,
    CHAN_CHARGE                     CHAR(30) CHARACTER SET NONE,
    CHAN_RESP_TRAVAUX               CHAR(30) CHARACTER SET NONE,
    CHAN_TEL_RESP_TRAVAUX           CHAR(20) CHARACTER SET NONE,
    CHAN_TEL_PORTABLE_RESP_TRAVAUX  CHAR(20) CHARACTER SET NONE,
    CHAN_RESP_CLIENT                CHAR(30) CHARACTER SET NONE,
    CHAN_TEL_RESP_CLIENT            CHAR(20) CHARACTER SET NONE,
    CHAN_TEL_PORTABLE_RESP_CLIENT   CHAR(20) CHARACTER SET NONE,
    CHAN_DATE_PREVU                 DATE,
    CHAN_LIBELLE_AFFAIRE            CHAR(50) CHARACTER SET NONE,
    CHAN_DATE_AFFAIRE_DEBUT         DATE,
    CHAN_DATE_AFFAIRE_FIN           DATE,
    CHAN_LIEU                       CHAR(30) CHARACTER SET NONE,
    CHAN_CONNECTE                   CHAR(1) CHARACTER SET NONE
);
 
 
 
 
/******************************************************************************/
/***                              Primary Keys                              ***/
/******************************************************************************/
 
ALTER TABLE AGENTS ADD CONSTRAINT PK_AGENTS PRIMARY KEY (AGENT_SOCIETE, AGENT_MATRICULE);
 
 
/******************************************************************************/
/***                                Indices                                 ***/
/******************************************************************************/
 
CREATE INDEX AFFE_CHANTIER ON AFFECTATION (AFFE_CHANTIER);
CREATE INDEX AFFE_MATRICULE ON AFFECTATION (AFFE_MATRICULE);
CREATE INDEX AGENTS_MATRICULE ON AGENTS (AGENT_MATRICULE, AGENT_NOM, AGENT_PRENOM);
CREATE INDEX AGENTS_NOM ON AGENTS (AGENT_NOM, AGENT_PRENOM);
CREATE INDEX AGENTS_SOCIETE ON AGENTS (AGENT_SOCIETE, AGENT_MATRICULE);
CREATE INDEX AGENT_MATRICULE ON AGENTS (AGENT_MATRICULE);
CREATE INDEX AGENT_NOM ON AGENTS (AGENT_NOM, AGENT_PRENOM);
CREATE INDEX CHAN_NUMERO ON CHANTIERS (CHAN_NUMERO);

Y a t il moyen d'optimiser ceci

Merci

[makowski]

surement oui
mais pour cela, il faut un peu plus de précision sur pourquoi par exemple avoir choisit DOUBLE PRECISION pour les champs numérique
et avoir les statistiques de la base en ce qui concerne les index

par contre un petit truc : éviter d'utiliser l'extension .GDB sous Windows, cela cause des ralentissement du fait du system restore de Windows

autre chose c'est Firebird qui est utilisé ?, si oui quelle version ?

sur les index on peut déjà dire que certains sont inutiles car il font double emploi :
CREATE INDEX AGENTS_MATRICULE ON AGENTS (AGENT_MATRICULE, AGENT_NOM, AGENT_PRENOM);
CREATE INDEX AGENTS_NOM ON AGENTS (AGENT_NOM, AGENT_PRENOM);
CREATE INDEX AGENT_MATRICULE ON AGENTS (AGENT_MATRICULE);
CREATE INDEX AGENT_NOM ON AGENTS (AGENT_NOM, AGENT_PRENOM);

le premier des 4 suffit :
CREATE INDEX AGENTS_MATRICULE ON AGENTS (AGENT_MATRICULE, AGENT_NOM, AGENT_PRENOM);

[Yurck]

Bonjour,

Le premier suffit ?

Je me suis laissé dire (et je l'ai vérifié par test à l'époque avec IB 5) qu(il valait mieux avoir trois index sur chacun des champs plutôt que un sur les trois.
Ainsi :
CREATE INDEX AGENTS_NOM ON AGENTS (AGENT_NOM);
CREATE INDEX AGENT_MATRICULE ON AGENTS (AGENT_MATRICULE);
CREATE INDEX AGENT_PRENOM ON AGENTS (AGENT_PRENOM);

serait meilleur que
CREATE INDEX AGENTS_MATRICULE ON AGENTS (AGENT_MATRICULE, AGENT_NOM, AGENT_PRENOM);

Cela offrirait plus de possibilité et surtout éviterait les erreurs d'interprétation de l'optimiseur qui prendrait en compte le nom du premier champ et la profondeur de l'index.
Par exemple :

select ..
from ...
where AGENT_MATRICULE = 'toto' and AGENT_NOM = 'titi' and AGENT_PRENOM ='tutu'

utilserait le même index que pour

select ..
from ...
where AGENT_MATRICULE = 'toto' and AGENT_NOM = 'titi' and AGENT_VILLE ='Paris'

et ce même si
CREATE INDEX AGENTS_MATRICULE ON AGENTS (AGENT_MATRICULE, AGENT_NOM, AGENT_VILLE);

existe.
J'utilise le conditionnel car je n'ai pas refait les tests depuis IB 5.0.742.

[makowski]

des index sur un seul champ auront forcément une plus faible sélectivité et donc seront moins efficaces
par contre c'est vrai que si tu as un index (A,B) il sera utilisé pour une order by sur A, sur A,B (pas B,A), une recherche sur A, mais pas sur B, tu as raison sur ce point
maintenant, les index on un coût, donc tout dépend des recherches et tris utilisés dans l'exploitation de la base
donc j'ai causé un petit peu vite
donc plutot que :
CREATE INDEX AGENTS_MATRICULE ON AGENTS (AGENT_MATRICULE, AGENT_NOM, AGENT_PRENOM);
CREATE INDEX AGENTS_NOM ON AGENTS (AGENT_NOM, AGENT_PRENOM);
CREATE INDEX AGENTS_SOCIETE ON AGENTS (AGENT_SOCIETE, AGENT_MATRICULE);
CREATE INDEX AGENT_MATRICULE ON AGENTS (AGENT_MATRICULE);
CREATE INDEX AGENT_NOM ON AGENTS (AGENT_NOM, AGENT_PRENOM);

à priori il vaut mieux :
CREATE INDEX AGENTS_MATRICULE ON AGENTS (AGENT_MATRICULE, AGENT_NOM, AGENT_PRENOM);
CREATE INDEX AGENTS_NOM ON AGENTS (AGENT_NOM, AGENT_PRENOM);

je supprime l'index AGENTS_SOCIETE car c'est déjà celui de la clé primaire crée avant