Optimisation requête SQL 2

Version imprimable

1 pièce(s) jointe(s)

Bonjour,
je souhaiterais obtenir pour un client précis toutes lignes d'un BL pour lequel l'état du lancement n'est pas soldé ou annulée et il n'y a pas de commande (CDE_ID=0).
Voici la requête :
Code:

1 2 3 4 5 SELECT NO_Offre FROM ((BL_LIGNE BLL INNER JOIN BL_ENTETE BLE ON BLE.LISTENOBL_ID=BLL.LISTENOBL_ID and BLE.CLIENT_ID=433) INNER JOIN LANCEMENT LCT ON BLL.LCT_ID=LCT.LCT_ID and LCT.ETAT_ID<>2 AND LCT.ETAT_ID<>5 and LCT.CDE_ID=0) INNER JOIN OFFRE OFR ON LCT.OFR_ID=OFR.OFR_ID
J'obtiens 15 secondes de temps d'exécution.
J'ai vérifier que tous les indexes soient bien définis.
La requête me retourne 1 seule ligne.
Si j'enlève la condition LCT.CDE_ID=0 le temps est de <1s.
De même si j'enlève la table des offres le temps est d'environ 2s.

Qu'est qui ne va pas? Y-a-t-il un moyen de réduire ce temps de 15s?
J'ai fait la requête sur firebird 1.5. (pas encore près pour la 2.1...)
Vous trouverez le diagramme en pièce jointe.

Et comme ça, en utilisant WHERE

Code:

1
2
3
4
5
6
7
8
9
10
11
SELECT 
  NO_Offre
FROM
  BL_LIGNE BLL  
  INNER JOIN BL_ENTETE BLE ON BLE.LISTENOBL_ID = BLL.LISTENOBL_ID
  INNER JOIN LANCEMENT LCT ON  BLL.LCT_ID = LCT.LCT_ID
  INNER JOIN OFFRE OFR ON LCT.OFR_ID = OFR.OFR_ID
WHERE
  BLE.CLIENT_ID = 433 AND
  LCT.ETAT_ID NOT IN (2, 5) AND 
  LCT.CDE_ID = 0

Ensuite il serait peut-être intéressant de regarder le PLAN utilisé par cette requête.

@+ Claudius

09/10/2008, 13h46
ludo00002

j'ai fait le test et ça fait toujours 15 secondes.
Voici le plan :
PLAN JOIN (LCT INDEX (FK_Lancement_6),OFR INDEX (RDB$PRIMARY1),BLE INDEX (FK_BL_ENTETE_3),BLL INDEX (RDB$FOREIGN105,RDB$FOREIGN11))
09/10/2008, 13h53
makowski

donnes la structure des tables avec leurs index et les plans de requêtes

Citation:

Envoyé par makowski

donnes la structure des tables avec leurs index et les plans de requêtes

[QUOTE]

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
SET SQL DIALECT 3;
 
CREATE DATABASE 'c:\Base\TEST'
PAGE_SIZE 4096
DEFAULT CHARACTER SET NONE;
 
/* Table: BL_ENTETE, Owner: SYSDBA */
 
CREATE TABLE "BL_ENTETE" 
(
  "BLE_ID"	 INTEGER NOT NULL,
  "BLE_NO"	 VARCHAR(32),
  "CLT_ID"	 INTEGER NOT NULL,
CONSTRAINT "PK_BL_ENTETE" PRIMARY KEY ("BLE_ID")
);
 
/* Table: BL_LIGNE, Owner: SYSDBA */
 
CREATE TABLE "BL_LIGNE" 
(
  "BLL_ID"	 INTEGER NOT NULL,
  "BLE_ID"	 INTEGER NOT NULL,
  "LCT_ID"	 INTEGER NOT NULL,
CONSTRAINT "PK_BL_LIGNE" PRIMARY KEY ("BLL_ID")
);
 
/* Table: LANCEMENT, Owner: SYSDBA */
 
CREATE TABLE "LANCEMENT" 
(
  "LCT_ID"	 INTEGER NOT NULL,
  "OFR_ID"	 INTEGER NOT NULL,
  "CDE_ID"	 INTEGER NOT NULL,
  "ETAT_ID"	 INTEGER NOT NULL,
CONSTRAINT "PK_LANCEMENT" PRIMARY KEY ("LCT_ID")
);
 
/* Table: OFFRE, Owner: SYSDBA */
 
CREATE TABLE "OFFRE" 
(
  "OFR_ID"	 INTEGER NOT NULL,
  "NO_OFFRE"	 VARCHAR(32) NOT NULL,
CONSTRAINT "PK_OFFRE" PRIMARY KEY ("OFR_ID")
);
 
/*  Index definitions for all user tables */
 
