Discussion :

[Reisubar]

Bonjour,

J'utilise depuis longtemps une requête assez simple pour mettre à jour un ensemble de records :

Code :

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

UPDATE T1 SET CHAMP=:Champ WHERE X IN (SELECT Y FROM T2 WHERE Z=:Valeur)

Depuis quelques temps cette requête est très longue à s'exécuter. Je ne sais pas vraiment pourquoi, à part que la base a pas mal grossi... Mais j'en suis à 6/7 secondes, ce qui me parait un peu excessif.

Y a t-il quelque chose à améliorer dans cette requête ? T1 et T2 sont "reliés" via une table intermédiaire, mais j'ai vu que FB ne supportait pas els jointures dans les UPDATE. Je ne vois donc pas de construction plus "intelligente" pour éviter que FB parcoure tout T1...

Ma spécialité n'étant pas les BD, si vous avez une idée je suis preneur :-)

J'utilise FB 2.1.

Merci

[makowski]

donne la structure des tables qu'on y voit plus clair

[Reisubar]

Bon, j'ai tourné le problème dans tous les sens et je ne comprends pas.

J'ai une structure de tables de cette forme

TVERSIONS (ID, .., .., FILEID)
TFILES (ID, .., ROOTID)
TROOTS (ID, ...)

Les champs soulignés sont des clés primaires. Les autres sont des clés étrangères qui référencent la table du dessous. La structure me parait simple et pas du tout exotique. Les clés étrangères sont (forcément) indexées.

Ma requête est la suivante : je veux mettre à jour une colonne pour un ensemble de records dans TVERSIONS appartenant à TROOTS satisfaisant une seule condition. J'ai donc dans ma requète :
- Le code de mise à jour du champ,
- Le code de jointure des tables, tout con, qui passe par des champs indexés, donc devrait être très rapide :
- Le code de test de ma condition,

Code :

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

1
2
3
4
UPDATE TVERSIONS SET TVERSIONS.CHAMPX=X 
  WHERE TVERSIONS.FILEID IN (SELECT TFILES.ID FROM TFILES
     JOIN TROOTS ON TROOTS.ID=:RootID)
  AND TVERSIONS.CHAMPZ=:Z

Le plan généré par FB est le suivant :

Code :

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

PLAN JOIN (TFILES INDEX (PK_TFILES), TROOTS INDEX (PK_ROOTS)) PLAN (TVERSIONS NATURAL)

Je ne comprends pas d'où sort ce PLAN (TVERSIONS NATURAL) !!
Pourquoi à t'il besoin de parcourir toute la table TVERSIONS puisque le premier critère "limitatif" consiste en la sélection d'enregistrement via le champ indexé FILEID ?

Alors forcément, le parcours complet de TVERSIONS sur 300000 enregistrements prend environ 30s, ce qui est beaucoup trop long...

Voilà, j'espère avoir été plus précis et complet !

[makowski]

un IN (SELECT ...) n'utilise pas d'index

tu pourrais créer un index sur TVERSIONS.CHAMPZ

et tu es sur qu'il ne manque pas qq chose là :

Code :

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

1
2
(SELECT TFILES.ID FROM TFILES
     JOIN TROOTS ON TROOTS.ID=:RootID)

?

c'est pas plutot :

Code :

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

1
2
(SELECT TFILES.ID FROM TFILES
     JOIN TROOTS ON TROOTS.ID=TFILES.ROOTID AND TROOTS.ID=:RootID)

mais tu peux peut être utiliser qq chose comme :

Code :

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

1
2
3
4
5
6
 
UPDATE TVERSIONS SET TVERSIONS.CHAMPX=X
WHERE TVERSIONS.ID=(SELECT A.ID FROM TVERSIONS A JOIN TFILES ON TFILES.ID=A.FILEID 
JOIN TROOTS ON TROOTS.ID=TFILES.ROOTID AND TROOTS.ID=:RootID 
WHERE TVERSIONS.ID=A.ID)
 AND TVERSIONS.CHAMPZ=:Z

[Reisubar]

Merci de ton aide !

un IN (SELECT ...) n'utilise pas d'index

Cool, bon à savoir. Ca explique tout du coup :-)

tu pourrais créer un index sur TVERSIONS.CHAMPZ

Oui, j'y ait pensé mais ce champ n'est pas très sélectif (il peut y avoir une dizaines de valeurs différentes max) donc bon...

et tu es sur qu'il ne manque pas qq chose là :

Si :-) Mauvais copié-collé, désolé !

mais tu peux peut être utiliser qq chose comme :

IB Expert me donne toujours un plan avec NATURAL avec ta requête :

Code :

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

1
2
PLAN JOIN (JOIN (A INDEX (PK_TVERSIONS), TFILES INDEX (PK_TFILES)), TROOTS INDEX (PK_ROOTS))
PLAN (TVERSIONS NATURAL)

Ta requête ne fonctionnait qu'avec l'index que tu me proposais de créer ?

Merci beaucoup de ton expertise :-)

[makowski]

après Natural, n'est pas forcément mauvais, parcontre, il faut voire le nombre de lecture par table (moins de lecture = plus rapide)

L'autre solution est d'écrire une procédure stockée avec un curseur
c'est même la meilleure solution dans ce cas je pense

D'ailleurs avec un index :

Code :

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

CREATE INDEX IDX_TVERSIONS3 ON TVERSIONS (FILEID,CHAMPZ);

voilà ce que donne :

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
SET TERM !!;
EXECUTE BLOCK
AS
  DECLARE ID CHAR(5);
  DECLARE CUR CURSOR FOR
  (SELECT TVERSIONS.CHAMPX FROM TVERSIONS,TFILES,TROOTS WHERE TFILES.ID=TVERSIONS.FILEID 
AND TROOTS.ID=TFILES.ROOTID AND TROOTS.ID=0 AND TVERSIONS.CHAMPZ=0);
BEGIN
 OPEN CUR;
 WHILE (1=1) DO
 BEGIN
   FETCH CUR INTO :ID;
   IF (ROW_COUNT=0) THEN LEAVE;
   UPDATE TVERSIONS SET CHAMPX='X' WHERE CURRENT OF CUR;
  END
  CLOSE CUR; 
END
!!
SET TERM ;!!

PLAN JOIN (TFILES NATURAL, TVERSIONS INDEX (IDX_TVERSIONS3), TROOTS INDEX (PKTROOTS))

[Reisubar]

Merci pour tes conseils, je vais essayer de mettre ça en oeuvre !