Requête Update avec 3 tables et jointures

**cantador** · 19/10/2015, 19h11

merci escartefigue pour ton intervention qui m'a donné à réfléchir..

désolé, j'ai fermé le post un peu vite..
certains désirent peut-être ajouter des commentaires ou suggestions.

en ce qui me concerne, j'aurais bien aimé avoir la version
procédure stockée qui pourrait servir à l'occasion.

je ré-ouvre le post !

cantador

**SergioMaster** · 20/10/2015, 08h30

Bonjour,

Envoyé par cantador

en ce qui me concerne, j'aurais bien aimé avoir la version
procédure stockée qui pourrait servir à l'occasion.

1- Sans un script de création des tables (pour pouvoir écrire ça de manière correcte avec les bons champs etc.... )
donc au débotté, sans contrôle de syntaxe

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
SET TERM ^ ;
 
CREATE PROCEDURE UPDATE_SURFACE 
RETURNS 
 ( NOMBREMODIF : INTEGER )
AS 
DECLARE VARIABLE RC ; // à rajouter le type = type NEW_REF_CADAST
DECLARE VARIABLE CS ; // à rajouter le type = type COMMUNE_PACAGE
BEGIN
  FOR  SELECT p.NEW_REF_CADAST,p.COMMUNE_PACAGE
         FROM PACAGE p INNER JOIN EXPLOITANT e on p.NOPACAGE=e.PACAGE  
        WHERE  e.NOM is not null
        INTO :RC,CS
   DO BEGIN
       UPDATE  SURFACE SET PRESENCE_EXPLOITANT = '1' WHERE REFERENCE_CADASTRALE=:RC AND  COMMUNE_SURFACE=:CS;
       NOMBREMODIF=NOMBREMODIF+1;
   END
  SUSPEND;
END^
 
SET TERM ; ^

et je suis presque sûr que cela sera plus rapide, nota : cerise sur le gâteau pas forcément nécessaire je rapporte de nombre de modifications => un SELECT * FROM UPDATE_SURFACE

comme je l'ai déjà indiqué depuis l'arrivée de EXECUTE BLOCK la procédure incluse donc dans la BDD ne serait pas nécessaire , avec quelques modifications dans le code de la procédure c'est certainement possible . sans le point 1 je ne me lance pas dans une écriture

**cantador** · 20/10/2015, 08h33

@escartefigue
4'30"
mais c'est l'Amérique!
si cette manœuvre était à effectuer quotidiennement, ce serait galère
mais comme cela se produira une fois par an et en dehors de l'application
par l'administrateur..

cantador

**cantador** · 20/10/2015, 11h26

voilà :

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
 
CREATE PROCEDURE UPDATE_SURFACE
RETURNS(
  NOMBREMODIF INTEGER)
AS
DECLARE VARIABLE RC VARCHAR(6);
DECLARE VARIABLE CS VARCHAR(30);
BEGIN
  FOR  SELECT p.NEW_REF_CADAST,p.COMMUNE_PACAGE
         FROM PACAGE p INNER JOIN EXPLOITANT e on p.NOPACAGE=e.PACAGE  
        WHERE e.NOM is not null
        INTO :RC,CS
   DO BEGIN
       UPDATE  SURFACE SET PRESENCE_EXPLOITANT = '1' 
       WHERE REFERENCE_CADASTRALE=:RC AND  COMMUNE_SURFACE=:CS;
       NOMBREMODIF=NOMBREMODIF+1;
   END
  SUSPEND;
END
;

l'exécution du CREATE créée bien la procédure :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
BEGIN
  FOR  SELECT p.NEW_REF_CADAST,p.COMMUNE_PACAGE
         FROM PACAGE p INNER JOIN EXPLOITANT e on p.NOPACAGE=e.PACAGE  
        WHERE e.NOM is not null
        INTO :RC,CS
   DO BEGIN
       UPDATE  SURFACE SET PRESENCE_EXPLOITANT = '1' 
       WHERE REFERENCE_CADASTRALE=:RC AND  COMMUNE_SURFACE=:CS;
       NOMBREMODIF=NOMBREMODIF+1;
   END
  SUSPEND;
END

son exécution :

Code :

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

1
2
 
EXECUTE PROCEDURE UPDATE_SURFACE;

met curieusement 5'24"
l'update oscille lui, entre 4'15" et 4'30"..