CREATE INDEX "IDX_LANCEMENT_1" ON "LANCEMENT"("CDE_ID");
CREATE INDEX "IDX_LANCEMENT_2" ON "LANCEMENT"("ETAT_ID");
ALTER TABLE "BL_LIGNE" ADD CONSTRAINT "FK_BL_LIGNE_1" FOREIGN KEY ("BLE_ID") REFERENCES "BL_ENTETE" ("BLE_ID") ON UPDATE CASCADE ON DELETE NO ACTION;
ALTER TABLE "BL_LIGNE" ADD CONSTRAINT "FK_BL_LIGNE_2" FOREIGN KEY ("LCT_ID") REFERENCES "LANCEMENT" ("LCT_ID") ON UPDATE CASCADE ON DELETE NO ACTION;
ALTER TABLE "LANCEMENT" ADD CONSTRAINT "FK_LANCEMENT_1" FOREIGN KEY ("OFR_ID") REFERENCES "OFFRE" ("OFR_ID") ON UPDATE CASCADE ON DELETE NO ACTION;
ALTER TABLE "LANCEMENT" ADD CONSTRAINT "FK_LANCEMENT_2" FOREIGN KEY ("LCT_ID") REFERENCES "LANCEMENT" ("LCT_ID") ON UPDATE CASCADE ON DELETE NO ACTION;

Voici le plan :

Code:

1
2
3
4
5
 
PLAN JOIN (LCT INDEX (IDX_LANCEMENT_1),
 OFR INDEX (PK_OFFRE),
 BLL INDEX (FK_BL_LIGNE_1,FK_BL_LIGNE_2),
 BLE INDEX (PK_BL_ENTETE))

09/10/2008, 14h59
makowski

il faut rajouter des index
le premier serait à priori sur la table LANCEMENT sur les champs (LCT_ID,CDE_ID)

puis regarder les statistiques des requetes, le nombre de lecture faites
09/10/2008, 15h40
ludo00002

Citation:

Envoyé par makowski

il faut rajouter des index
le premier serait à priori sur la table LANCEMENT sur les champs (LCT_ID,CDE_ID)

puis regarder les statistiques des requetes, le nombre de lecture faites

En fait j'ai bien l'index CDE_ID (j'ai même créé en plus l'index sur (LCT,CDE_ID)) sur la table Lancement mais apparemment il ne l'utilise pas dans le plan. Sans aller plus loin, c'est peut-être ça le problème?
09/10/2008, 16h17
makowski

Citation:

Envoyé par ludo00002

En fait j'ai bien l'index CDE_ID (j'ai même créé en plus l'index sur (LCT,CDE_ID)) sur la table Lancement mais apparemment il ne l'utilise pas dans le plan. Sans aller plus loin, c'est peut-être ça le problème?

en partie oui
met à jour les statistiques des index
mais c'est peut être aussi une des limites de l'optimizeur dans Firebird 1.5
beaucoup de travail a été fait de ce point de vue dans les version 2.x de Firebird
mais là comme ça sans les données réelles, sans le nombre de lectures faites, difficile à dire
09/10/2008, 16h59
ludo00002

J'ai regardé le plan qu'il a fait sous firebird 2.1 (en local) et je l'ai reproduit sous la 1.5. Sous la 2.1 est local c'est moins de 1s (mon PC est beaucoup plus puissant que le serveur) et sur le serveur le temps est 15s.

Voici les stats :
Exec time : 00:00:14:0906
Prepare time : 00:00:00:0015
Starting memory 19832208
Current memory 19916872
Delta memory 84664
number of buffers 2048
Reads 914039
Writes 219516
Rows affected 14
09/10/2008, 17h08
makowski

j'aurais préféré avoir de meilleures stats de lecture (par table) comme les donne Databaseworkbench par exemple
mais bon, encore une bonne raison de dire au revoir et merci à la 1.5 et passer à la 2.1

mais quand même, c'est quoi les stats de la base ?
(gstat -h)
09/10/2008, 17h17
ludo00002

Flags 0
Checksum 12345
Generation 131642
Page size 4096
ODS version 10.1
Oldest transaction 104752
Oldest active 112589
Oldest snapshot 111980
Next transaction 131618
Bumped transaction 1
Sequence number 0
Next attachment ID 0
Implementation ID 16
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Aug 26, 2008 8:37:31
Attributes

Variable header data:
Sweep interval: 20000
*END*
09/10/2008, 18h29
makowski

Citation:

Oldest transaction 104752
Oldest active 112589
Oldest snapshot 111980
Next transaction 131618

pas terrible tout ça, Il y avait beacoup d'activité sur la base à ce moment là ?
après un sweep dans une période calme, voire même sans personne d'autre de connecté sur la base, la requète ira plus vite et pas seulement celle là
il faudrait regarder un peu comment sont gérées les transactions dans les applis utilisant cette base.
10/10/2008, 09h18
ludo00002

ça fait 1 mois et 1/2 que je n'ai pas fait de backup. J'ai fait un sweep hier soir. Voici les stats de ce matin.
Je sais que c'est pas terrible. Je n'ai pas le choix, tant que j'utiliserais les composants IBX ce sera comme ça je pense.

Flags 0
Checksum 12345
Generation 131937
Page size 4096
ODS version 10.1
Oldest transaction 111999
Oldest active 112589
Oldest snapshot 111980
Next transaction 131912
Bumped transaction 1
Sequence number 0
Next attachment ID 0
Implementation ID 16
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Aug 26, 2008 8:37:31
Attributes

Variable header data:
Sweep interval: 20000
*END*
10/10/2008, 12h29
makowski

non, les IBX n'y sont pour rien, c'est de la faute du developpeur