Rapidite de traitement par jointure ou par fonction

[garciajulien]

Bonjour,
J'ai une grosse requete impliquant une dizaine de table ( la 1ere possedant 3 cles etrangere, et la requete veut recuperer des champs de ces 3 tables) et des trucs dans le genre.

Actuellement, je me pose la question de rapidite de traitement.
Existe-t-il un moyen plus rapide que 10 requetes imbriques ? Comme par exemple l'utilisation de fonction pour chaque table ?

Merci d'avance

Julien

EDIT :
Voici la query

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
SELECT wojcsitecattyfrmn.*,
        lh.HONO,lh.HOURS,lh.RATE
FROM
(
  SELECT wojcsitecatty.*,frmn.JC_FOREMANDESC
  FROM
  (
    SELECT wojcsitecat.*,ty.JTY_DESC
    FROM 
    (
      SELECT wojcsite.*,cat.JC_CATEGORYDESC
      FROM
      (
        SELECT wojc.*,site.DEST_LIB
        FROM
        (
          SELECT wo.*,
                  jc.JC_CATEGORYCODE,jc.JC_DATECREATE,jc.JC_FOREMANCODE,jc.JC_JOBDESC,
                  jc.JC_JOBTYPE,jc.JC_JOBSEQNO
          FROM
          (
            SELECT 
                A.WO_WORKORDERNO, A.WO_SITECODE,A.WO_FROMSITECODE, A.WO_ASSETCODE,B.PL_DESC,
                A.WO_DATECREATE, A.WO_DATESTART, A.WO_DATEEND, A.WO_DATECLOSE,B.PL_BRAND
            FROM WorkShop.PLTWKORD AS A WITH (nolock)
              LEFT OUTER JOIN WorkShop.PLTPLANT AS B WITH (nolock)
                ON B.PL_CODE = A.WO_ASSETCODE
            WHERE A.WO_DATECREATE >= Convert(DateTime,'01/01/2009')
            AND A.WO_DATECREATE <= Convert(DateTime,'01/01/2010')
          ) AS wo -- WorkOrder + Asset
            LEFT OUTER JOIN WorkShop.PLTJOBCD AS jc
            ON jc.JC_WORKORDERNO = wo.WO_WORKORDERNO
        ) AS wojc -- WorkOrder + Asset + JobCard
          LEFT OUTER JOIN Account.Fddst AS site
          ON wojc.WO_SITECODE = site.DEST_COD
      ) AS wojcsite -- WorkOrder + Asset + JobCard + Site
        LEFT OUTER JOIN WorkShop.PLTJOBCAT AS cat
        ON wojcsite.JC_CATEGORYCODE = cat.JC_CATEGORYCODE
    ) AS wojcsitecat -- WorkOrder + Asset + JobCard + Site + Category
      LEFT OUTER JOIN WorkShop.PLTJOBTY AS ty
      ON wojcsitecat.JC_JOBTYPE = ty.JTY_JOBTYPE 
  ) AS wojcsitecatty -- WorkOrder + Asset + JobCard + Site + Category + Type
    LEFT OUTER JOIN WorkShop.PLTFORMN AS frmn
    ON wojcsitecatty.JC_FOREMANCODE = frmn.JC_FOREMANCODE
) AS wojcsitecattyfrmn -- WorkOrder + Asset + JobCard + Site + Category + Type + Foreman
  LEFT OUTER JOIN WorkShop.PLTJOBHR AS lh
  ON wojcsitecattyfrmn.JC_JOBSEQNO = lh.JC_JOBSEQNO
  AND wojcsitecattyfrmn.WO_WORKORDERNO =  lh.JC_WORKORDERNO

[CinePhil]

Ne peux-tu faire cette requête uniquement avec des jointures plutôt qu'avec des sous-requêtes ?

[garciajulien]

Je vais essayer mais, ayant deja essaye, je n'arrivais jamais au bon resultat

Mais bon merci qd meme

[CinePhil]