j'aurais bien aimé afficher le nombre de lignes mises à jour, c'est-à-dire, afficher la variable d'incrémentation NOMBREMODIF..

je code dans SQL Manager For

Code :

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

1
2
 
EXECUTE PROCEDURE UPDATE_SURFACE RETURNING_VALUES NOMBREMODIF;

mais il me jette méchamment alors que pourtant je ne lui ai rien dit de mal..

souci de syntaxe peut-être ?

cantador

**SergioMaster** · 20/10/2015, 12h29

toc toc

Envoyé par sergiomaster

et je suis presque sûr que cela sera plus rapide, nota : cerise sur le gâteau pas forcément nécessaire je rapporte de nombre de modifications => un SELECT * FROM UPDATE_SURFACE

je croyais que le => était clair

si cela met toujours autant de temps alors c'est bien coté index qu'il faut chercher !

CREATE INDEX IDX_CADASURF ON SURFACE(REFERENCE_CADASTRALE,COMMUNE_SURFACE);

et à moins qu'il n'y ait déjà une contrainte référentielle ?
CREATE INDEX IDX_EXPPACAGE ON EXPLOITANT(PACAGE)

**cantador** · 20/10/2015, 15h54

oui j'ai zappé ton commentaire..
j'ai bien compris qu'avec le SELECT, on exécutait la procédure stockée avec affichage du nombre d'enregistrements modifiés..

il n'y a pas de contrainte et
pour les index, effectivement, je n'ai pas crée d'index composite pour ne pas gêner d'autres développements actuels et ultérieurs..
c'est un choix, mais les temps d'exécution sont bons et me conviennent.

en revanche, j'ai lu le lien sur EXECUTE BLOCK et si j'ai bien compris, une fois la procédure stockée, on peut
la lancer en utilisant qu'une partie du code, ce qui est intéressant.

tu lances un SELECT * from NOM DE LA PROCEDURE

mais où est l'instruction EXECUTE BLOCK ?

merci

cantador

**cantador** · 20/10/2015, 16h28

à noter que si je lance dans l'éditor de SQL manger :

Code :

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

1
2
 
execute procedure UPDATE_SURFACE;

à la fin de l'exécution, il affiche la zone
NOMBREMODIF mais = Null
?

Toutefois, la requête est parfaitement exécutée et tous les enregistrements sont renseignés..

cantador

**cantador** · 21/10/2015, 15h59

@SergioMaster

autre chose, j'ai quand même, afin d'en avoir le cœur net, fait quelques essais avec des clés composites
mais comme je le craignais, toutes les requêtes s'allongent..
@+

cantador

**SergioMaster** · 21/10/2015, 16h28

Bonjour,

je croyais avoir répondu hier soir , mais apparemment déjà bien fatigué je n'ai pas cliqué sur envoi !
au sujet de

en revanche, j'ai lu le lien sur EXECUTE BLOCK et si j'ai bien compris, une fois la procédure stockée, on peut
la lancer en utilisant qu'une partie du code, ce qui est intéressant.

je ne crois pas que tu ais vraiment compris, execute block permet de faire un ensemble d'instructions SQL (comme une procédure donc) mais sans que cet ensemble soit mémorisé dans la base de données (contrairement en cela à la procédure)

à noter que si je lance dans l'éditor de SQL manger :
execute procedure UPDATE_SURFACE;à la fin de l'exécution, il affiche la zone
NOMBREMODIF mais = Null

Cela prouve simplement que SQL manager sait qu'il peut récupérer le champ NOMBREMODIF mais que comme l'instruction (EXECUTE PROCEDURE) ne le demande pas, il renvoi null . Ceci à contrario du SELECT * FROM <nom de la procedure> qui explicitement demande les champs renvoyés par la procédure

j'ai quand même, afin d'en avoir le cœur net, fait quelques essais avec des clés composites
mais comme je le craignais, toutes les requêtes s'allongent..

ça, je trouve cela plutôt étrange. l'index ne sera maintenu qu'en cas de INSERT ou d'UPDATE et utilisé qu'en cas de besoin.
mais je pourrais aussi te rétorquer que tu peux toujours construire l'index uniquement dans la procédure et le détruire en fin de cette dernière (et ce toujours dans le corps de la procédure)
il faudrait également que tu te penches sur les Plans du SELECT et du UPDATE pour vérifier que ce sont bien les bons index qui sont utilisés.
De même d'ailleurs que les plans de toutes "les requêtes qui s'allongent"

NB. je ne sais pas si un ORDER BY pour le SELECT n'améliorerait pas aussi un peu la vitesse d'exécution de la boucle (à voir)

**escartefigue** · 22/10/2015, 08h58

Envoyé par cantador

@SergioMaster

autre chose, j'ai quand même, afin d'en avoir le cœur net, fait quelques essais avec des clés composites
mais comme je le craignais, toutes les requêtes s'allongent..
@+

cantador

Si la taille de votre clef composée excède celle de votre processeur, alors votre CPU doit manipuler la valeur de clef en 2 fois, ceci peut expliquer cela.
C'est l'une des raisons pour lesquelles il est recommandé d'avoir des colonnes clefs concises.

**SergioMaster** · 22/10/2015, 10h24

Bonjour,

Envoyé par escartefigue

Si la taille de votre clef composée excède celle de votre processeur, alors votre CPU doit manipuler la valeur de clef en 2 fois, ceci peut expliquer cela.

euh tu es sûr de toi là ? moi je me réfère à ce calculateur
après avoir lu cette FAQ comme on était à deux mains pour les traduire je ne sais si elle est dans le FAQ du forum

C'est l'une des raisons pour lesquelles il est recommandé d'avoir des colonnes clefs concises.

en tout cas, ça j'approuve

**cantador** · 22/10/2015, 11h13

je ne crois pas que tu ais vraiment compris, execute block permet de faire un ensemble d'instructions SQL (comme une procédure donc) mais sans que cet ensemble soit mémorisé dans la base de données (contrairement en cela à la procédure)

donc, finalement, ça revient au même que de lancer une requête SQL dans un éditeur..
à moins que EXECUTE BLOCK soit plus rapide.

Cela prouve simplement que SQL manager sait qu'il peut récupérer le champ NOMBREMODIF mais que comme l'instruction (EXECUTE PROCEDURE) ne le demande pas, il renvoi null . Ceci à contrario du SELECT * FROM <nom de la procedure> qui explicitement demande les champs renvoyés par la procédure

hélas non, je viens de tester le :

Code :

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

1
2
 
SELECT * FROM UPDATE_SURFACE

il fait le boulot, mais, le log affiche NOMBREMODIF= Null

Pour le reste, tout va bien, l'appli tourne sur une trentaine de postes.
toutes les requêtes se font dans la seconde avec une visualisation cartographique + végétation.
je vais pouvoir souffler un peu..
enfin, pas trop quand même, car il y a une suite
et d'autres programmes à construire !

mais je pourrais aussi te rétorquer que tu peux toujours construire l'index uniquement dans la procédure et le détruire en fin de cette dernière (et ce toujours dans le corps de la procédure)
il faudrait également que tu te penches sur les Plans du SELECT et du UPDATE pour vérifier que ce sont bien les bons index qui sont utilisés.
De même d'ailleurs que les plans de toutes "les requêtes qui s'allongent"

NB. je ne sais pas si un ORDER BY pour le SELECT n'améliorerait pas aussi un peu la vitesse d'exécution de la boucle (à voir)

oui, nous sommes d'accord sur ces initiatives

en tout cas merci à tous (je ne veux oublier personne),
pour vos précieux conseils.

cantador

**Barbibulle** · 22/10/2015, 12h06

Envoyé par cantador

@SergioMaster

autre chose, j'ai quand même, afin d'en avoir le cœur net, fait quelques essais avec des clés composites
mais comme je le craignais, toutes les requêtes s'allongent..
@+

cantador

En ajoutant une clé composite et en gardant les clés existantes il y a un allongement du temps des autres requêtes ? (Je parle des Select pas des Insert/update)

Et qu'elle est le gain sur l'update ou la PS de mise à jour ?

Envoyé par escartefigue

Si la taille de votre clef composée excède celle de votre processeur, alors votre CPU doit manipuler la valeur de clef en 2 fois, ceci peut expliquer cela.
C'est l'une des raisons pour lesquelles il est recommandé d'avoir des colonnes clefs concises.

Et donc cette clé composite serait plus longue à utiliser que d'utiliser une clé simple suivi de x manipulation de données / valeur de clé sélectionnée pour déterminer si la colonne non indexée correspond à la recherche ?