Ca devrait donner à peu près ça :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT A.WO_WORKORDERNO, A.WO_SITECODE, A.WO_FROMSITECODE, A.WO_ASSETCODE, B.PL_DESC,
  A.WO_DATECREATE, A.WO_DATESTART, A.WO_DATEEND, A.WO_DATECLOSE, B.PL_BRAND,
  jc.JC_CATEGORYCODE, jc.JC_DATECREATE, jc.JC_FOREMANCODE, jc.JC_JOBDESC,
  jc.JC_JOBTYPE, jc.JC_JOBSEQNO, site.DEST_LIB, cat.JC_CATEGORYDESC, ty.JTY_DESC,
  frmn.JC_FOREMANDESC, lh.HONO, lh.HOURS, lh.RATE
FROM WorkShop.PLTWKORD AS A WITH (nolock)
LEFT OUTER JOIN WorkShop.PLTPLANT AS B WITH (nolock)
  ON B.PL_CODE = A.WO_ASSETCODE
LEFT OUTER JOIN WorkShop.PLTJOBCD AS jc
  ON jc.JC_WORKORDERNO = A.WO_WORKORDERNO
  LEFT OUTER JOIN WorkShop.PLTJOBCAT AS cat
        ON jc.JC_CATEGORYCODE = cat.JC_CATEGORYCODE
  LEFT OUTER JOIN WorkShop.PLTJOBTY AS ty
    ON jc.JC_JOBTYPE = ty.JTY_JOBTYPE 
  LEFT OUTER JOIN WorkShop.PLTFORMN AS frmn
    ON jc.JC_FOREMANCODE = frmn.JC_FOREMANCODE
LEFT OUTER JOIN WorkShop.PLTJOBHR AS lh
  ON jc.JC_JOBSEQNO = lh.JC_JOBSEQNO
  AND a.WO_WORKORDERNO =  lh.JC_WORKORDERNO
LEFT OUTER JOIN Account.Fddst AS site
  ON A.WO_SITECODE = site.DEST_COD
WHERE A.WO_DATECREATE BETWEEN >= '2009-01-01' AND '2010-01-01'

Remarque 1 : Préférer le BETWEEN plutôt que >= et <=.

Remarque 2 : Dans la requête, il peut y avoir un problème au niveau de l'avant dernière jointure car la condition porte sur 2 tables. Il faudrait normalement deux instances de la table WorkShop.PLTWKORD AS A, ce qui pourrait donner ça :

Code :

1
2
3
4
LEFT OUTER JOIN WorkShop.PLTJOBHR AS lh1
  ON jc.JC_JOBSEQNO = lh.JC_JOBSEQNO
  RIGHT OUTER JOIN WorkShop.PLTWKORD AS A2 WITH (nolock)
    ON a2.WO_WORKORDERNO =  lh.JC_WORKORDERNO

A essayé éventuellement avec une seconde instance de la table WorkShop.PLTJOBCD AS jc.

A essayer.

[Waldar]

Déjà est-ce que toutes les jointures externes se justifient ?
Par exemple, si vous avez des clefs étrangères vers des tables de références vous savez que vous n'en avez pas besoin (rapidement, je perçois les sites, les catégories, les job types), et les jointures externes sont plus coûteuses.

Ensuite, c'est recommandé d'écrire "tout d'un coup". L'optimiseur de toute façon réécrira la requête comme il lui plaît, alors autant simplifier le code :

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
SELECT 
    A.WO_WORKORDERNO, A.WO_SITECODE,A.WO_FROMSITECODE, A.WO_ASSETCODE,B.PL_DESC,
    A.WO_DATECREATE, A.WO_DATESTART, A.WO_DATEEND, A.WO_DATECLOSE,B.PL_BRAND,
    jc.JC_CATEGORYCODE, jc.JC_DATECREATE, jc.JC_FOREMANCODE, jc.JC_JOBDESC,
    jc.JC_JOBTYPE, jc.JC_JOBSEQNO, site.DEST_LIB, cat.JC_CATEGORYDESC, ty.JTY_DESC,
    frmn.JC_FOREMANDESC, lh.HONO, lh.HOURS, lh.RATE
FROM
    WorkShop.PLTWKORD AS A WITH (nolock)
    LEFT OUTER JOIN WorkShop.PLTPLANT AS B WITH (nolock)
      ON B.PL_CODE = A.WO_ASSETCODE
    LEFT OUTER JOIN WorkShop.PLTJOBCD AS jc
      ON jc.JC_WORKORDERNO = A.WO_WORKORDERNO
    LEFT OUTER JOIN Account.Fddst AS site
      ON site.DEST_COD = A.WO_SITECODE
    LEFT OUTER JOIN WorkShop.PLTJOBCAT AS cat
      ON cat.JC_CATEGORYCODE = jc.JC_CATEGORYCODE
    LEFT OUTER JOIN WorkShop.PLTJOBTY AS ty
      ON ty.JTY_JOBTYPE = jc.JC_JOBTYPE
    LEFT OUTER JOIN WorkShop.PLTFORMN AS frmn
      ON frmn.JC_FOREMANCODE = jc.JC_FOREMANCODE
    LEFT OUTER JOIN WorkShop.PLTJOBHR AS lh
      ON lh.JC_JOBSEQNO    = jc.JC_JOBSEQNO
     AND lh.JC_WORKORDERNO = A.WO_WORKORDERNO
WHERE
    A.WO_DATECREATE >= Convert(DateTime,'01/01/2009')
AND A.WO_DATECREATE <= Convert(DateTime,'01/01/2010')

Edit 1 : Grillé

Edit 2 : Phil, je pense qu'aucune de vos deux remarques soit juste.
Le moteur SQL doit convertir between en >= et <=.
Le double prédicat de jointure sur une même table ne pose pas de problème si le besoin le justifie.

[CinePhil]

Envoyé par Waldar

Le moteur SQL doit convertir between en >= et <=.

Pas sûr ! Si BETWEEN existe, il doit y avoir une raison. Et si j'en crois SQLPro (Point 7 du tableau), BETWEEN serait plus optimisé que >= AND <=.

Le double prédicat de jointure sur une même table ne pose pas de problème si le besoin le justifie.

Je n'affirmerais rien à ce sujet mais je suis très méfiant quant au résultat obtenu quand ce genre de cas se présente. Une vérification s'impose.

Code :

1
2
FROM A
JOIN B ON condition_jointure

Logiquement, la syntaxe de la jointure signifie qu'on joint 1 table à 1 autre. La condition de jointure ne devrait donc porter que sur ces deux tables et pas une troisième.
Peut-être cette double condition arrive t-elle parce que plusieurs cheminements sont possibles dans le modèle de données pour arriver à la dernière table ?

Encore une fois, je suis très méfiant et une vérification du résultat s'impose.

[Waldar]

J'ai testé à l'instant sur Oracle (9i / 11g) et SQL Server (2005), aucune différence de plan d'exécution entre BETWEEN et >= / <=.

Pour les multi jointures, faisons un test (syntaxe SQL Server).
On a trois tables qu'on rempli :

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
declare @table1 TABLE
(
    id    integer,
    num   integer,
    nom1  varchar(10)
);
 
declare @table2 TABLE
(
    id    integer,
    cod   varchar(10),
    nom2  varchar(10)
);
 
declare @table3 TABLE
(
    cod   varchar(10),
    num   integer,
    nom3  varchar(10)
);
 
INSERT INTO @table1 VALUES (1, 101, 'ID1');
INSERT INTO @table1 VALUES (2, 102, 'ID2');
INSERT INTO @table1 VALUES (3, 103, 'ID3');
INSERT INTO @table1 VALUES (4, 104, 'ID4');
INSERT INTO @table1 VALUES (5, 105, 'ID5');
 
INSERT INTO @table2 VALUES (1, 'A', 't2-A');
INSERT INTO @table2 VALUES (2, 'B', 't2-B');
INSERT INTO @table2 VALUES (3, 'C', 't2-C');
INSERT INTO @table2 VALUES (4, 'D', 't2-D');
 
INSERT INTO @table3 VALUES ('A', 101, 'TestA');
INSERT INTO @table3 VALUES ('B', 102, 'TestB');
INSERT INTO @table3 VALUES ('D', 104, 'TestD');
INSERT INTO @table3 VALUES ('E', 103, 'TestE');