C'est effectivement contre-intuitif.

Je n'ai jamais constaté le cas mais il est vrai que j'ai peu voir jamais de clé composite.
Le seul cas qui me vient à l'esprits concerne un index composé de 3 champs un Integer et une date et un decimal(18,0). J'ai constaté un gain de 5 à 40 fois plus rapide sur certain traitements. (avant ces traitements utilisaient un autre index composite mais avec seulement 2 des champs). Les traitements boostés sont effectivement ceux qui utilisaient le 3eme champs dans ses critères de sélection. Mais je nais pas constaté de dégradation dans les autres traitements qui n'utilisent pas ce 3eme champs....

**SergioMaster** · 22/10/2015, 12h10

Envoyé par cantador

donc, finalement, ça revient au même que de lancer une requête SQL dans un éditeur..

pas tout à fait quand même puisque dans EXECUTE BLOCK on peut mettre plusieurs requêtes et utiliser des variables

hélas non, je viens de tester le :

Code :

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

1
2
 
SELECT * FROM UPDATE_SURFACE

il fait le boulot, mais, le log affiche NOMBREMODIF= Null

je me suis peut être planté à l'écriture de la procédure (en général je renvoi plus un ensemble d'enregistrements dans mes procédures et le SUSPEND se trouve par le fait dans la boucle).

du coup je doute alors après un test vite fait avec flamerobin

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
 
SET TERM #;
EXECUTE block
RETURNS (NB INTEGER)
AS
DECLARE A INTEGER; 
begin 
  NB=0;
  FOR SELECT 1 FROM APPOSE INTO :A DO
   begin
    NB=:NB+1;
   end 
  SUSPEND;
END#
SET TERM ; #

me renvoi 18
même chose en procédure

Code SQL :

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
SET TERM ^ ;
 
CREATE PROCEDURE TEST
RETURNS 
 (NB INTEGER)
AS 
DECLARE VARIABLE A INTEGER; 
BEGIN
  NB=0;
  FOR SELECT 1 FROM APPOSE INTO :A 
   DO begin 
     NB=NB+1;
   end
  SUSPEND;    
END^
 
SET TERM ; ^

SELECT * FROM TEST me donne 18

**cantador** · 26/10/2015, 15h11

de retour au pays..

@SergioMaster

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
 
SET TERM #;
EXECUTE block
RETURNS (NB INTEGER)
AS
DECLARE A INTEGER; 
begin 
  NB=0;
  FOR SELECT 1 FROM APPOSE INTO :A DO
   begin
    NB=:NB+1;
   end 
  SUSPEND;
END#
SET TERM ; #

n'affiche rien
?

mais en revanche, l'exécution de la procédure créée par

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
 
SET TERM ^ ;
 
CREATE PROCEDURE TEST
RETURNS 
 (NB INTEGER)
AS 
DECLARE VARIABLE A INTEGER; 
BEGIN
  NB=0;
  FOR SELECT 1 FROM APPOSE INTO :A 
   DO begin 
     NB=NB+1;
   end
  SUSPEND;    
END^
 
SET TERM ; ^

affiche la bonne valeur !
mais ne me donne pas la solution pour UPDATE_SURFACE..

mais ce n'est pas très important car on peut toujours vérifier le résultat par un SELECT après coup.

ce qui turlupine est plus le sujet évoqué par
@Barbibulle et les clés composite car j'ai le sentiment d'être passé à côté de quelque chose.
et dans ce contexte, j'ai une question qui me taraude :

exemple deux tables (table1 , champ1, champ2) (table2, champ1, champ2)
si j'ai à établir une requête SELECT avec deux jointures :
table1.champ1 avec table2.champ1
table1.champ2 avec table2.champ2

il est logique de créer un index composite sur les tables.
mais dans ce cas, cet index est créé sur les deux tables ou sur la plus petite des tables en taille

?
merci par avance

nb: j'ai une autre question sur un UPDATE multibases, mais cela fera l'objet d'un autre post, si nécessaire.

cantador

**escartefigue** · 26/10/2015, 15h14

Envoyé par Barbibulle