On vérifie les donné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
SELECT * FROM @table1;
 
id          num         nom1
----------- ----------- ----------
1           101         ID1
2           102         ID2
3           103         ID3
4           104         ID4
5           105         ID5
 
SELECT * FROM @table2;
 
id          cod        nom2
----------- ---------- ----------
1           A          t2-A
2           B          t2-B
3           C          t2-C
4           D          t2-D
 
SELECT * FROM @table3;
 
cod        num         nom3
---------- ----------- ----------
A          101         TestA
B          102         TestB
D          104         TestD
E          103         TestE

Et faisons le test qui nous intéresse :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
    t1.nom1,
    t2.nom2,
    t3.nom3
FROM
    @table1 t1
    INNER JOIN @table2 t2
      ON t2.id = t1.id
    LEFT OUTER JOIN @table3 t3
      ON t3.cod = t2.cod
     AND t3.num = t1.num;
 
nom1       nom2       nom3
---------- ---------- ----------
ID1        t2-A       TestA
ID2        t2-B       TestB
ID3        t2-C       
ID4        t2-D       TestD

Rien d'incohérent et j'ai bien le résultat que j'attendais !

[garciajulien]

Bonjour et merci encore de vous pencher sur le probleme.

J'ai en effet enlever les requetes imbriques et j'ai modifie le between. Par contre pour la jointure avec 2 conditions c'est necessaire car ce sont les 2 cles primaires de la table.
Je sui sentrain de regarder au niveau des index dans sql server 2005 et voila je ne sais comment faire pour faire mieux.

Pour les joitures externes effectivment, il ne me faut pas garder les cles etrangere mais juste m'en servir pour acceder certaines donnees.

Il y a t il d'autre moyen pour faire cela?

Merci

[Waldar]

Envoyé par garciajulien

Pour les jointures externes effectivment, il ne me faut pas garder les cles etrangere mais juste m'en servir pour acceder certaines donnees.

Là j'ai du mal à vous suivre !

Le principe de la jointure externe c'est de ramener les résultats si une partie de la jointure n'existe pas.

Je suis quasi sûr que votre table WorkShop.PLTJOBCAT qui contient les libellés des catégories possède une clef primaire sur JC_CATEGORYCODE, et j'espère que votre table WorkShop.PLTJOBCD a une clef étrangère indexée sur sa colonne JC_CATEGORYCODE.

Si c'est le cas, la jointure externe est complètement inutile et vous pouvez convertir votre LEFT OUTER JOIN en INNER JOIN.

Si vous éliminez la majorité des jointures externes vous gagnerez en performance.

[SQLpro]

Envoyé par garciajulien

Par contre pour la jointure avec 2 conditions c'est necessaire car ce sont les 2 cles primaires de la table.

Une table ne peut avoir qu'une seule clef primaire. Jamais deux !

A +

[CinePhil]

Si tu pouvais fournir un schéma (MCD, MLD, E/R, diagramme de classes...) de ta BDD, on pourrait peut-être trouver une autre manière de t'aider à joindre tout ça correctement.

Une description du besoin ne serait pas de trop non plus. Tu as vraiment besoin de ramener toutes ces colonnes ?

Pour les joitures externes effectivment, il ne me faut pas garder les cles etrangere mais juste m'en servir pour acceder certaines donnees.

Il y a t il d'autre moyen pour faire cela?

Euh... là j'ai pas trop compris !
Le principe de base est que les tables sont associées par une condition de jointure entre clé primaire et clé étrangère.

Par contre pour la jointure avec 2 conditions c'est necessaire car ce sont les 2 cles primaires de la table.

Ca veut dire que la table en question est une table issue d'une association à cardinalités n,m ?

Quand j'ai à faire des requêtes complexes, je pars du schéma de la BDD pour construire le cheminement des jointures. Essaie de faire pareil.

[garciajulien]

Je viens d'en referer a mon DBA, on va refaire la db car on est en plein rewrite donc ce sera mieux de repartir sur de bonne base.

Je creuse la dessus et je veins vous retrouver quand tout sera propre si j'ai encore des soucis. Encore merci ^^