Je n'ai jamais constaté le cas mais il est vrai que j'ai peu voir jamais de clé composite.
Le seul cas qui me vient à l'esprits concerne un index composé de 3 champs un Integer et une date et un decimal(18,0). J'ai constaté un gain de 5 à 40 fois plus rapide sur certain traitements. (avant ces traitements utilisaient un autre index composite mais avec seulement 2 des champs). Les traitements boostés sont effectivement ceux qui utilisaient le 3eme champs dans ses critères de sélection. Mais je nais pas constaté de dégradation dans les autres traitements qui n'utilisent pas ce 3eme champs....

Il y a 2 choses distinctes : la manipulation de l'identifiant par la CPU, et le facteur de filtrage de l'index

Toutes choses égales par ailleurs, il est préférable que l'identifiant soit de taille < à la taille de la CPU

Mais bien évidemment, le coût IO est nettement prioritaire dans le temps total de traitement par rapport au cout CPU
Si vous avez un index avec 2 colonnes, insuffisamment filtrant, et que vous ajoutez une colonne supplémentaire qui divise significativement le nombre moyen de lignes sélectionnées, alors vous êtes gagnant (du moins pour votre requête, après il peut y avoir d'autres effets de bord comme le coût d'insertion)

**Barbibulle** · 26/10/2015, 18h30

Envoyé par cantador

exemple deux tables (table1 , champ1, champ2) (table2, champ1, champ2)
si j'ai à établir une requête SELECT avec deux jointures :
table1.champ1 avec table2.champ1
table1.champ2 avec table2.champ2

il est logique de créer un index composite sur les tables.
mais dans ce cas, cet index est créé sur les deux tables ou sur la plus petite des tables en taille

C'est un exemple qui ne me parle pas car dans mes bases, j'ai normalisé les tables et si j'ai une jointure entre deux tables j'ai forcément d'un coté une clé primaire (qui peut être composite) et de l'autre une clé étrangère.

Techniquement la clé primaire génère un index unique et le clé étrangère un index sur l'autre table.

Donc indirectement les deux tables ont toutes les deux des indexes.

Lorsqu'on fait un select avec jointure, seul ces indexes sont utilisés.

**SergioMaster** · 27/10/2015, 09h22

Bonjour,

@SergioMaster
le code ... n'affiche rien ?

Chez moi, avec mon GUI (FlameRobin) et une table remplie (évidemment) cela me donne bien le nombre de lignes de la table , soit la même valeur que

Code :

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

SELECT COUNT(*) FROM APPOSE

Donc à priori cela peut venir de ton GUI , par curiosité j'ai tenté la même chose sur RapidSQL qui m'envoie balader (il n'accepte même pas la syntaxe DECLARE VARIABLE) puis sur DataWorkBench Lite for Firebird qui lui m'a demandé de supprimer les SET TERM et ne me renvoi aucun résultat

Moralité : tout les GUI ne se ressemblent pas ! Comme je viens de rencontrer quelque chose que je ne peux pas faire avec Flamerobin (mon gui principal) je vais faire une tentative avec ces deux autres, histoire de savoir de quoi il retourne
[EDIT] Ces deux derniers ne connaissent même pas EXECUTE STATEMENT

**cantador** · 27/10/2015, 11h26

@SergioMaster

Moralité : tout les GUI ne se ressemblent pas !

oui, c'est vraisemblablement le cas..

@Barbibulle
toutes mes tables ont des clés primaires (une habitude..)
l'exemple que je t'ai soumis concernaient des clés étrangères

pour être clair, je reformule ma question :

deux tables (table1 , codetable1, champ1, champ2) (table2, codetable2, champ1, champ2)
les champs codetable1, codetable2 sont des clés primaires.

si j'ai à établir une requête SELECT avec deux jointures :
table1.champ1 avec table2.champ1
table1.champ2 avec table2.champ2

on crée 2 index composites (champ1, champ2) dans les deux tables ou dans une seule ?

**escartefigue** · 27/10/2015, 14h39

La clef composée dans une table, est
- soit la résultante d'une association du modèle conceptuel
exemple : la ligne de commande peut avoir pour clef n°de commande (issu de l'entité type commande) et code article (issu de l'entité type article)
- soit la résultante d'une entité faible qui fait référence à une entité forte du modèle conceptuel
exemple : la ligne de commande peut avoir pour clef n°de commande (issu de l'entité type commande) et un chrono (qui redémarre à 1 à chaque nouvelle commande)
on parle dans ce 2ème cas d'identification relative

Requête Update avec 3 tables et jointures

Firebird

Discussions similaires

Partager

Partager